Calculate quarter for dates given an example end date and quarter number

I have an issue where I need to determine fiscal quarters, but won't always know the start/end dates for the quarters. They will, however, always be 3 months long. What I will know is the ending date of the current quarter, and what quarter and year that refers to. For example, I might be given:

Current Quarter: Q4
Current Year: 2021
Current Quarter End Date: 1/31/2021

How can I get the quarter for any other date? If any of those 3 values were to change, the query still needs to provide the quarter for any given date based on those 3 parameters.

I came up with the following, which puts the last 4 years into a temp table:

DECLARE @QuarterEnd             DATE            =   '1/31/2022'
,       @CurrentQuarter         INT             =   1
,       @CurrentYear            INT             =   2022
,       @Counter                INT             =   16
,       @qs                     INT             =   0
,       @qe                     INT             =   2
,       @DateToTest             DATE            =   '12/15/2021'

CREATE TABLE #Quarters (
        StartDate   DATE
,       EndDate     DATE
,       Qtr         INT
,       Yr          INT
)

WHILE @Counter <> 0
BEGIN
    INSERT INTO #Quarters VALUES (
            cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, @QuarterEnd)-@qe , 0) as date)
        ,   cast(DATEADD(MONTH, DATEDIFF(MONTH, -1, @QuarterEnd)-@qs, -1) as date)
        ,   @CurrentQuarter
        ,   @CurrentYear
    )
    SET @Counter = @Counter - 1
    SET @qs = @qs + 3
    SET @qe = @qe + 3
    SET @CurrentQuarter = CASE WHEN @CurrentQuarter = 1 THEN 4 ELSE @CurrentQuarter - 1 END
    SET @CurrentYear = CASE WHEN @CurrentQuarter = 4 THEN @CurrentYear - 1 ELSE @CurrentYear END
END

SELECT  @DateToTest
,       (SELECT CONCAT('Q', Qtr, ' ', Yr) FROM #Quarters WHERE @DateToTest BETWEEN StartDate and EndDate)
FROM    #Quarters

However, this doesn't seem to be practical when I'm running queries that will return hundreds of thousands of records.

I suppose I can throw that into a function and call it with:

SELECT MyQuarter = dbo.MyQuarterFunction(@QuarterEnd, @CurrentQuarter, @CurrentYear, @DateToTest)

There has to be a more efficient way to do this. Any suggestions?

Solution

Assuming that you have two input variables:

declare @quarter_end date = '2021-01-31';
declare @current_quarter int = 4;

You can calculate the first month of financial year:

declare @first_month_of_fy int = (month(@quarter_end) - @current_quarter * 3 + 12) % 12 + 1;
-- 2 i.e. February

And use that value to calculate the quarter and year for any date using some math:

select *
from (values
    ('2020-12-15'),
    ('2021-01-15'),
    ('2021-12-15'),
    ('2022-01-15')
) as t(testdate)
cross apply (select
    (month(testdate) - @first_month_of_fy + 12) % 12 + 1
) as ca1(month_of_fy)
cross apply (select
    (month_of_fy - 1) / 3 + 1,
    year(dateadd(month, 12 - month_of_fy, dateadd(day, - day(testdate) + 1, testdate)))
) as ca2(fy_quarter, fy_year)

DB<>Fiddle