Hi All,
I have a requirement to display month wise count for the branch.
Branch name (input parameter) and month (input parameter)
Ex:
Branch - UK and month - Sep-18 as input parameter , i want to display my report as below
UK Sep17 Oct 17 Nov-17 Dec-17 Jan-18 Feb-18 mar-18 ................ Sep-18
No of sales 10 20 30 40 100 120 140 ....................200
Below are query to get the one year date based on the input parameter.
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY')-365, LEVEL -1), 'MON-YY') MONTHS
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),2), ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),-11))
I need to convert this as a column and display in my report.
I cant hard coded the month values in PIVOT function , i tried with below query and its not working .
WITH TAB1 AS(
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY')-365, LEVEL -1), 'MON-YY') MONTHS
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),2), ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),-11)))
select MONTHS from TAB1
pivot (
count(*) for MONTHS in(SELECT listagg(MONTHS,',') within group (order by MONTHS) FROM TAB1)
Regards,
San