Fiscal Year Grouping Using SQL Server

Question

Is there a way in SQL Server that can show a fiscal year (starts on October 1 and ends on September 30) from a table with a date column (from 1998 to 2010). Here is what I did:

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 repeating too much when you do it more than 10 years ago?

Answer 1

You can even create your own custom function in SQL Server that takes a date argument and returns the fiscal year as 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 like for example:

 SELECT Id, ShippingDate, GetFiscalYear(ShippingDate) FROM SomeTable 

Answer 2

Here is one request that will give you the necessary information.

 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 

Answer 3

Yes, this is a little repeated. I would use DatePart and some easily distinguishable rules:

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

Answer 4

Beginning of the 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() your own date if required

Answer 5

This is a dynamic script for the UK, from April to March, for different dates that you can use as a reference,

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 

Answer 6

Yesterday was the answer to this question, which was subsequently deleted. I do not know why. Is something wrong with him?

Please do not vote for this answer, I just want to know why it was deleted.

With vigorous testing, I still can't ruin it. In my example, the fiscal year begins on 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) 

Answer 7

Here are some Sql tests 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 

Answer 8

I do not have a link to the SQL server, but here is how I will 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 made up for it so much.