title: SQL to SELECT last 4 Financial Quarters
body: <p>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. </p>
  
  <ul>
  <li>This is using Australian financial year (June - July)</li>
  <li>The Financial Year 2018 is July 2017 to June 2018</li>
  <li>FinancialQuarterID is structured like YYYYQ</li>
  </ul>
  
  <p>If the date is 28th March 2018 then the table returned is like:</p>
  
  <pre><code>╔═══╦════════════╦═════════════╗  
  ║   ║ ID         ║ QuartersAgo ║  
  ╠═══╬════════════╬═════════════╣  
  ║ 1 ║ 20182      ║ 1           ║  
  ║ 2 ║ 20181      ║ 2           ║  
  ║ 3 ║ 20174      ║ 3           ║  
  ║ 4 ║ 20173      ║ 4           ║  
  ╚═══╩════════════╩═════════════╝  
  </code></pre>
  
  <p>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).</p>
  
  <p>In the first phase I am determining the financial year based on the month of the calendar year. </p>
  
  <p>In a similar way I am then breaking the years down into Quarters to determine the current financial quarter.</p>
  
  <p>Once I have found that I am setting and determining values for the nth quarter ago. </p>
  
  <p>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.</p>
  
  <pre><code>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) &lt;= 6
    SET @ThisFinancialYear = DatePart(Year, @Today)
  ELSE 
    SET @ThisFinancialYear = DatePart(Year, @Today) +1 
  
  SET @LastFinancialQuarter = CASE
    WHEN  @ThisCalendarMonth &lt;= 3 THEN CONCAT(@ThisCalendarYear     ,4 - 2)
    WHEN  @ThisCalendarMonth &lt;= 6 THEN CONCAT(@ThisCalendarYear     ,4 - 1)
    WHEN  @ThisCalendarMonth &lt;= 9 THEN CONCAT(@ThisCalendarYear     ,4 - 0)
    WHEN  @ThisCalendarMonth &lt;= 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
  </code></pre>
  
  <p>Would appreciate some feedback :) Thanks</p>