Grouping by Fiscal Year (Oracle)

Question

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

---

Pulling two FY is OK but it's too much repeatative when pulling more than 10 FY.

Answer 1

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

Answer 2

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.

Answer 3

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