SO53833341, Sql Server

SO53833341

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

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(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(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
         , Quarter
         , Yr
         , count(*)  as DaysInQuarter
    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)

View schema
Execution time: 0,12 sec, rows selected: 42, rows affected: 0, absolute service time: 0,31 sec, absolute service time: 0,32 sec

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