Query optimization for weekly data for the month

Question

Need help in SQL server query optimization which is as follow:

    enter declare @sDate datetime  
declare @eDate datetime 

SET @sDate = '2017-01-01'  
SET @eDate = '2017-01-31'

SELECT    
    @sDate AS [StartDate], 
    DATEADD(day,6, @sDate) [ENDDATE],                       
    SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen],  
    SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
    FROM GS
    WHERE     
    (GS.[ModifiedDate] > @sDate)
    AND 
    (GS.[ModifiedDate] <= DATEADD(day,6, @sDate))
UNION
SELECT    
    DATEADD(day,7, @sDate) AS [StartDate], 
    DATEADD(day,13, @sDate) [ENDDATE],                      
    SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen],  
    SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
    FROM GS
    WHERE     
    (GS.[ModifiedDate] > DATEADD(day,7, @sDate))
    AND 
    (GS.[ModifiedDate] <= DATEADD(day,13, @sDate))
UNION
SELECT    
    DATEADD(day,14, @sDate) AS [StartDate], 
    DATEADD(day,20, @sDate) [ENDDATE],                      
    SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen],  
    SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
    FROM GS
    WHERE     
    (GS.[ModifiedDate] > DATEADD(day,7, @sDate))
    AND 
    (GS.[ModifiedDate] <= DATEADD(day,20, @sDate))
UNION
SELECT    
    DATEADD(day,21, @sDate) AS [StartDate], 
    DATEADD(day,27, @sDate) [ENDDATE],                      
    SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen],  
    SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
    FROM GS
    WHERE     
    (GS.[ModifiedDate] > DATEADD(day,21, @sDate))
    AND 
    (GS.[ModifiedDate] <= DATEADD(day,27, @sDate))
UNION

SELECT    
    DATEADD(day,27, @sDate) AS [StartDate], 
    @eDate [ENDDATE],                       
    SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen],  
    SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
    FROM GS
    WHERE     
    (GS.[ModifiedDate] > DATEADD(day,27, @sDate))
    AND 
    (GS.[ModifiedDate] <= @eDate)

and result should be as follow:

StartDate               ENDDATE                 rcOpen  rcClosed
2017-01-01 00:00:00.000 2017-01-07 00:00:00.000 NULL    NULL
2017-01-08 00:00:00.000 2017-01-14 00:00:00.000 NULL    NULL
2017-01-15 00:00:00.000 2017-01-21 00:00:00.000 12      5
2017-01-22 00:00:00.000 2017-01-28 00:00:00.000 NULL    NULL
2017-01-28 00:00:00.000 2017-01-31 00:00:00.000 NULL    NULL

May be I need to use CTE (common table expressions)for weekly data as mentioned here

how to get the start and end dates of all weeks between two dates in SQL server?

    declare @sDate datetime,
        @eDate datetime;

select  @sDate = '2013-02-25',
        @eDate = '2013-03-25';


;with cte as
(
  select @sDate StartDate, 
    DATEADD(wk, DATEDIFF(wk, 0, @sDate), 6) EndDate
  union all
  select dateadd(ww, 1, StartDate),
    dateadd(ww, 1, EndDate)
  from cte
  where dateadd(ww, 1, StartDate)<=  @eDate
)
select *
from cte

iamdave · Accepted Answer · 2017-01-26T11:58:57.450

0

If you can't add a Dates or Numbers table to your database, using a derived table generated by the CTE you mention is probably the best way forward:

declare @sDate datetime,
        @eDate datetime;

select  @sDate = '2013-02-25',
        @eDate = '2013-03-25';


;with cte as
(
  select @sDate StartDate, 
    DATEADD(wk, DATEDIFF(wk, 0, @sDate), 6) EndDate
  union all
  select dateadd(ww, 1, StartDate),
    dateadd(ww, 1, EndDate)
  from cte
  where dateadd(ww, 1, StartDate)<=  @eDate
)
SELECT
    CTE.StartDate
    ,CTE.EndDate
    ,SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen]
    ,SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
FROM CTE
    LEFT JOIN GS
        ON CTE.StartDate < GS.[ModifiedDate]
            AND CTE.EndDate >= GS.[ModifiedDate]
GROUP BY CTE.StartDate
        ,CTE.EndDate
ORDER BY CTE.StartDate

edited Jan 26 '17 at 11:58

answered Jan 25 '17 at 12:22

iamdave

12,023
3
24
53

Hi iamdave, I tried this way but got following error GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'cte' – Dev Developer Jan 25 '17 at 12:34
@DevDeveloper Did you copy my code exactly? There *isn't* a `group by`, `having` or aggregate function in the recursive part of my `cte`... – iamdave Jan 25 '17 at 12:44
sorry my mistake, result is as follow ( which is not showing null value weeks) 'StartDate EndDate rcOpen rcClosed 2017-01-15 00:00:00.000 2017-01-22 00:00:00.000 12 5' – Dev Developer Jan 25 '17 at 13:06
@DevDeveloper Change the `JOIN` to a `LEFT JOIN`. – iamdave Jan 25 '17 at 13:11
@iamdeve thanks alot, instead of Left Join, i have to use Right Join then I got required result. – Dev Developer Jan 26 '17 at 11:49
@DevDeveloper As my mistake, the tables were the wrong way around. If you swap the `CTE` and `GS` name you will be able to use `left join`. Ideally you avoid `right join` as much as possible. – iamdave Jan 26 '17 at 12:00

