Find the quarterly periods within any Financial Year

Question

I am trying to produce a report in SSRS that shows me a number of jobs within each quarter within a financial year. I have the days/months of each companies FY stored in a database, which I use a query to find current or previous FY start/end dates.

DECLARE @FirstDate DATETIME
DECLARE @LastDate DATETIME
DECLARE @CurrentDate DATETIME
DECLARE @CompanyID BIGINT

SET @CurrentDate = GETDATE()
SET @CompanyID = 631


SELECT  @FirstDate = CASE WHEN DATEFROMPARTS(Year(@CurrentDate), Month(c.FinancialYearStart), Day(c.FinancialYearStart)) <= GETDATE() THEN  
                                                              -- We are in the right Year
                                                              DATEFROMPARTS(Year(@CurrentDate), Month(c.FinancialYearStart), Day(c.FinancialYearStart))
                                                       ELSE
                                                              -- we need to go back a year
                                                              DATEFROMPARTS(Year(@CurrentDate)-1, Month(c.FinancialYearStart), Day(c.FinancialYearStart))
                                                       END
       , @LastDate = CASE WHEN DATEFROMPARTS(Year(@CurrentDate), Month(c.FinancialYearStart), Day(c.FinancialYearStart)) <= GETDATE() THEN  
                                                              -- We are in the right Year
                                                              DATEADD(day, -1, DATEADD(year, 1, DATEFROMPARTS(Year(@CurrentDate), Month(c.FinancialYearStart), Day(c.FinancialYearStart))))
                                                       ELSE
                                                              -- we need to go back a year
                                                              DATEADD(day, -1, DATEADD(year, 1, DATEFROMPARTS(Year(@CurrentDate)-1, Month(c.FinancialYearStart), Day(c.FinancialYearStart))))
                                                       END
FROM tCompany c
WHERE c.ID = @CompanyID

How can I query each quarters for the FY from the @FirstDate?

E.g. if a companies FY was 01/04 - 31/03 how can I determine each quarter months?

Quarter 1 - Apr, May, Jun Quarter 2 - Jul, Aug, Sept Quarter 3 - Oct, Nov, Dec Quarter 4 - Jan, Feb, Mar

Answer 1

Usually one will define fiscal quarters specifically as a field on a calendar table, since often they don't operate as expected - 4-4-5 or 4-5-4 accounting, for example.

If you are just looking at calendar months, the use of a CASE WHEN expression and the MONTH() function might work. You'll need to figure out what the "starting" month is for that specific fiscal year, then use that to convert the MONTH() to a fiscal month 1 - 12, then CASE WHEN fisalMonth in (1,2,3) ... etc.

Really though - use a calendar table. See Aaron Bertrand's article here: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Answer 2

Step 1: Create @FirstDate Variable

DECLARE @FirstDate DATETIME = '05/31/2015'

Step 2: Create SQL To Find Each Quarter

SELECT
    DATEPART(MONTH,@FirstDate) AS [Q1 Start],
    DATEPART(MONTH, DATEADD(MONTH, 3, @FirstDate)) AS [Q2 Start],
    DATEPART(MONTH, DATEADD(MONTH, 6, @FirstDate)) AS [Q3 Start],
    DATEPART(MONTH, DATEADD(MONTH, 9, @FirstDate)) AS [Q4 Start]

Output:

=====================================
|Q1 Start|Q2 Start|Q3 Start|Q4 Start|
=====================================
|5       |8       |11      |2       |
=====================================