SQL query to return fiscal year and quarters

Question

There is a table that lists each of four annual quarters and their start/end days and months.

Given this table and a start year of, say, 2017, how can I get a list of quarters in (end) date and name format, such as Q3 2017, '06/30/2017'?

I tried this but only get the current quarter:

select q.QID, q.QuarterName, q.StartMonth, q.StartDay, q.EndMonth, q.EndDay,
datefromparts(datepart (yy, '1/1/2017'), q.StartMonth, q.StartDay) StartQuarter, 
datefromparts(datepart (yy, '6/28/2018'), q.EndMonth, q.EndDay) EndQuarter 
from Quarters q 
where datefromparts(datepart (yy, '6/28/2018'), q.EndMonth, q.EndDay) < '06/30/2018';

The "Quarters" table looks like:

QID QuarterName     StartMonth  StartDay    EndMonth    EndDay
1   First Quarter   10          1           12          31
2   Second Quarter  1           1           3           31
3   Third Quarter   4           1           6           30
4   Fourth Quarter  7           1           9           30

Not sure if this is relevant or not but it is used in .Net/C# environment.

S3S · Answer 1 · 2018-06-28T18:23:37.647

This seems to be what you want. You can use a recursive CTE and ignore this table to do this for multiple years...

declare @quarters table (QID int, QuarterName varchar(64), StartMonth int,  StartDay int,  EndMonth int,    EndDay int)
insert into @quarters
values

(1,'First Quarter',10,1,12,31),
(2,'Second Quarter',1,1,3,31),
(3,'Third Quarter',4,1,6,30),
(4,'Fourth Quarter',7,1,9,30)


declare @year int = '2018'

select
    *
    ,convert(varchar,convert(date,cast(@year as varchar) + right('0' + cast(EndMonth as varchar),2)  + cast(EndDay as varchar)),101) 
from @quarters

Or, recursive CTE...

;with cte as(
    select 
        *
        ,EndDate = convert(date,convert(date,cast(datepart(year,@startDate) as varchar) + right('0' + cast(EndMonth as varchar),2)  + cast(EndDay as varchar)))
    from @quarters
    union all
    select
        Qid
        ,QuarterName
        ,StartMonth
        ,StartDay
        ,EndMonth
        ,EndDay
        ,dateadd(year,1,EndDate)
    from cte
    where datepart(year,EndDate) <= datepart(year,@endDate)
    )

select
        Qid
        ,QuarterName
        ,StartMonth
        ,StartDay
        ,EndMonth
        ,EndDay
        ,EndDate = convert(varchar,EndDate,101)
from cte

score 0 · Answer 2 · answered Jun 28 '18 at 18:57

Perhaps another option use CHOOSE since your Fiscal Year is shifted one QTR

Example

Declare @Date1 date = '2017-10-01'

Select DateR1 = D
      ,DateR2 = EOMONTH(D)
      ,AsString = concat(choose(DatePart(QQ,D),'Q2','Q3','Q4','Q1'),' ',year(D)-IIF(DatePart(QQ,D)<4,1,0) )
      ,AsDate   = format(EOMonth(D),'MM/dd/yyyy')
From (
        Select Top (12) D=DateAdd(MONTH,-1+Row_Number() Over (Order By (Select Null)),@Date1) From  master..spt_values n1,master..spt_values n2
     ) A

Returns

DateR1      DateR2      AsString    AsDate
2017-10-01  2017-10-31  Q1 2017     10/31/2017
2017-11-01  2017-11-30  Q1 2017     11/30/2017
2017-12-01  2017-12-31  Q1 2017     12/31/2017
2018-01-01  2018-01-31  Q2 2017     01/31/2018
2018-02-01  2018-02-28  Q2 2017     02/28/2018
2018-03-01  2018-03-31  Q2 2017     03/31/2018
2018-04-01  2018-04-30  Q3 2017     04/30/2018
2018-05-01  2018-05-31  Q3 2017     05/31/2018
2018-06-01  2018-06-30  Q3 2017     06/30/2018
2018-07-01  2018-07-31  Q4 2017     07/31/2018
2018-08-01  2018-08-31  Q4 2017     08/31/2018
2018-09-01  2018-09-30  Q4 2017     09/30/2018

Salman A · Accepted Answer · 2018-06-29T19:08:08.697

Try this:

DECLARE @year INT = 2017;

WITH numbers(n) AS (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
  FROM       (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS v1(x)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS v2(x)
)
SELECT CONCAT('Q', QID, ' ', @year + n) AS QuarterName, EndDate
FROM numbers
CROSS JOIN Quarters
CROSS APPLY (SELECT
  DATEFROMPARTS(@year + n - CASE WHEN QID = 1 THEN 1 ELSE 0 END, StartMonth, StartDay) AS StartDate,
  DATEFROMPARTS(@year + n - CASE WHEN QID = 1 THEN 1 ELSE 0 END, EndMonth, EndDay) AS EndDate
) AS CA
WHERE StartDate <= CURRENT_TIMESTAMP ORDER BY EndDate

The numbers common table expression generates numbers between 0 and 99 (see this question). These are cross multiplied by Quarters table. The dates are generated by adding @year to each number and adjusted since first quarter of year n belongs to n - 1^th year. Cross apply makes it easier to reference calculated values inside WHERE, SELECT and ORDER BY clause.

Result:

| QuarterName | EndDate    |
|-------------|------------|
| Q1 2017     | 2016-12-31 |
| Q2 2017     | 2017-03-31 |
| Q3 2017     | 2017-06-30 |
| Q4 2017     | 2017-09-30 |
| Q1 2018     | 2017-12-31 |
| Q2 2018     | 2018-03-31 |
| Q3 2018     | 2018-06-30 |

SQL Fiddle with slight modification

Thank you very much for everyone's response but Salman A's answer was what i was looking for. — NoBullMan, Jun 29 '18 at 18:40

SQL query to return fiscal year and quarters

3 Answers3