M.Ali · Answer 2 · 2017-01-25T13:12:15.410

This query will perform better than a recursive CTE.

declare @sDate datetime = '2017-01-01'; 
declare @eDate datetime = '2017-01-31';

WITH X AS (
SELECT DISTINCT 
      DATEADD(DAY, -  (DATEPART(WEEKDAY, [Dates])-1), [Dates]) [WeekStart]
    , DATEADD(DAY, 7- (DATEPART(WEEKDAY, [Dates])), [Dates])   [WeekEnd]
FROM (
        SELECT DISTINCT DATEADD(DAY , rn -1 , @sDate) [Dates]
        FROM (
                Select TOP (DATEDIFF(DAY, @sDate, @eDate)) 
                      ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
                FROM master..spt_values a 
                      CROSS JOIN master..spt_values b
             )a
     ) b
)
SELECT  [WeekStart]
      , [WeekEnd]
      , SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen],  
      , SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
FROM X 
LEFT JOIN GS  ON GS.[ModifiedDate] <= CTE.[WeekEnd] 
                AND GS.[ModifiedDate] >= CTE.[WeekStart]

score 0 · Answer 3 · answered Jan 25 '17 at 12:34

Another way of doing it

declare @sDate datetime  
declare @eDate datetime 

SET @sDate = '2017-01-01'  
SET @eDate = '2017-01-31'

--A recursive CTE for fetching the weeks range
;WITH CTE AS
(
    SELECT @sDate SDATE
    , DATEADD(DD,6,@sDate) AS TO_DTE

    UNION ALL 

    SELECT DATEADD(DD,1,TO_DTE)
    , CASE 
        WHEN DATEADD(DD, 7, TO_DTE) > @eDate
            THEN @eDate
        ELSE DATEADD(DD, 7, TO_DTE)
        END
    FROM CTE
    WHERE DATEADD(DD, 1, TO_DTE) <= @eDate


)
/* An Intermediate result of CTE to better understand
+-------------------------+-------------------------+
|          SDATE          |         TO_DTE          |
+-------------------------+-------------------------+
| 2017-01-01 00:00:00.000 | 2017-01-07 00:00:00.000 |
| 2017-01-08 00:00:00.000 | 2017-01-14 00:00:00.000 |
| 2017-01-15 00:00:00.000 | 2017-01-21 00:00:00.000 |
| 2017-01-22 00:00:00.000 | 2017-01-28 00:00:00.000 |
| 2017-01-29 00:00:00.000 | 2017-01-31 00:00:00.000 |
+-------------------------+-------------------------+
*/

SELECT CTE.SDATE
    ,CTE.TO_DTE
    ,SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen]
    ,SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
FROM GS
    JOIN CTE
        ON GS.[ModifiedDate] > CTE.SDATE
            AND GS.[ModifiedDate] <= CTE.TO_DTE
GROUP BY CTE.SDATE
        ,CTE.TO_DTE
ORDER BY CTE.SDATE

Shakeer above CTE query is also working fine, but I need to use CTE to fetch data from table. — Dev Developer, Jan 25 '17 at 12:48

score 0 · Answer 4 · answered Feb 13 '17 at 12:54

declare @sDate datetime,
        @eDate datetime;

select  @sDate = '2013-02-25',
        @eDate = '2013-03-25';


;with cte as
(
  select @sDate StartDate, 
    DATEADD(dd,(7 - (DATEPART(dw,DATEADD(month,DATEDIFF(mm,0,@SelectedDate),0)) + @@DATEFIRST) % 7) % 7,DATEADD(month,DATEDIFF(mm,0,@sDate),0)) EndDate
  union all
  select dateadd(dd, 1, EndDate),
    dateadd(ww, 1, EndDate)
  from cte
  where dateadd(ww, 1, StartDate)<=  @eDate
)
SELECT
    CTE.StartDate
    ,CTE.EndDate
    ,SUM(CASE WHEN GS.[Status] = 'Open' THEN 1 ELSE 0 END) [rcOpen]
    ,SUM(CASE WHEN GS.[Status] = 'Closed'  THEN 1 ELSE 0 END) [rcClosed]
FROM CTE
    Right JOIN CTE
        ON CTE.StartDate < GS.[ModifiedDate]
            AND CTE.EndDate >= GS.[ModifiedDate]
GROUP BY CTE.StartDate
        ,CTE.EndDate
ORDER BY CTE.StartDate

After making few changes in @iamdave answer above will be the correct answer

score -1 · Answer 5 · edited Jan 25 '17 at 13:21

-1

Maybe you can use UNION ALL instead of just UNION.

edited Jan 25 '17 at 13:21

ase

13,231
4
34
46

answered Jan 25 '17 at 12:14

Rogier Wijsman

133
7

Union is also working fine but i need to optimize above query. – Dev Developer Jan 25 '17 at 12:46
@DevDeveloper `union all` is faster than `union` as it doesn't check for and then remove duplicate rows. – iamdave Jan 25 '17 at 12:49
Thank you, really appreciated – Dev Developer Jan 26 '17 at 11:47

Query optimization for weekly data for the month

5 Answers5