Hi Gurus, need your pick your brain please. Below is my scenario(I have data for all years 2015, 2016, 2017 and 2018 but below are just a few example rows to show my requirement)
Dataset:
WITH t
AS (SELECT TO_DATE ('01/01/2016', 'MM/DD/YYYY') AS book_date, 50 AS CY_RENEWAL_PREMIUM FROM DUAL
UNION ALL
SELECT TO_DATE ('01/01/2016', 'MM/DD/YYYY'), 25 FROM DUAL
UNION ALL
SELECT TO_DATE ('01/01/2016', 'MM/DD/YYYY'), 50 FROM DUAL
UNION ALL
SELECT TO_DATE ('01/01/2017', 'MM/DD/YYYY'), 30 FROM DUAL
UNION ALL
SELECT TO_DATE ('01/01/2017', 'MM/DD/YYYY'), 45 FROM DUAL
UNION ALL
SELECT TO_DATE ('01/01/2017', 'MM/DD/YYYY'), 30 FROM DUAL
UNION ALL
SELECT TO_DATE ('02/01/2016', 'MM/DD/YYYY'), 30 FROM DUAL
UNION ALL
SELECT TO_DATE ('02/01/2016', 'MM/DD/YYYY'), 50 FROM DUAL
UNION ALL
SELECT TO_DATE ('02/01/2016', 'MM/DD/YYYY'), 40 FROM DUAL
UNION ALL
SELECT TO_DATE ('02/01/2017', 'MM/DD/YYYY'), 30 FROM DUAL
UNION ALL
SELECT TO_DATE ('02/01/2017', 'MM/DD/YYYY'), 45 FROM DUAL
UNION ALL
SELECT TO_DATE ('02/01/2017', 'MM/DD/YYYY'), 35 FROM DUAL)
SELECT book_date, sum(CY_RENEWAL_PREMIUM) FROM t group by book_date ORDER BY 1
| BOOK_DATE | SUM(CY_RENEWAL_PREMIUM) |
|---|
| 01-JAN-16 | 125 |
| 01-FEB-16 | 120 |
| 01-JAN-17 | 105 |
| 01-FEB-17 | 110 |
https://livesql.oracle.com/apex/f?p=590:1:113999089809507:::::Requirement: My requirement is to get the 2016 numbers in the same row as 2017(For every month). Similarly in my full dataset i need to show 2018,2017 in the same row etc.
Final Data set should look like this: Please note that since there are no 2015 numbers data is NULL , but 2017 and 2016 Premiums are in the same row. I need these as i have to again calculate CY_Premium/PY_Premium ratios monthwise for all years.
| BOOK_DATE | CY_RENEWAL_PREMIUM | BOOK_DATE_PY | PY_PREMIUM |
|---|
| 01-JAN-16 | 125 | 01-JAN-15 | - |
| 01-FEB-16 | 120 | 01-FEB-15 | - |
| 01-JAN-17 | 105 | 01-JAN-16 | 125 |
| 01-FEB-17 | 110 | 01-FEB-16 | 120 |
SELECT TO_DATE ('01/01/2016', 'MM/DD/YYYY') AS book_date, 125 AS CY_RENEWAL_PREMIUM, TO_DATE ('01/01/2015', 'MM/DD/YYYY') AS book_date_PY, NULL as PY_PREMIUM from dual
union all
select TO_DATE ('02/01/2016', 'MM/DD/YYYY') AS book_date, 120 AS CY_RENEWAL_PREMIUM, TO_DATE ('02/01/2015', 'MM/DD/YYYY') AS book_date_PY, NULL as PY_PREMIUM from dual
union all
select TO_DATE ('01/01/2017', 'MM/DD/YYYY') AS book_date, 105 AS CY_RENEWAL_PREMIUM, TO_DATE ('01/01/2016', 'MM/DD/YYYY') AS book_date_PY, 125 as PY_PREMIUM from dual
union all
select TO_DATE ('02/01/2017', 'MM/DD/YYYY') AS book_date, 110 AS CY_RENEWAL_PREMIUM, TO_DATE ('02/01/2016', 'MM/DD/YYYY') AS book_date_PY, 120 as PY_PREMIUM FROM DUAL
Any ideas are appreciated. Please let me know if you need additional details.
Thanks a lot in advance,
B