SQL Server 2008 Question

Question

Hi,

Is there a way in SQL Server that can show the Fiscal Year (begins on October 1 and ends on September 30) from a table which has a date column (1998 to 2010). Here is what I have done:

select 'FY1999' as FY, site, count(*) from mytable
where mydate >='10/1/1998' and mydate <'10/1/1999'
group by site

select 'FY2000' as FY, site, count(*) from mytable
where mydate >='10/1/1999' and mydate <'10/1/2000'
group by site

select 'FY2001' as FY, site, count(*) from mytable
where mydate >='10/1/2000' and mydate <'10/1/2001'
group by site

Isn't it too much repetitive when doing this for more then 10 FY year?

Answer 1

You can create a function to calculate the fical year:

CREATE FUNCTION fn_FiscalYear(@date DATETIME)
RETURNS INT
AS BEGIN
    DECLARE @AddYear int
    SET @AddYear = 0
    IF (DATEPART(mm, @date) > 9)
        SET @AddYear = 1

    RETURN( DATEDIFF(yyyy, '1999-10-01', @date)+1998+@AddYear)
END
go

select dbo.fn_FiscalYear('1999-10-01')
select dbo.fn_FiscalYear('2000-09-30')
select dbo.fn_FiscalYear(GETDATE())
select dbo.fn_FiscalYear('2005-01-01')
select dbo.fn_FiscalYear('2004-12-31')

and use that function in your queries:

SELECT dbo.fn_FiscalYear(TransactionDate), Amount
FROM dbo.Transactions

Answer 2

select case 
           when month(MyDate) >= 10 then Year(MyDate) + 1 
           else Year(MyDate) 
       end as FiscalYear, 
    count(*) as Count
from MyTable
group by case 
             when month(MyDate) >= 10 then Year(MyDate) + 1 
             else Year(MyDate) 
         end