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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top