Grouping by Fiscal Year (Oracle)

Is there a way in Oracle that can pull the FY? I used the script below to pull just two FY. Mytable date range is from FY1998 to FY2009.

 SELECT 'FY2008' as FY, 
         Site, 
         COUNT(*) 
    FROM mytable 
   WHERE date >='10-OCT-2007' 
     AND date <'10-OCT-2008' 
GROUP BY site

 SELECT 'FY2008' as FY, 
         Site, 
         COUNT(*) 
    FROM mytable 
   WHERE date >='10-OCT-2008' 
     AND date <'10-OCT-2009' 
GROUP BY site

Pull开发者_如何转开发ing two FY is OK but it's too much repeatative when pulling more than 10 FY.

Add 83 days to your date and truncate it to whole year:

select 'FY'||TRUNC(date + 83, 'YYYY') as FY, Site, count(*)
from mytable
group by 'FY'||TRUNC(date + 83, 'YYYY'), site

Assuming Oracle 9i+, use a CASE expression:

  SELECT CASE 
           WHEN TO_CHAR(t.date, ) = 10 AND EXTRACT(DAY FROM t.date) >= 10 THEN 
            'FY' || EXTRACT(YEAR FROM t.date) + 1
           WHEN TO_CHAR(t.date, ) > 10 THEN 
            'FY' || EXTRACT(YEAR FROM t.date) + 1
           ELSE
            'FY' || EXTRACT(YEAR FROM t.date) 
         END AS FY,
         t.site, 
         COUNT(*) 
    FROM YOUR_TABLE t
GROUP BY t.site, FY

And for completeness, in addition to @eumiro answer. In countries (such as Australia) which have a financial year running from 1 July to 30 June, you can replace the 83 with 184.

A few options:

You can use the to_char function here. Check this link for an explanation: http://www.techonthenet.com/oracle/functions/to_char.php

You may also try using a case statement

select case when date >='10-OCT-2007' and date <'10-OCT-2008' then 'FY08'
            when date >='10-OCT-2008' and date <'10-OCT-2009' then 'FY09'
            else 'Other' end as fiscal_year, count(*)
  from mytable
 group by  case when date >='10-OCT-2007' and date <'10-OCT-2008' then 'FY08'
            when date >='10-OCT-2008' and date <'10-OCT-2009' then 'FY09'
            else 'Other' end

Ultimately, if you have create table privileges you may want to consider making a date lookup table. Search for "date dimension" in data warehousing guides.

For example:
Your table would have date, date_desc, fiscal_year, etc....

then you could just join and group by fiscal year, or whatever else you want.

Here is another way to easily determine the Fiscal Year of a date for those who's Fiscal Year runs from July to June:

SELECT 'FY'||TO_CHAR(ROUND(your_date_here,'YEAR'),'YY') AS FY