Advertisement
Guest User

Untitled

a guest
May 27th, 2015
250
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.92 KB | None | 0 0
  1. use Reinstatement;
  2.  
  3. declare @StartDate as datetime
  4. set @StartDate = '05/24/2015'
  5. declare @EndDate as datetime
  6. set @EndDate = '05/30/2015';
  7.  
  8.  
  9. with Date_Range AS
  10. (
  11.     SELECT @StartDate AS Start_Dat
  12.     UNION ALL
  13.     SELECT Start_Dat+1
  14.     FROM Date_Range
  15.     WHERE
  16.         Start_Dat+1 <= @EndDate
  17. )
  18. --SELECT
  19. --  *
  20. --FROM 
  21. --  Date_Range
  22.  
  23.  
  24. select  (Start_Dat),
  25.         nullif(COUNT(T.DueDate), 0) AS Planned,
  26.         SUM(CASE WHEN T.DueDate >= Start_Dat AND T.DueDate < DATEADD(DAY, 1, Start_Dat) and t.TaskType = 1 AND T.SiteClearDate IS NOT NULL THEN 1 ELSE 0 END) AS Actual,
  27.         ((SUM(CASE WHEN T.DueDate >= Start_Dat AND T.DueDate < DATEADD(DAY, 1, Start_Dat) and t.TaskType = 1 AND T.SiteClearDate IS NOT NULL THEN 1 ELSE 0 END)) * 100)
  28.          / (nullif(COUNT(T.DueDate), 0))  AS '%'
  29.        
  30.         from Tasks AS T
  31.         outer apply Date_Range
  32.  
  33.         Where (T.DueDate >= Start_Dat) AND (T.DueDate < dateadd(day,1,Start_Dat)) AND T.TaskType = 1
  34.         GROUP BY Start_Dat
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement