Fiscal Year Grouping (Oracle)

Question

Is there a way in Oracle that can pull FY? I used the script below to pull out only two FY. Range of ranges of incoming calls: 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 FYs is fine, but this is too much to repeat when pulling more than 10 FYs.

Answer 1

Add 83 days to your date and truncate them to a whole year:

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

Answer 2

Assuming Oracle 9i +, use the 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 

Answer 3

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

Answer 4

Several variants:

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

You can also try using the 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 are creating table privileges, you might want to create a date lookup table. Find the "date dimension" in the data warehouse guides.

For example:
Your table will have date, date_desc, financial_year, etc.

then you can just join the group for the fiscal year or whatever you want.

Answer 5

Here is another way to easily determine the fiscal year dates for those who work during the fiscal year from July to June:

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