Number of days by quarter between StartDate and EndDate - SQL Server

Question

I need to fill up columns by number of day in quarters. Time period is about 10 years. Structure of tables is below. Thanks for any suggestions

SELECT StartDate, EndDate
INTO #tmp_RTX 
FROM DateTable


         StartDate                 EndDate
-------------------------------------------------
2015-11-01 00:00:00.000  2018-06-01 00:00:00.000
2017-09-02 00:00:00.000  2021-12-02 00:00:00.000
2016-01-02 00:00:00.000  2019-01-02 00:00:00.000
      .
      .
      .

2018-10-26 00:00:00.000  2020-10-26 00:00:00.000

  INSERT INTO DWHMart.[RTX].[RoadTax]
      (  ,[NuberOfaDaysInQuarter1]
         ,[NuberOfaDaysInQuarter2]
         ,[NuberOfaDaysInQuarter3]
         ,[NuberOfaDaysInOctoberNovember]
         ,[NuberOfaDaysInDecember]
         ,[NuberOfaDaysInTotal])

SELECT 
,[NuberOfaDaysInQuarter1] = NuberOfaDaysInQuarter1
,[NuberOfaDaysInQuarter2] = NuberOfaDaysInQuarter2
,[NuberOfaDaysInQuarter3] = NuberOfaDaysInQuarter3
,[NuberOfaDaysInOctoberNovember] = NuberOfaDaysInOctoberNovember
,[NuberOfaDaysInDecember] = NuberOfaDaysInDecember
,[NuberOfaDaysInTotal] = DATEDIFF(DAY, StartDate, EndDate)
FROM #tmp_RTX

EXPECTED RESULT:

StartDate                 EndDate                 NuberOfaDaysInQuarter1   ..
2015-11-01 00:00:00.000  2018-06-01 00:00:00.000        .
2017-09-02 00:00:00.000  2021-12-02 00:00:00.000        .
2016-01-02 00:00:00.000  2019-01-02 00:00:00.000        .
      .                                                 .
      .                                                 .
      .                                                 .

2018-10-26 00:00:00.000  2020-10-26 00:00:00.000        .

