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

Question

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?

user_61305 · Answer

Just create a permanent table called Quarters.

CREATE TABLE dbo.Quarters
(
  StartDate     date,
  QuarterNumber tinyint,
  FiscalYear    int,
  NextQuarterStartDate AS (DATEADD(MONTH, 3, StartDate))
);

INSERT dbo.Quarters(StartDate, QuarterNumber, FiscalYear)
  VALUES('20200201',1,2020),
        ('20200501',2,2020),
        ('20200801',3,2020),
        ('20201101',4,2020),
        ('20210201',1,2021),
        ('20210501',2,2021),
        ('20210801',3,2021),
        ('20211101',4,2021),
        ('20220201',1,2022),
        ('20220501',2,2022),
        ('20220801',3,2022),
        ('20221101',4,2022);

Now any time you are given a date (like GETDATE()) you can find the other information easily:

DECLARE @date date = GETDATE();

SELECT * FROM dbo.Quarters 
  WHERE @date >= StartDate
  AND @date < NextQuarterStartDate;

Example db<>fiddle

If you need to support multiple fiscal calendars simultaneously, just add a column (like CalendarID or CompanyID or CustomerID).

And really, you don't even need a calendar or quarters table for this. You already have a table of clients, right? Just add a column to store what month their fiscal year starts. That's really all you need.

CREATE TABLE dbo.Clients
(
  ClientID        int           NOT NULL CONSTRAINT PK_Clients PRIMARY KEY,
  Name            nvarchar(200) NOT NULL CONSTRAINT UQ_ClientName UNIQUE,
  FiscalYearStart tinyint       NOT NULL CONSTRAINT CK_ValidMonth 
    CHECK (FiscalYearStart BETWEEN 1 AND 12)
);

Now let's insert a few rows with some clients with different fiscal years:

INSERT dbo.Clients(ClientID, Name, FiscalYearStart)
  VALUES(1, N'ClientFeb',    2), -- fiscal year starts in February 
        (2, N'ClientMay',    5), -- fiscal year starts in May
        (3, N'ClientNormal', 1); -- fiscal year matches calendar

Now, yes, we need a function, but let's not do any while loops or counters or #temp tables.

