Maybe I'm just being a bit dense but I would rather use the below script to generate instead of relying on the datediff function and working back from today's date... obviously the below has more columns than you would need for explanation - can anyone point out why mine wouldn't be the preferred route?
--example table with dates to sort by Fin Year and Fin Quarter
CREATE TABLE #DatesToTest
(
TestDate DATE NOT NULL
,FinYear VARCHAR(5) NULL
, FinYearStart DATE NULL
,FinQuarter1Start DATE NULL
,FinQuarter2Start DATE NULL
,FinQuarter3Start DATE NULL
,FinQuarter4Start DATE NULL
,FinQuarter INT
)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE())
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-365)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-180)
DECLARE @FinYearStart VARCHAR(20)
SET @FinYearStart = '07-01' -- Use this variable to define the beginning of your financial year
--PRINT @FinYearStart
--calculate financial year based off of financial year start date
SELECT * FROM [#DatesToTest] AS DTT
UPDATE [#DatesToTest]
SET [FinYear] = CASE WHEN [TestDate]>=CAST(CAST(YEAR([TestDate]) AS CHAR(4))+'-'+@FinYearStart AS DATE)
THEN YEAR([TestDate])
ELSE YEAR([TestDate])-1
END
SELECT * FROM [#DatesToTest] AS DTT
--Set financial year start date into table for easier access and quarter formula
UPDATE [#DatesToTest]
SET FinYearStart= CAST([FinYear]+'-'+@FinYearStart AS DATE)
SELECT * FROM [#DatesToTest] AS DTT
--set the financial year quarter dates for easy calculations
UPDATE [#DatesToTest]
SET [FinQuarter1Start] = [FinYearStart]
,[FinQuarter2Start] = DATEADD(MONTH,3,[FinYearStart])
,[FinQuarter3Start] = DATEADD(MONTH,6,[FinYearStart])
,[FinQuarter4Start] = DATEADD(MONTH,9,[FinYearStart])
SELECT * FROM [#DatesToTest] AS DTT
--check which quarter the date falls into
UPDATE [#DatesToTest]
SET [FinQuarter] = CASE WHEN [TestDate] >= [FinQuarter4Start] THEN 4
WHEN [TestDate] >= [FinQuarter3Start] THEN 3
WHEN [TestDate] >= [FinQuarter2Start] THEN 2
ELSE 1 END
SELECT * FROM [#DatesToTest] AS DTT
--tidy up
DROP TABLE [#DatesToTest]