SQL to SELECT last 4 Financial Quarters

Question

I'm learning SQL and I have this horrible looking query to select the last 4 financial/fiscal quarters and how many quarters ago they were.

• This is using Australian financial year (June - July)
• The Financial Year 2018 is July 2017 to June 2018
• FinancialQuarterID is structured like YYYYQ

If the date is 28th March 2018 then the table returned is like:

╔═══╦════════════╦═════════════╗  
║   ║ ID         ║ QuartersAgo ║  
╠═══╬════════════╬═════════════╣  
║ 1 ║ 20182      ║ 1           ║  
║ 2 ║ 20181      ║ 2           ║  
║ 3 ║ 20174      ║ 3           ║  
║ 4 ║ 20173      ║ 4           ║  
╚═══╩════════════╩═════════════╝  

My query is below. Surely there is a much better and more efficient way... What I like about this is that it works and I can commit it to version control (unlike Excel and PowerBI that I would usually build this thing in).

In the first phase I am determining the financial year based on the month of the calendar year.

In a similar way I am then breaking the years down into Quarters to determine the current financial quarter.

Once I have found that I am setting and determining values for the nth quarter ago.

What I don't like is that I feel like I should just be inserting values into the variable table as I am determining which quarter I am. Instead I assign to a value and then insert that later on.

DECLARE @Today DATE = GETDATE();

DECLARE @ThisCalendarYear VARCHAR(4)
DECLARE @ThisFinancialYear VARCHAR(4)
DECLARE @ThisCalendarMonth INT

DECLARE @Last4FinancialQuarters TABLE(
    ID VARCHAR(5) NOT NULL,
    QuartersAgo INT NOT NULL
);

DECLARE @LastFinancialQuarter VARCHAR(5)
DECLARE @SecondLastFinancialQuarter VARCHAR(5)
DECLARE @ThirdLastFinancialQuarter VARCHAR(5)
DECLARE @FourthLastFinancialQuarter VARCHAR(5)

SET @ThisCalendarYear = DatePart(Year, @Today)
SET @ThisCalendarMonth = DatePart(Month, @Today)

IF DatePart(Month, @Today) <= 6
  SET @ThisFinancialYear = DatePart(Year, @Today)
ELSE 
  SET @ThisFinancialYear = DatePart(Year, @Today) +1 

SET @LastFinancialQuarter = CASE
  WHEN  @ThisCalendarMonth <= 3 THEN CONCAT(@ThisCalendarYear     ,4 - 2)
  WHEN  @ThisCalendarMonth <= 6 THEN CONCAT(@ThisCalendarYear     ,4 - 1)
  WHEN  @ThisCalendarMonth <= 9 THEN CONCAT(@ThisCalendarYear     ,4 - 0)
  WHEN  @ThisCalendarMonth <= 12 THEN CONCAT(@ThisCalendarYear + 1,4 - 3)
END

SET @SecondLastFinancialQuarter = CASE
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 4 THEN CONCAT(@ThisFinancialYear,3)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 3 THEN CONCAT(@ThisFinancialYear,2)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 2 THEN CONCAT(@ThisFinancialYear,1)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 4 THEN CONCAT(@ThisFinancialYear - 1,4)
END

SET @ThirdLastFinancialQuarter = CASE
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 4 THEN CONCAT(@ThisFinancialYear,2)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 3 THEN CONCAT(@ThisFinancialYear,1)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 2 THEN CONCAT(@ThisFinancialYear - 1,4)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 4 THEN CONCAT(@ThisFinancialYear - 1,3)
END

SET @FourthLastFinancialQuarter = CASE
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 4 THEN CONCAT(@ThisFinancialYear,1)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 3 THEN CONCAT(@ThisFinancialYear - 1,4)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 2 THEN CONCAT(@ThisFinancialYear - 1,3)
  WHEN SUBSTRING(@LastFinancialQuarter, 5,1) = 4 THEN CONCAT(@ThisFinancialYear - 1,2)
