I am trying to do counts for multiple fiscal years. I can do this for calendar years, but have been unable to figure it out for fiscal years.
Select count(*), to_char(date,'yyy')year from myTable where date between '01-jul-96 00:00' and '01-jul-06 00:00' group by to_char(date,'yyy')
This returns group by calendar year, but I would like to do this by fiscal year (July to July) without having to do each year individually. Thanks in advance for any help.
My Oracle SQL is really rusty, but perhaps the ROUND function will serve your purpose:
select count(*) rows , round(date,'YEAR') year from myTable where date >= '01-jul-96 00:00' and date < '01-jul-06 00:00' group by round(date,'YEAR')
What a happy coincidence that your fiscal year starts on July 1st. Any other date would have required more creative date manipulation.
Notice also the minor correction to your WHERE condition.
This was first published in January 2007