How to split a time frame in one row to different quarters in different rows in T-SQL

Question

I need to show the distribution membership in year and quarter. I am using SQL Server 2012.

The data look like this:

CREATE TABLE MyTable
(
    MyGroup nvarchar(10) NULL,
    StartTime nvarchar(10) NULL,
    EndTime nvarchar (10) NULL,
    Quantity int NULL
)

Insert into MyTable 
Values ('a', '7/1/2014', '6/30/20116', '10'),
       ('b', '12/1/2013', '11/30/2014', '8')

The desired result:

MyGroup StartTime   EndTime Year    Quarter Quantity
a   7/1/2014    6/30/2016   2014    2014-Q3  10
a   7/1/2014    6/30/2016   2014    2014-Q4  10
a   7/1/2014    6/30/2016   2015    2015-Q1  10
a   7/1/2014    6/30/2016   2015    2015-Q2  10
a   7/1/2014    6/30/2016   2015    2015-Q3  10
a   7/1/2014    6/30/2016   2015    2015-Q4  10
a   7/1/2014    6/30/2016   2016    2016-Q1  10
a   7/1/2014    6/30/2016   2016    2016-Q2  10
b   12/1/2013   11/30/2014  2013    2013-Q4  8
b   12/1/2013   11/30/2014  2014    2014-Q1  8
b   12/1/2013   11/30/2014  2014    2014-Q2  8
b   12/1/2013   11/30/2014  2014    2014-Q3  8

You should be storing dates in a column of type [`DATE`](https://msdn.microsoft.com/en-us/library/bb630352.aspx) instead of using `VARCHAR`. If you won't do that (you should really), at least store your dates as `YYYYMMDD`. — TT., Feb 28 '16 at 10:26

score 1 · Answer 1 · edited Feb 28 '16 at 15:10

1

There are two ways to make this:

By creating 3 lookup tables (Quarters, Years and YearQuarters) to help you in the joins and this will make the query very easy
Without changing the database or creating any extra tables but this will make the query very complex and it will include some repeated code

edited Feb 28 '16 at 15:10

nathan_jr

9,092
3
40
55

answered Feb 28 '16 at 05:51

Hazem Torab

369
1
10

score 1 · Answer 2 · edited May 23 '17 at 12:15

Here I use a Numbers table to explode the days as rows from your Start-->End range, group that day range by quarter, then extract the year:quarter.

declare @MyTable table 
(
    MyGroup nvarchar(10) NULL,
    StartTime date NULL,
    EndTime date NULL,
    Quantity int NULL
);

Insert into @MyTable Values
('a',   '7/1/2014', '6/30/2016',   '10'),
('b',   '12/1/2013',    '11/30/2014',   '8')


;with 
DaysBetween (MyGroup, MyDate) as
(   select  mt.MyGroup,
            dateadd(day, n-1, mt.StartTime) 
    from    @MyTable mt
    cross
    apply   dbo.Number n
    where   n.N <= datediff(day, mt.StartTime, mt.EndTime)
),
AsQuarters (MyGroup, StartOfQuarter) as
(   select  MyGroup, dateadd(quarter, datediff(quarter, 0, MyDate), 0)
    from    DaysBetween
    group
    by      MyGroup, dateadd(quarter, datediff(quarter, 0, MyDate), 0)
)
select  MyGroup, datepart(year, StartOfQuarter), datepart(quarter, StartOfQuarter)
from    AsQuarters
order
by      1, 2, 3;

Returns:

MyGroup year    quarter
------- ----    -------
a       2014    3
a       2014    4
a       2015    1
a       2015    2
a       2015    3
a       2015    4
a       2016    1
a       2016    2
b       2013    4
b       2014    1
b       2014    2
b       2014    3
b       2014    4 <-- Did you forget this one?

Sagar · Accepted Answer · 2016-02-28T07:51:39.420

Try below query. You need to optimize query by introducing indexes in your table in case you are doing this operation with large data.

;With Quarters as (
    select MyGroup,StartTime as StartTime1 ,DATEADD(quarter,DATEDIFF(quarter,0,StartTime),0) as StartTime , Endtime, Quantity from MyTable 
    union all
    select MyGroup,StartTime1,DATEADD(quarter,1,StartTime) , Endtime  , Quantity
    from Quarters
    where StartTime < DATEADD(quarter,DATEDIFF(quarter,0,EndTime),0)
)
select MyGroup, Convert(varchar(10),StartTime,110) as StartTime, Convert(varchar(10),EndTime,110) as EndTime, DATEPART(YEAR,StartTime) as Years,
   -- CONVERT(varchar(3),StartTime,109) + ' ' + CONVERT(varchar(4),StartTime,120) as QuarterMonth , 
   CONVERT(varchar(4),StartTime,120) + '-Q' + 
    CAST(CEILING(CAST(month(StartTime) AS decimal(4,2)) / 3) AS char(1)) AS SelectQuarter , Quantity
from Quarters order by Quantity desc

How to split a time frame in one row to different quarters in different rows in T-SQL

3 Answers3