Data in table tbl_xyz is stored this way

------------+-----------+-------+-------+---------------+---------------+
customerId  | amount    |   fee | rate  |   installment |   dateIssued  |
------------+-----------+-------+-------+---------------+---------------+
1           |   200000  |   3000|   50  |           4   |   2019-01-01  |
------------+-----------+-------+-------+---------------+---------------+
2           |   206000  |   3000|   30  |           4   |   2019-01-03  |
------------+-----------+-------+-------+---------------+---------------+
3           |   200000  |   3000|   50  |           3   |   2019-01-11  |
------------+-----------+-------+-------+---------------+---------------+
4           |   200000  |   3000|   40  |           4   |   2019-01-12  |
------------+-----------+-------+-------+---------------+---------------+
5           |   340000  |   3000|   20  |           4   |   2019-01-13  |
------------+-----------+-------+-------+---------------+---------------+
6           |   20000   |   3000|   50  |           2   |   2019-01-15  |
------------+-----------+-------+-------+---------------+---------------+
7           |   600000  |   3000|   50  |           4   |   2019-01-15  |
------------+-----------+-------+-------+---------------+---------------+
8           |   250000  |   6000|   70  |           1   |   2019-01-15  |
------------+-----------+-------+-------+---------------+---------------+

I want to have this output

Note: this output is for all records dateIssued is of any date in January only.

------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
customerId  | amount    |   fee | rate  |   installment |   dateIssued  |   total   |   inst/mo |   JAN |   FEB |   MAR |   APR |
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
1           |   200000  |   3000|   50  |           4   |   2019-01-01  |   300000  |     75500 |  75500|  75500|  75500|  75500|
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
2           |   206000  |   3000|   30  |           4   |   2019-01-03  |   267800  |    66950  | 66950 | 66950 | 66950 | 66950 |
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
3           |   200000  |   3000|   50  |           3   |   2019-01-11  |   300000  |    100000 | 100000| 100000| 100000|      0|
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
4           |   200000  |   3000|   40  |           4   |   2019-01-12  |   ...     |      ...  |   ... |   ... |   ... |   ... |
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
5           |   340000  |   3000|   20  |           4   |   2019-01-13  |   ...     |      ...  |   ... |   ... |   ... |   ... |
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
6           |   20000   |   3000|   50  |           2   |   2019-01-15  |   ...     |      ...  |   ... |   ... |   ... |   ... |
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
7           |   600000  |   3000|   50  |           4   |   2019-01-15  |   ...     |      ...  |   ... |   ... |   ... |   ... |
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+
8           |   250000  |   6000|   70  |           1   |   2019-01-15  |   425000  |    425000 |425000 |      0|      0|      0|
------------+-----------+-------+-------+---------------+---------------+-----------+-----------+-------+-------+-------+-------+

Here are some conditions:

total = (amount + (amount * rate)/100)

inst/mo = (amount + (amount * rate)/100) / installment

JAN, FEB, MAR and APR columns depends on month in dateIssued.

In above example all records are of January only. For February it would have been FEB, MAR, APR and MAY. And distribution of values in each month depends on installment.

0 Answers