I have a data set that looks like the following below in sample data. I need to break out the data as a summary of the data on a per week basis with the weekend ending on Sunday. I have tried various attempts at a dynamic pivot table but have had no success. Any thoughts or suggestions.
Sample Data
EmpID ClientID Contact_Date Hrs
12000 1234 1/5/2015 1.67
12001 1235 1/6/2015 2.00
12002 1236 1/7/2015 4.00
.......
etc
Desired Result
EmpID ClientID Wk 2/8/2015 Wk 2/1/2015 Wk 1/25/2015 Wk 1/18/2015 ....etc
12000 1234 4 3 2 0
I have this query to GET Every Sunday within the date range of dates from all entries in the table with a certain status code.**
DECLARE @StartDate DATETIME
,@EndDate DATETIME
SET @STARTDATE = (SELECT MIN(contact_date) FROM dbo.vw_hrs_entered)
SET @ENDDATE = (SELECT MAX(contact_date) FROM dbo.vw_hrs_entered)
SELECT *
FROM (SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
[Date] = dateadd(DAY,ROW_NUMBER()
OVER(ORDER BY c1.name),
DATEADD(DD,-1,@StartDate))
FROM [master].[dbo].[spt_values] c1 ) x
WHERE datepart(dw,[Date]) = 1
RETURNS
1/11/2015 12:00:00 AM
1/18/2015 12:00:00 AM
1/25/2015 12:00:00 AM
2/1/2015 12:00:00 AM
2/8/2015 12:00:00 AM
Aucun commentaire:
Enregistrer un commentaire