Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- use Reinstatement;
- declare @StartDate as datetime
- set @StartDate = '05/24/2015'
- declare @EndDate as datetime
- set @EndDate = '05/30/2015';
- with Date_Range AS
- (
- SELECT @StartDate AS Start_Dat
- UNION ALL
- SELECT Start_Dat+1
- FROM Date_Range
- WHERE
- Start_Dat+1 <= @EndDate
- )
- --SELECT
- -- *
- --FROM
- -- Date_Range
- select (Start_Dat),
- nullif(COUNT(T.DueDate), 0) AS Planned,
- 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,
- ((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)
- / (nullif(COUNT(T.DueDate), 0)) AS '%'
- from Tasks AS T
- outer apply Date_Range
- Where (T.DueDate >= Start_Dat) AND (T.DueDate < dateadd(day,1,Start_Dat)) AND T.TaskType = 1
- GROUP BY Start_Dat
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement