'SQL - How to determine fiscal quarter when quarters are determined by # of days?

I have a situation I've never seen before, where a fiscal year started on 2/4/2018 is broken down like this:

Q1 - 111 days long (2/4/2018 - 5/26/2018)
Q2 - 83 days long (5/27/2018 - 8/18/2018)
Q3 - 83 days long (8/19/2018 - 11/10/2018)
Q4 - 83 days long (11/11/2018 - 2/2/2019)

That is considered FY 2018

The next year, FY 2019, starts on 2/3/2019 and has the same quarter lengths, and Q4 would end on 2/1/2020. FY 2020 then starts on 2/2/2020.

I need to be able to determine the fiscal year and quarter for a given date (@testdate in my code below). The following works for FY 2018:

declare @startdate  date    = '2/4/2018'
,       @startyear  int     = 2018
,       @testdate   date    = '2/5/2018'

select  'Fiscal Year'       = case when datediff(dd, @startdate, @testdate) between 0   and 363 then @startyear else 0 end

select  'Fiscal Quarter'    = case when datediff(dd, @startdate, @testdate) between 0   and 111 then 'Q1'
                                   when datediff(dd, @startdate, @testdate) between 112 and 195 then 'Q2'
                                   when datediff(dd, @startdate, @testdate) between 196 and 279 then 'Q3'
                                   when datediff(dd, @startdate, @testdate) between 280 and 363 then 'Q4'
                                   else 'Q0' end

The problem is when I have a date after the end of FY 2018 Q4 (2/2/2019). I'm not sure how to get any date past that to automatically fall into the day ranges (0-111, 112-195, 196-279, 280-363). Manually, for FY 2019, I can subtract 364 from the date and that seems to work. For FY 2020, I can subtract 364 * 2 (728). For each year after that, keep subtracting 364 * n where n is the number of years between whatever future fiscal year and FY 2018.

This works for FY 2020:

declare @startdate  date    = '2/4/2018'
,       @startyear  int     = 2018
,       @testdate   date    = '5/28/2020'
,       @testdate2  date    = null

set @testdate2 = dateadd(d, -728, @testdate)

select  'Fiscal Year'       = case when datediff(dd, @startdate, @testdate2) between 0   and 363 then @startyear + 2 else 0 end

select  'Fiscal Quarter'    = case when datediff(dd, @startdate, @testdate2) between 0   and 111 then 'Q1'
                                   when datediff(dd, @startdate, @testdate2) between 112 and 195 then 'Q2'
                                   when datediff(dd, @startdate, @testdate2) between 196 and 279 then 'Q3'
                                   when datediff(dd, @startdate, @testdate2) between 280 and 363 then 'Q4'
                                   else 'Q0' end

I'm guessing the solution revolves around how to calculate that -728 automatically (which would be 364 * n), and how to increment @startyear accordingly.

Any ideas on how to determine the fiscal year and quarter for a given date with these odd fiscal quarters?

Thanks!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source