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

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!

CodePudding user response：

Here is a solution which does an integer division to get the number of years after 2018. We then subtract 364 times this number from DATEDIFF the base year to get the number of days from the start of the current tax year. We can use your case statement to determine the quarter.

create function dbo.quarter (@date date)
returns varchar(100)
as
begin
declare @days int = datediff(dd,'2018-04-02',@date)
declare @years int = @days / 364
set @days  = @days - (@years * 364)
set @years = @years   2018
declare @quarter char(2)= case 
      when @days between 0   and 111 then 'Q1'
      when @days between 112 and 195 then 'Q2'
      when @days between 196 and 279 then 'Q3'
      when @days between 280 and 363 then 'Q4'
      else 'Q0' end
      return concat(@years,'-', @quarter)
 end

GO

✓

select dbo.quarter('5/4/2018') quarter;
select dbo.quarter('2020-04-02') quarter;
select dbo.quarter('2022-01-24') quarter;
select dbo.quarter(GETDATE()) quarter;
select dbo.quarter('2021-12-25') quarter;
GO

| quarter |
| :------ |
| 2018-Q1 |

| quarter |
| :------ |
| 2020-Q1 |

| quarter |
| :------ |
| 2021-Q4 |

| quarter |
| :------ |
| 2021-Q4 |

| quarter |
| :------ |
| 2021-Q3 |

db<>fiddle here