CREATE FUNCTION dbo.GetLast16Quarters
(
  @DateToTest date,
  @ClientID   int
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN 
(
  WITH n(n) AS
  (
    SELECT n = 1 UNION ALL 
    SELECT n + 1 FROM n WHERE n < 20
  ),
  Last20Quarters(QuarterStart, FiscalYearStart) AS
  (
    SELECT QuarterStart = DATEADD(QUARTER, 1-n, 
      DATEFROMPARTS(YEAR(@DateToTest)+1, FiscalYearStart, 1)), 
      FiscalYearStart
    FROM dbo.Clients CROSS JOIN n WHERE ClientID = @ClientID
  ),
  Last16Quarters AS
  (
    SELECT TOP (16) QuarterStart, 
      y = YEAR(DATEADD(MONTH, 1-FiscalYearStart, QuarterStart)) 
    FROM Last20Quarters WHERE QuarterStart < @DateToTest
    ORDER BY QuarterStart DESC
  )
  SELECT QuarterStart, 
         QuarterEnd = EOMONTH(QuarterStart, 2),
         FiscalYear = y,
         QuarterNumber = ROW_NUMBER() OVER 
           (PARTITION BY y ORDER BY QuarterStart)
  FROM Last16Quarters);

Then to call it:

DECLARE @DateToTest date = '20211215';
SELECT * FROM dbo.GetLast16Quarters(@DateToTest, 1);

Output:

QuarterStart QuarterEnd FiscalYear QuarterNumber

2018-02-01 2018-04-30 2018 1

2018-05-01 2018-07-31 2018 2

2018-08-01 2018-10-31 2018 3

2018-11-01 2019-01-31 2018 4

2019-02-01 2019-04-30 2019 1

2019-05-01 2019-07-31 2019 2

2019-08-01 2019-10-31 2019 3

2019-11-01 2020-01-31 2019 4

2020-02-01 2020-04-30 2020 1

2020-05-01 2020-07-31 2020 2

2020-08-01 2020-10-31 2020 3

2020-11-01 2021-01-31 2020 4

2021-02-01 2021-04-30 2021 1

2021-05-01 2021-07-31 2021 2

2021-08-01 2021-10-31 2021 3

2021-11-01 2022-01-31 2021 4

Example db<>fiddle

user_87015 · Answer

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

user_1783592 · Answer

I ended up creating a function to handle this. Since I'm given the last day of the quarter, which quarter it is, and which year it is, I can determine the start and end date of that fiscal year. Since a quarter is always 3 months, I can also determine which months fall into which quarter.

The first 4 variables, @qa, @qb, @qc, @qd hold a comma separated list of the months within each quarter (@qa is current quarter, @qb is current quarter -1, @qc is current quarter -2, and @qd is current quarter -3)

The second 2 variables determine the first day and last day of the fiscal calendar

To get the quarter & year, I first get the month from the supplied date (@Date) and see if it's in @qa, @qb, @qc or @qd. That tells me the fiscal quarter.

Finally, I compare the given date to the start and end date of the current fiscal year, and to the 6 years prior (going back 6 years is enough for what I need)

CREATE FUNCTION [dbo].[FunctionNameHere]
(
    @Date           DATE
,   @QuarterEnd     DATE
,   @CurrentQuarter INT
,   @CurrentYear    INT
)
RETURNS VARCHAR(7)
AS
BEGIN

DECLARE     @qa                 VARCHAR(8)  = (concat(datepart(m, dateadd(m,  0, @QuarterEnd)),',', datepart(m, dateadd(m, -1,  @QuarterEnd)),',', datepart(m, dateadd(m, -2,  @QuarterEnd))))
DECLARE     @qb                 VARCHAR(8)  = (concat(datepart(m, dateadd(m, -3, @QuarterEnd)),',', datepart(m, dateadd(m, -4,  @QuarterEnd)),',', datepart(m, dateadd(m, -5,  @QuarterEnd))))
DECLARE     @qc                 VARCHAR(8)  = (concat(datepart(m, dateadd(m, -6, @QuarterEnd)),',', datepart(m, dateadd(m, -7,  @QuarterEnd)),',', datepart(m, dateadd(m, -8,  @QuarterEnd))))
DECLARE     @qd                 VARCHAR(8)  = (concat(datepart(m, dateadd(m, -9, @QuarterEnd)),',', datepart(m, dateadd(m, -10, @QuarterEnd)),',', datepart(m, dateadd(m, -11, @QuarterEnd))))
DECLARE     @YearStart          DATE        = DATEADD(d, 1, DATEADD(q, -@CurrentQuarter, @QuarterEnd))
DECLARE     @YearEnd            DATE        = DATEADD(q, 4-@CurrentQuarter, @QuarterEnd)

DECLARE     @Qtr                VARCHAR(8)  =   CONCAT('Q', CASE WHEN DATEPART(m, @Date) IN (SELECT value FROM string_split(@qa, ',')) THEN @CurrentQuarter 

                                                                 WHEN DATEPART(m, @Date) IN (SELECT value FROM string_split(@qb, ',')) THEN CASE WHEN @CurrentQuarter = 1 THEN 4
                                                                                                                                                 WHEN @CurrentQuarter = 2 THEN 1
                                                                                                                                                 WHEN @CurrentQuarter = 3 THEN 2
                                                                                                                                                 WHEN @CurrentQuarter = 4 THEN 3 END

                                                                 WHEN DATEPART(m, @Date) IN (SELECT value FROM string_split(@qc, ',')) THEN CASE WHEN @CurrentQuarter = 1 THEN 3
                                                                                                                                                 WHEN @CurrentQuarter = 2 THEN 4
                                                                                                                                                 WHEN @CurrentQuarter = 3 THEN 1
                                                                                                                                                 WHEN @CurrentQuarter = 4 THEN 2 END

                                                                 WHEN DATEPART(m, @Date) IN (SELECT value FROM string_split(@qd, ',')) THEN CASE WHEN @CurrentQuarter = 1 THEN 2
                                                                                                                                                 WHEN @CurrentQuarter = 2 THEN 3
                                                                                                                                                 WHEN @CurrentQuarter = 3 THEN 4
                                                                                                                                                 WHEN @CurrentQuarter = 4 THEN 1 END
                                                END,
                                                ' ',
                                                CASE WHEN @Date BETWEEN @YearStart AND @YearEnd THEN @CurrentYear
                                                     WHEN @Date BETWEEN dateadd(Year, -1, @YearStart) AND dateadd(Year, -1, @YearEnd) THEN @CurrentYear - 1
                                                     WHEN @Date BETWEEN dateadd(Year, -2, @YearStart) AND dateadd(Year, -2, @YearEnd) THEN @CurrentYear - 2
                                                     WHEN @Date BETWEEN dateadd(Year, -3, @YearStart) AND dateadd(Year, -3, @YearEnd) THEN @CurrentYear - 3
                                                     WHEN @Date BETWEEN dateadd(Year, -4, @YearStart) AND dateadd(Year, -4, @YearEnd) THEN @CurrentYear - 4
                                                     WHEN @Date BETWEEN dateadd(Year, -5, @YearStart) AND dateadd(Year, -5, @YearEnd) THEN @CurrentYear - 5
                                                     WHEN @Date BETWEEN dateadd(Year, -6, @YearStart) AND dateadd(Year, -6, @YearEnd) THEN @CurrentYear - 6
                                                     ELSE 9999 END)

    return @Qtr

END

QuarterStart	QuarterEnd	FiscalYear	QuarterNumber
2018-02-01	2018-04-30	2018	1
2018-05-01	2018-07-31	2018	2
2018-08-01	2018-10-31	2018	3
2018-11-01	2019-01-31	2018	4
2019-02-01	2019-04-30	2019	1
2019-05-01	2019-07-31	2019	2
2019-08-01	2019-10-31	2019	3
2019-11-01	2020-01-31	2019	4
2020-02-01	2020-04-30	2020	1
2020-05-01	2020-07-31	2020	2
2020-08-01	2020-10-31	2020	3
2020-11-01	2021-01-31	2020	4
2021-02-01	2021-04-30	2021	1
2021-05-01	2021-07-31	2021	2
2021-08-01	2021-10-31	2021	3
2021-11-01	2022-01-31	2021	4

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

3 Answers