END


INSERT INTO @Last4FinancialQuarters (ID, QuartersAgo)
VALUES
    (@LastFinancialQuarter, 1),
    (@SecondLastFinancialQuarter, 2),
    (@ThirdLastFinancialQuarter, 3),
    (@FourthLastFinancialQuarter, 4);

SELECT * FROM @Last4FinancialQuarters

Would appreciate some feedback :) Thanks

Answer 1

I have also use Recursive CTE to solve this query (I did take help from above @Max solution)-

declare @Today date = getdate()
declare @CalendarMonth int = datepart(month, @Today)
declare @LastFinancialQuarterDate date = dateadd(month, (case when @CalendarMonth <= 6 then 6 else 18 end) - @CalendarMonth, @Today)

;with cte as
(
    select  cast(datepart(year, @LastFinancialQuarterDate) as varchar(4))
            +
            cast(datepart(quarter, @LastFinancialQuarterDate) as varchar(1)) as ID,
            1 as QuartersAgo
    union all
    select  cast(datepart(year, dateadd(month, cte.QuartersAgo * -3, @LastFinancialQuarterDate)) as varchar(4))
            +
            cast(datepart(quarter, dateadd(month, cte.QuartersAgo * -3, @LastFinancialQuarterDate)) as varchar(1)) as ID,
            (cte.QuartersAgo + 1) as QuartersAgo
    from cte
    where cte.QuartersAgo < 4
)
select * from cte

First thing I did is to find date which belongs to last Australian quarter and set it to @LastFinancialQuarterDate variable. After that I have use that variable in Recursive CTE to traverse last four quarters.

Answer 2

Recursive CTE to the rescue:

;WITH cte AS
(
    SELECT 1 as QuartersAgo, GETDATE() as DT, 
        CAST(YEAR(DATEADD(MONTH, 3, GETDATE())) AS VARCHAR(4)) + CAST(DATEPART(QUARTER, DATEADD(MONTH, 3, GETDATE())) AS VARCHAR(1)) as FinancialQuarter
    UNION ALL 
    SELECT QuartersAgo + 1, DATEADD(MONTH, -3, cte.DT), 
        CAST(YEAR(DATEADD(MONTH, 3, DATEADD(MONTH, -3, cte.DT))) AS VARCHAR(4)) + CAST(DATEPART(QUARTER, DATEADD(MONTH, 3, DATEADD(MONTH, -3, cte.DT))) AS VARCHAR(1))
    FROM cte
    WHERE QuartersAgo < 4
)
SELECT FinancialQuarter, QuartersAgo FROM cte

Here's the output:

FinancialQuarter    QuartersAgo
20182               1
20181               2
20174               3
20173               4

Here are some important points:

1. To get today's date as an Australian fiscal quarter, add 3 months and concat the year to the quarter (which you can get with DATEPART(QUARTER(DATE)), so, like this:

   CAST(YEAR(DATEADD(MONTH, 3, GETDATE())) AS VARCHAR(4)) + CAST(DATEPART(QUARTER, DATEADD(MONTH, 3, GETDATE())) AS VARCHAR(1))

2. A CTE (common table expression) is kind of like a temp table that's in scope for a query. That's a simplistic explanation. CTEs are worth reading about!
3. You can create a recursive CTE using UNION ALL - the part of the query before the UNION ALL is the anchor, and the recursive part comes after. In this case, I use WHERE QuartersAgo < 4 to stop the query from recursing after a few unions.
4. The CTE starts with ;WITH - all CTEs start with "WITH", and the semicolon just terminates any hanging chads that were dangling in front of the CTE definition.
5. After the parentheses surrounding the CTE definition, you can query from the CTE. But only once. After that, the CTE is out of scope.
6. It's possible to nest a bunch of CTEs - a CTE can refer to CTEs that precede it, but not those that follow it.