Logic to get dynamic date range in query for Fiscal Year

Question

I have a requirement to select data for different date ranges, depending one the current month it is run on. I have a Stored Procedure that takes in a user supplied Date i.e. 04/06/2020 into the variable @PPE. Based on the Month portion of this date, I need to query back varying ranges as in a Fiscal Year (July 1 - June 30th) follows: I am using the example using a 2020 Financial Year.

MONTH(@PPE)     Date Range to Query
July            07/01/2019 - 07/01/2019
August          07/01/2019 - 08/31/2019
September       07/01/2019 - 09/30/2019
October         07/01/2019 - 09/30/2019
November        07/01/2019 - 09/30/2019
December        07/01/2019 - 09/30/2019
January         07/01/2019 - 12/31/2019
February        07/01/2019 - 12/31/2019
March           07/01/2019 - 12/31/2019
April           07/01/2019 - 03/31/2020
May             07/01/2019 - 03/31/2020
June            07/01/2019 - 03/31/2020

Then Starting next Fiscal Year (2021) it would go as follows, and onwards...

MONTH(@PPE)     Date Range to Query
July            07/01/2020 - 07/01/2020
August          07/01/2020 - 08/31/2020
September       07/01/2020 - 09/30/2020
October         07/01/2020 - 09/30/2020
November        07/01/2020 - 09/30/2020
December        07/01/2020 - 09/30/2020
January         07/01/2020 - 12/31/2020
February        07/01/2020 - 12/31/2020
March           07/01/2020 - 12/31/2020
April           07/01/2019 - 03/31/2021
May             07/01/2019 - 03/31/2021
June            07/01/2019 - 03/31/2021

I started with this query , but I'm having trouble manipulating the correct FISCAL_YEAR to put in, depending on what the month of the @PPE date is, because it spans 2 years between April and June.

PERIOD represents the Fiscal Month number i.e. July = 1 , August = 2, ....June = 12

DECLARE @PPE datetime; SET @PPE = '04/06/2020'

SELECT COMPANY, DEPTID,  FISCAL_YEAR, PERIOD, GH_REPORTID,  SUM(GH_HOURS), SUM(GH_DOLLARS)
FROM PS_GH_SLT_ACTUALS
WHERE DEPTID = 55650000
AND GH_REPORTID = 'BA'
AND PERIOD <= CASE WHEN MONTH(@PPE) BETWEEN 1 AND 3 THEN 12
                   WHEN MONTH(@PPE) BETWEEN 4 AND 6 THEN 3
                   WHEN MONTH(@PPE) BETWEEN 7 AND 9 THEN 6
                   WHEN MONTH(@PPE) BETWEEN 10 AND 12 THEN 9
                   END
-- AND PERIOD <= 7  ???
AND FISCAL_YEAR = CASE WHEN MONTH(@PPE) BETWEEN 1 AND 3 THEN YEAR(@PPE)
                       WHEN MONTH(@PPE) BETWEEN 4 AND 6 THEN ....???


GROUP BY COMPANY, DEPTID,  FISCAL_YEAR, PERIOD, GH_REPORTID

Generally speaking, a date or calendar table is the way to go for this type of query. That table would include Fiscal Year in some kind of fashion. Look into data warehouse date dimension tables. — devlin carnate

CR7SMS CR7SMS · Accepted Answer · 2020-02-11T19:47:50

If your fiscal year is already in the format, then you can use the following and condition:

FISCAL_YEAR = CASE WHEN MONTH(@PPE) BETWEEN 1 AND 6 THEN YEAR(@PPE)
                      ELSE YEAR(@PPE)+1 END

The above condition should work under the assumption that, Jul 2019 to Jun 2020 is called as Fiscal year 2020, as per your data. Hope this helps.

Logic to get dynamic date range in query for Fiscal Year

1 Answers