hallianonline 0 Newbie Poster

Hello I am facing a problem while creating a view in MySQL, I tried all the possible solutions I got from internet but was failed. actually I have two tables 1 is for installment schedule which contains a planned schedules and the table 2 contains all installment payment which has been paid tables are as follows

Installment Schedule Table

+------------------+---------------+--------------------+--------+---------+--------------------+
| InstallmentName  | InstallmentSr |    ScheduleDate    | Amount | PayStat |      PayDate       |
+------------------+---------------+--------------------+--------+---------+--------------------+
| 12th Installment |            12 | 11/3/2017 00:00:00 |   9565 |       0 | 12/3/2017 00:00:00 |
| 11th Installment |            11 | 10/3/2017 00:00:00 |   9585 |       0 | 11/3/2017 00:00:00 |
| 10th Installment |            10 | 9/3/2017 00:00:00  |   9585 |       0 | 10/3/2017 00:00:00 |
| 9th Installment  |             9 | 8/3/2017 00:00:00  |   9585 |       0 | 9/3/2017 00:00:00  |
| 8th Installment  |             8 | 7/3/2017 00:00:00  |   9585 |       0 | 8/3/2017 00:00:00  |
| 7th Installment  |             7 | 6/3/2017 00:00:00  |   9585 |       0 | 7/3/2017 00:00:00  |
| 6th Installment  |             6 | 5/3/2017 00:00:00  |   9585 |       0 | 6/3/2017 00:00:00  |
| 5th Installment  |             5 | 1/3/2017 00:00:00  |   9585 |       0 | 5/3/2017 00:00:00  |
| 4th Installment  |             4 | 12/3/2016 00:00:00 |   9585 |       0 | 1/3/2017 00:00:00  |
| 3rd Installment  |             3 | 11/3/2016 00:00:00 |   9585 |       0 | 12/3/2016 00:00:00 |
| 2nd Installment  |             2 | 10/3/2016 00:00:00 |   9585 |       0 | 11/3/2016 00:00:00 |
| 1st Installment  |             1 | 9/3/2016 00:00:00  |   9585 |       1 | 10/3/2016 00:00:00 |
+------------------+---------------+--------------------+--------+---------+--------------------+

Installment Payments Table

+--------+--------------------+---------------+
| Amount |    PaymentDate     | InstallmentSr |
+--------+--------------------+---------------+
|   9585 | 12/3/2016 00:00:00 |             1 |
+--------+--------------------+---------------+

Now I want a view in mySQL which shows a runningbalance/overdue balance if the payment status i.e. PayStat = 0 and Due date i.e. PayDate is less than current date (Today) something like this

+------------------+------------+-----------------+--------+------------+---------------+----------------+
| Installment Name | ScheduleSr |  ScheduleDate   | Amount | AmountPaid | BalanceAmount | OverDueBalance |
+------------------+------------+-----------------+--------+------------+---------------+----------------+
| 1st Installment  |          1 | 9/12/2016 0:00  |   9000 |       9000 |             0 |              0 |
| 2nd Installment  |          2 | 10/12/2016 0:00 |   9000 |          0 |          9000 |           9000 |
| 3rd Installment  |          3 | 11/12/2016 0:00 |   9000 |          0 |          9000 |          18000 |
+------------------+------------+-----------------+--------+------------+---------------+----------------+
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.