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?
CodePudding user response：
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
CodePudding user response：
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)
CodePudding user response：
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