CODEWITHSUNDEEP
sqlsql-server
Tom
Tom

Reputation: 83

Grouping Fiscal year using SQL Server

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?

Upvotes: 7

Views: 10339

Answers (8)

Colin
Colin

Reputation: 22575

Here is some test Sql based on Simon's answer

    DECLARE @basestartdate datetime, @baseenddate datetime
SET @basestartdate = CAST('1 April 1753' AS datetime)
SET @baseenddate = CAST('31 March 1754' AS datetime)

;
WITh TestData as
(
SELECT
    CAST('1 April 2015' AS datetime) input,
    CAST('1 April 2015' AS datetime) expectedstartdate,
    CAST('31 March 2016' AS datetime) expectedenddate UNION SELECT
    CAST('2 April 2015' AS datetime),
    CAST('1 April 2015' AS datetime),
    CAST('31 March 2016' AS datetime) UNION SELECT
    CAST('31 December 2015' AS datetime),
    CAST('1 April 2015' AS datetime),
    CAST('31 March 2016' AS datetime) UNION SELECT
    CAST('1 January 2016' AS datetime),
    CAST('1 April 2015' AS datetime),
    CAST('31 March 2016' AS datetime) UNION SELECT
    CAST('28 February 2016' AS datetime),
    CAST('1 April 2015' AS datetime),
    CAST('31 March 2016' AS datetime) UNION SELECT
    CAST('31 March 2016' AS datetime),
    CAST('1 April 2015' AS datetime),
    CAST('31 March 2016' AS datetime) UNION SELECT
    CAST('1 April 2016' AS datetime),
    CAST('1 April 2016' AS datetime),
    CAST('31 March 2017' AS datetime)
),
Results AS
(
SELECT
    input,
    expectedstartdate,
    DATEADD(MONTH, 
            12 * (DATEDIFF(MONTH, @basestartdate, input) / 12),
            @basestartdate) startdate,
    expectedenddate,
    DATEADD(MONTH, 
            12 * (DATEDIFF(MONTH, @basestartdate, input) / 12),
            @baseenddate) enddate
FROM testdata
)
SELECT
    CASE
        WHEN (expectedstartdate = startdate) THEN 'Pass' ELSE 'Fail'
    END startdateresult,
    CASE
        WHEN (expectedenddate = enddate) THEN 'Pass' ELSE 'Fail'
    END startdateresult
FROM results
ORDER BY input

Upvotes: 0

Simon
Simon

Reputation: 11

Start of fiscal year:

DATEADD(MONTH, DATEDIFF(MONTH, '20100401', getdate()) / 12 * 12, '20100401')

End of Fiscal Year

DATEADD(MONTH, DATEDIFF(MONTH, '20100401', getdate()) / 12 * 12, '20110331')

Replace getdate() with your own date if required

Upvotes: 1

itwb
itwb

Reputation: 437

Yesterday there was an answer to this question, which was subsequently deleted. I do not know why. Was there something wrong with it?

Please don't vote this answer down, I just want to know why it was deleted.

With vigorous testing, I still can't manage to fault it. In my example, Fiscal year starts July 1.

The answer was:

SELECT SUM(value), CAST(Year(DateAdd(Month, -6, TransactionDate)) as varchar) + ' - ' + CAST(Year(DateAdd(Month, 6, TransactionDate)) as varchar) as 'FY'
FROM          mytable
GROUP BY CAST(Year(DateAdd(Month, -6, mydate)) as varchar) + ' - ' + CAST(Year(DateAdd(Month, 6, mydate)) as varchar)

Upvotes: 0

Conficker
Conficker

Reputation: 39

This is the dynamic script for UK, April to March, for different dates you can use as refernece,

Good Luck

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12), getDate() ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12),getDate() ))+1 ) )  

SET @EndDate = DATEADD(SS,-1,DATEADD(mm,12,@StartDate))

SELECT @StartDate,@EndDate

Upvotes: 1

CesarGon
CesarGon

Reputation: 15345

You can even create your user-defined function in SQL Server that takes a date argument and returns the fiscal year as an int:

CREATE FUNCTION GetFiscalYear(@TheDate date)
RETURNS int
AS
BEGIN
    DECLARE @FiscalYear int  

    IF DATEPART(month, @TheDate) < 10
        SELECT @FiscalYear = DATEPART(year, @TheDate)
    ELSE
        SELECT @FiscalYear = DATEPART(year, @TheDate) + 1  

    RETURN @FiscalYear
END

Then you can use this as, for example:

SELECT Id, ShippingDate, GetFiscalYear(ShippingDate)
FROM SomeTable

Upvotes: 7

bobs
bobs

Reputation: 22224

Here's a single query that will give you the information you want.

SELECT DATEPART(yyyy, DATEADD(mm, 3, mydate)) AS FY, site, COUNT(*) AS row_count
FROM mytable
GROUP BY DATEPART(yyyy, DATEADD(mm, 3, mydate)), site

Upvotes: 12

Stefan Mai
Stefan Mai

Reputation: 23959

Yes, it is a bit repetitive. I'd be using DatePart and some easy to discern rules:

  • Fiscal year is the year of the date if the month is < 10.
  • Fiscal year is the year of the date + 1 if the month >= 10

Upvotes: 1

wallyk
wallyk

Reputation: 57804

I don't have a SQL server reference handy, but here's how I'd do it in MySQL:

select date_format (date_add(mydate, interval 92 days), 'FY%Y') as FY, site, count(*)
 from mytable
 group by FY, site;

There are 92 days in October, November, and December, so I've offset by that much.

Upvotes: 0

Related Questions