Good day! Recently Frank Kulash assisted me in coding a pivot table to get a static output range of results for value totals per month using Oracle 11g
My new issue I'm working towards is using a pivot table to get the values as I did before as well as the totals per year. To get the current correct results let me show what I have...
Current Pivot Query:
--------------------------------------------
WITH KEYACM AS
(
SELECT CF_PERIOD, CF_VALUE, CF_KEYWORD_ID,
row_number () OVER ( PARTITION BY CF_KEYWORD_ID
ORDER BY CF_PERIOD desc
) AS r_num
FROM AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA
)
SELECT *
FROM KEYACM
PIVOT ( MIN (KEYACM.CF_VALUE) AS VALUE
, MIN (KEYACM.CF_PERIOD) AS PERIOD
FOR r_num IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)
)
ORDER BY CF_KEYWORD_ID ;
-------------------------------------------------
This breaks up the CF_VALUE and CF_PERIOD by CF_KEYWORD_ID and the results look like this (let's assume that JAN-14 is the current period)
CF_KEYWORD_ID 1_VALUE 1_PERIOD 2_VALUE 2_PERIOD 3_VALUE 3_PERIOD etc...till 24_VALUE 24_PERIOD
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
available_reserve -100.50 01-JAN-14 375.00 01-DEC-13 21300.74 01-NOV-13 900.50 01-FEB-12
With the results above the values are correct and display perfectly within Jasper Ireports. What I'm looking to do is insert either 2 or 3 columns that would be a total of each year SUM_1, SUM_2 and SUM_3 (no label is needed). In the results above there should be one total for 2014 which will include just the month of January. Then there should be a total for all of 2013 and for 2012 EXCLUDING JAN since it did not make it into the last 24 months of the selected period. If the selected month was DECEMBER then you would only have two summaries since you would be looking at two full years.
Is there a solid way of accomplishing this?