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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading