Financial Year SQL

[binway]

I was after some code for Oracle that gave me a financial year by months with running totals and not being an expert thought something would be available on the web but couldn't find it anywhere. The financial year in this case was from the 01/04/2004 to 31/03/2005 but with a small modification it could work for any financial year. We only required it to return 10 rows of data hence the rownum <=10. So I thought I'd post this - please feel free to put it somewhere else. I didn't write this by myself but was pretty happy when we got this working.

To get better Date displays add this above the main code

SELECT
TO_CHAR(trunc(S1.END_DATE,'MONTH'),'YYYY-MM-DD HH24:MI:SS') "MONTH_START",
TO_CHAR(S1.END_DATE, 'YYYY-MM-DD HH24:MI:SS') "MONTH_END",
SUM(S1.C2) DMG_MAIN_COUNT_YTD_ACT_NUM
FROM

(Main Code)
SELECT
T3.START_DATE,
T3.END_DATE,
COUNT(ORD_ID) AS C2
FROM
T1,
T2,
(select CASE WHEN to_char(END_DATE, 'MM') < '04' THEN
to_date(to_char(add_months(END_DATE,-12), 'YYYY') || '-04-01', 'YYYY-MM-DD')
ELSE
to_date(to_char(END_DATE, 'YYYY') || '-04-01', 'YYYY-MM-DD')
END START_DATE,
END_DATE
from
(select LAST_DAY(ADD_MONTHS(TO_DATE('2005-04-28', 'YYYY-MM-DD'), -ROWNUM+1)) END_DATE
FROM ALL_OBJECTS
WHERE ROWNUM <= 10
order by 1)
) T3
WHERE
T1.Key = T2.key
AND T1.ORD_COMPLETE_DATE BETWEEN T3.START_DATE
AND T3.END_DATE
AND more where clause
GROUP BY T3.START_DATE,T3.END_DATE


Regards

Binway