I have a table with 25 date columns. Now i am building an application in PHP and have to show a month and year based summary. I have mentioned the Source table layout and expected Summary table below.
Here ID
represents each row. Source table ID
will be used in the summary table. The summary table will be created per year. I need some help to prepare this summary table. Here I would like to mention that i will have around 30K rows on the source table.
Source Table:
ID | DATE 1 | DATE 2 | DATE 3
--------------------------------------------------
1 | 2017-01-14 | 2017-01-19 | 2017-01-25
2 | | 2017-03-19 | 2017-03-25
3 | 2017-03-15 | | 2017-05-25
4 | 2017-04-24 | 2017-05-19 |
5 | 2017-04-10 | 2017-06-19 | 2017-07-25
6 | 2017-05-11 | 2017-06-19 | 2017-08-25
Summary Table
ID | YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
------------------------------------------------------------------------------------
1 | 2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2 | 2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
3 | 2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
4 | 2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
5 | 2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
6 | 2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0