Welcome to Stack Overflow. I've afraid your goal is rather vague here., you're asking for suggestions rather than help with specific code your having problems with. At a pure guess, seems like you need a [Calendar Table](http://www.sqlservercentral.com/articles/calendar/145206/). Otherwise you'll want to expand on your question. Show the current data you have, and the expected results you are after; along with your attempt(s). Thanks. — Thom A, Dec 18 '18 at 12:49
You've not defined if your quarters follow calendar year quarters or if they are on a different fiscal year variant. — xQbert, Dec 18 '18 at 16:37

serge · Answer 1 · 2018-12-19T10:33:31.613

The number of days in quarters is always the same excluding the leap years. Hence you can use the constant expressions for Q2..Q4 and the parameterized value for Q1.

DECLARE @my_date date = '2017-01-01';
SELECT 89 + ISDATE(CAST(@my_date AS char(4)) + '0229') AS NuberOfaDaysInQuarter1,
       90 AS NuberOfaDaysInQuarter2,
       91 AS NuberOfaDaysInQuarter3,
       91 AS NuberOfaDaysInQuarter4

Updated solution (accumulating day count by quarters)

DECLARE @start_date date = '2000-01-01';
DECLARE @quarters TABLE (start_date date, end_date date, num int);
-- Create quarter calendar from @start_date
WITH nums (n) AS
(
   SELECT TOP 100 (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1) * 3
   FROM sys.columns sc1 CROSS JOIN sys.columns sc2
),
calendar AS
(
   SELECT n, DATEADD(month, n, @start_date) AS start_date
   FROM nums
)
INSERT INTO @quarters
SELECT start_date, 
       DATEADD(day, -1, DATEADD(month, 3, start_date)) AS end_date,
       ((n / 3) % 4) + 1 AS num
FROM calendar;

-- test data
DECLARE @t TABLE (start_date date, end_date date);
INSERT INTO @t VALUES
('2015-11-01', '2018-06-01'),
('2017-09-02', '2021-12-02'),
('2016-01-02', '2019-01-02');

-- Calculations
WITH inner_counts AS (
   SELECT t.start_date, t.end_date, q.num,
          SUM(datediff(day, q.start_date, q.end_date)) AS days_count
   FROM @t t INNER JOIN @quarters q ON q.start_date > t.start_date AND q.start_date < t.end_date
   GROUP BY t.start_date, t.end_date, q.num
),
outer_counts1 AS (
   SELECT t.start_date, t.end_date, q.num,
          SUM(datediff(day, t.start_date, q.end_date)) AS days_count
   FROM @t t INNER JOIN @quarters q ON q.end_date = (SELECT MIN(end_date) FROM @quarters q WHERE q.end_date >= t.start_date)
   GROUP BY t.start_date, t.end_date, q.num
),
outer_counts2 AS (
   SELECT t.start_date, t.end_date, q.num,
          SUM(datediff(day, q.start_date, t.end_date)) AS days_count
   FROM @t t INNER JOIN @quarters q ON q.start_date = (SELECT MAX(start_date) FROM @quarters q WHERE q.start_date <= t.end_date)
   GROUP BY t.start_date, t.end_date, q.num
),
total_counts AS (
   SELECT start_date, end_date, num, SUM(days_count) AS days_count
   FROM (SELECT * FROM inner_counts 
         UNION ALL 
         SELECT * FROM outer_counts1 
         UNION ALL 
         SELECT * FROM outer_counts2) c
   GROUP BY start_date, end_date, num
)
SELECT start_date, end_date,
       SUM(CASE WHEN num = 1 THEN days_count ELSE 0 END) AS days_in_Q1,
       SUM(CASE WHEN num = 2 THEN days_count ELSE 0 END) AS days_in_Q2,
       SUM(CASE WHEN num = 3 THEN days_count ELSE 0 END) AS days_in_Q3,
       SUM(CASE WHEN num = 4 THEN days_count ELSE 0 END) AS days_in_Q4
FROM total_counts
GROUP BY start_date, end_date

Result

start_date end_date   days_in_Q1  days_in_Q2  days_in_Q3  days_in_Q4
---------- ---------- ----------- ----------- ----------- -----------
2015-11-01 2018-06-01 268         331         182         242
2016-01-02 2019-01-02 357         270         273         273
2017-09-02 2021-12-02 357         360         392         517

But I need number of days by quarter between StartDate and EndDate — Michal, Dec 18 '18 at 13:57
DATEDIFF(DAY, StartDate, EndDate). I need to devide into quartals — Michal, Dec 18 '18 at 14:03
@Michal so the question is "the numbers of quarters between two dates"? The number of days in the quarter is constant. — serge, Dec 18 '18 at 14:52
for example : When StartDate is 2015-03-15 and EndDate 2015-04-05 it cant be same number of days in every qaurter. In Q1 should be 16(days) and in Q2 5(days). — Michal, Dec 18 '18 at 15:11

Cato · Answer 2 · 2018-12-19T10:12:34.310

I've made my own dynamic table of days using a recursive CTE.

Then I've counted by quarter the number of days from it in each range you've given

I used a CROSS JOIN for each of your rows, as they have no unique ID for a GROUP BY, it works in any case though

;WITH RNG AS (SELECT cast(MIN(StartDate) as date) as MN, 
                 cast(MAX (EndDate ) as date) MX FROM #tmp_RTX),
 DATS AS (SELECT MN FROM RNG
          UNION ALL 
          SELECT DATEADD(day,1,MN) FROM dats 
            WHERE dats.MN < (SELECT MX FROM RNG)
        )
        SELECT StartDate T1,
                Enddate T1, 
                DQ.NuberOfaDaysInQuarter1   ,
                DQ.NuberOfaDaysInQuarter2   ,
                DQ.NuberOfaDaysInQuarter3  , 
                DQ.NuberOfaDaysInQuarter4   
                    FROM #tmp_RTX T1
                        CROSS APPLY (SELECT 
                                    SUM(CASE WHEN datepart(quarter ,MN) = 1 THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInQuarter1,
                                    SUM(CASE WHEN datepart(quarter ,MN) = 2 THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInQuarter2,      
                                    SUM(CASE WHEN datepart(quarter ,MN) = 3 THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInQuarter3,
                                    SUM(CASE WHEN datepart(quarter ,MN) = 4 THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInQuarter4
                                                 FROM DATS
                                                 WHERE MN BETWEEN 
                                                            CAST(t1.startdate as date) 
                                                                and 
                                                                CAST(T1.EndDate as date)
                            ) DQ

        OPTION  (maxrecursion 0);

The following was what built my test data (based on the conventions of your data)

SELECT '20151101' StartDate, '20180601' EndDate
INTO #tmp_RTX 

INSERT into #tmp_RTX  values ('20161231','20180101');

INSERT into #tmp_RTX  values ('20181231','20190101');

To handle the different requirement for the final quarter, check the month, not the quarter

;WITH RNG AS (SELECT cast(MIN(StartDate) as date) as MN, 
                 cast(MAX (EndDate ) as date) MX FROM #tmp_RTX),
 DATS AS (SELECT MN FROM RNG
          UNION ALL 
          SELECT DATEADD(day,1,MN) FROM dats 
            WHERE dats.MN < (SELECT MX FROM RNG)
        )
        SELECT StartDate T1,
                Enddate T1, 
                DQ.NuberOfaDaysInQuarter1   ,
                DQ.NuberOfaDaysInQuarter2   ,
                DQ.NuberOfaDaysInQuarter3  , 
                DQ.NuberOfaDaysInOctoberNovember   ,
                DQ.NuberOfaDaysInDecember
                    FROM #tmp_RTX T1
                        CROSS APPLY (SELECT 
                                    SUM(CASE WHEN datepart(quarter ,MN) = 1 THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInQuarter1,
                                    SUM(CASE WHEN datepart(quarter ,MN) = 2 THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInQuarter2,      
                                    SUM(CASE WHEN datepart(quarter ,MN) = 3 THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInQuarter3,
                                    SUM(CASE WHEN datepart(month ,MN) IN(10,11) THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInOctoberNovember,
                                    SUM(CASE WHEN datepart(month ,MN) IN(12) THEN 1 ELSE 0 END) 
                                            NuberOfaDaysInDecember
                                                 FROM DATS
                                                 WHERE MN BETWEEN 
                                                            CAST(t1.startdate as date) 
                                                                and 
                                                                CAST(T1.EndDate as date)
                            ) DQ

        OPTION  (maxrecursion 0)

Great work, but how would you do that for [NuberOfaDaysInOctoberNovember] and [NuberOfaDaysInDecember] instead of NuberOfaDaysInQuarter4 ? — Michal, Dec 19 '18 at 07:32

xQbert · Answer 3 · 2018-12-19T14:08:57.160

DEMO

Use a recursive CTE (Be sure to set max recursion option to the # of days you need the loop to process or 0 once you've confirmed no infinite loops exist. ) to generate all your days between max and min of your dates in datetable and then join this result to your datetable on date between range. using datepart on year and quarter of a cycledate we can get the appropriate quarter/year and then group.

I didn't pivot the results. Pivoting data is usually a display function and best left to those display tools crystal, BI, etc. It can be done in SQL and in in this case Dynamic SQL would be needed as the # of years is dynamic in nature. I choose not to to down that path as the Reporting tools can handle the dynamic nature of the data.

WITH 
DateTable as (SELECT cast('2015-11-01 00:00:00.000'as  date) startdate,  cast('2018-06-01 00:00:00.000'as  date) endDate union all
              SELECT '2017-09-02 00:00:00.000',  '2021-12-02 00:00:00.000' union all
              SELECT '2016-01-02 00:00:00.000',  '2019-01-02 00:00:00.000'),

CTE  AS (SELECT Min(StartDate) StartDate, max(EndDate) EndDate FROM DATETABLE),

CTE2 AS (SELECT C.StartDate as RangeStartDate
              , C.EndDate as RangeEndDate
              , 1 CycleCount
              , datepart(Q,dateadd(d,0,C.StartDate)) as Quarter
              , datepart(m,dateadd(d,0,C.StartDate)) as Month
              , datepart(YYYY, dateadd(d,0,C.StartDate)) as Yr
              , C.StartDate as CycleDate
         FROM CTE C
         UNION ALL
         SELECT RangeStartDate, RangeEndDate, CycleCount+1
              , datepart(Q,dateadd(d,1,CycleDate)) as Quarter
              , datepart(m,dateadd(d,1,CycleDate)) as Month     
              , datepart(YYYY, dateadd(d,1,CycleDate)) as Yr
              , dateadd(d,1,cycleDate) as CycleDate
         FROM  cte2
         WHERE datediff(d,RangeStartDate, RangeEndDate) >= CycleCount 

    )
    SELECT DT.StartDate
         , DT.ENDDate
         , concat(yr,'-',Quarter) [YYYY-Q]
         , count(*)  as DaysInQuarter
         , sum(case when Month in (10,11) then 1 else 0 end)as OctNovDays
         , sum(case when Month in (12) then 1 else 0 end)as DecDays
         , datediff(d,StartDate, EndDate) as DaysTotal
    FROM CTE2
    INNER JOIN DateTable DT
      on CTE2.CycleDate between DT.StartDate and DT.EndDate
    GROUP BY Quarter,DT.StartDate, DT.EndDate, YR
    ORDER BY  DT.StartDate, DT.EndDate, YR, Quarter
    OPTION (MAXRECURSION 10000)

Giving us:

+----+---------------------+---------------------+--------+---------------+------------+---------+-----------+
|    |      StartDate      |       ENDDate       | YYYY-Q | DaysInQuarter | OctNovDays | DecDays | DaysTotal |
+----+---------------------+---------------------+--------+---------------+------------+---------+-----------+
|  1 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2015-4 |            61 |         30 |      31 |       943 |
|  2 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2016-1 |            91 |          0 |       0 |       943 |
|  3 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2016-2 |            91 |          0 |       0 |       943 |
|  4 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2016-3 |            92 |          0 |       0 |       943 |
|  5 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2016-4 |            92 |         61 |      31 |       943 |
|  6 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2017-1 |            90 |          0 |       0 |       943 |
|  7 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2017-2 |            91 |          0 |       0 |       943 |
|  8 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2017-3 |            92 |          0 |       0 |       943 |
|  9 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2017-4 |            92 |         61 |      31 |       943 |
| 10 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2018-1 |            90 |          0 |       0 |       943 |
| 11 | 01.11.2015 00:00:00 | 01.06.2018 00:00:00 | 2018-2 |            62 |          0 |       0 |       943 |
| 12 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2016-1 |            90 |          0 |       0 |      1096 |
| 13 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2016-2 |            91 |          0 |       0 |      1096 |
| 14 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2016-3 |            92 |          0 |       0 |      1096 |
| 15 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2016-4 |            92 |         61 |      31 |      1096 |
| 16 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2017-1 |            90 |          0 |       0 |      1096 |
| 17 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2017-2 |            91 |          0 |       0 |      1096 |
| 18 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2017-3 |            92 |          0 |       0 |      1096 |
| 19 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2017-4 |            92 |         61 |      31 |      1096 |
| 20 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2018-1 |            90 |          0 |       0 |      1096 |
| 21 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2018-2 |            91 |          0 |       0 |      1096 |
| 22 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2018-3 |            92 |          0 |       0 |      1096 |
| 23 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2018-4 |            92 |         61 |      31 |      1096 |
| 24 | 02.01.2016 00:00:00 | 02.01.2019 00:00:00 | 2019-1 |             2 |          0 |       0 |      1096 |
| 25 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2017-3 |            29 |          0 |       0 |      1552 |
| 26 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2017-4 |            92 |         61 |      31 |      1552 |
| 27 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2018-1 |            90 |          0 |       0 |      1552 |
| 28 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2018-2 |            91 |          0 |       0 |      1552 |
| 29 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2018-3 |            92 |          0 |       0 |      1552 |
| 30 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2018-4 |            92 |         61 |      31 |      1552 |
| 31 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2019-1 |            90 |          0 |       0 |      1552 |
| 32 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2019-2 |            91 |          0 |       0 |      1552 |
| 33 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2019-3 |            92 |          0 |       0 |      1552 |
| 34 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2019-4 |            92 |         61 |      31 |      1552 |
| 35 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2020-1 |            91 |          0 |       0 |      1552 |
| 36 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2020-2 |            91 |          0 |       0 |      1552 |
| 37 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2020-3 |            92 |          0 |       0 |      1552 |
| 38 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2020-4 |            92 |         61 |      31 |      1552 |
| 39 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2021-1 |            90 |          0 |       0 |      1552 |
| 40 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2021-2 |            91 |          0 |       0 |      1552 |
| 41 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2021-3 |            92 |          0 |       0 |      1552 |
| 42 | 02.09.2017 00:00:00 | 02.12.2021 00:00:00 | 2021-4 |            63 |         61 |       2 |      1552 |
+----+---------------------+---------------------+--------+---------------+------------+---------+-----------+

Updated to handle Oct/Nov and Dec columns and total; whcih I failed to take into account on the original post. — xQbert, Dec 19 '18 at 14:11

Number of days by quarter between StartDate and EndDate - SQL Server

3 Answers3