'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 |
|---|
