FINANCIAL YEAR QUERY

SELECT DECODE (SIGN (TO_NUMBER (SUBSTR (month_code, 5)) - 6),
1, SUBSTR (month_code, 1, 4)
|| '-'
|| TO_CHAR (TO_NUMBER (SUBSTR (month_code, 1, 4)) + 1),
TO_CHAR (TO_NUMBER (SUBSTR (month_code, 1, 4)) - 1)
|| '-'
|| SUBSTR (month_code, 1, 4)
) fin_year,
SUM (bill_amt) bill_amt
FROM emp_bill
GROUP BY DECODE (SIGN (TO_NUMBER (SUBSTR (month_code, 5)) - 6),
1, SUBSTR (month_code, 1, 4)
|| '-'
|| TO_CHAR (TO_NUMBER (SUBSTR (month_code, 1, 4)) + 1),
TO_CHAR (TO_NUMBER (SUBSTR (month_code, 1, 4)) - 1)
|| '-'
|| SUBSTR (month_code, 1, 4)
)



SELECT TO_CHAR (ADD_MONTHS (TO_DATE (month_code, 'rrrrmm'), -6),
'RRRR'
)
|| '-'
|| TO_CHAR (ADD_MONTHS (TO_DATE (month_code, 'rrrrmm'), 6), 'RRRR')
fin_yr,
SUM (bill_amt) bill_amt
FROM emp_bill
GROUP BY TO_CHAR (ADD_MONTHS (TO_DATE (month_code, 'rrrrmm'), -6), 'RRRR')
|| '-'
|| TO_CHAR (ADD_MONTHS (TO_DATE (month_code, 'rrrrmm'), 6), 'RRRR')


select add_months(last_day(sysdate),-1)+1 from dual

select to_date(to_char(sysdate,'RRRRMM'),'RRRRMM') from dual

select trunc(sysdate,'MM') from dual


select to_number(to_char(sysdate, 'Dth'))-6 from dual

select substr(month_code,5)-6 from emp_bill