Author |
Topic |
mgarret
Starting Member
14 Posts |
Posted - 2013-07-25 : 18:23:55
|
Hi all. I need to sum the attendance hours by category and then group by 'Week of'. The 'Week of' start date is defined by the Monday in that week but Sunday is works too. If the Category Values are in ’Art’ or ‘PE’, they need to be combined into Non Educational. I also need to be able to flag the day(s) a student reaches 120 hours.My table which is structured like this:CREATE TABLE Attendance ( DOP_ID int, Category varchar(20), Title varchar(20), Date datetime, Hours int, ) INSERT INTO Attendance VALUES (4504498, 'GED Program', '7/1/2012', 7),(4504498, 'GED Program', '7/2/2012', 3),(4504498, 'GED Program', '7/3/2012', 3),(4504498, 'GED Program', '7/4/2012', 7),(4504498, 'GED Program', '7/5/2012', 3),(4504498, 'GED Program', '7/8/2012', 3),(4504498, 'GED Program', '7/9/2012', 7),(4504498, 'GED Program', '7/10/2012',7),(4504498, 'GED Program', '7/11/2012',3),(4504498, 'GED Program', '7/12/2012',3),(4504498, 'High School', '7/1/2012', 7),(4504498, 'High School', '7/2/2012', 3),(4504498, 'High School', '7/3/2012', 3),(4504498, 'High School', '7/4/2012', 3),(4504498, 'High School', '7/5/2012', 3),(4504498, 'High School', '7/8/2012', 7),(4504498, 'High School', '7/9/2012', 3),(4504498, 'High School', '7/10/2012',8),(4504498, 'High School', '7/11/2012',3),(4504498, 'High School', '7/12/2012',7),(9201052, 'Art', '7/15/2012', 6),(9201052, 'Art', '7/16/2012', 3),(9201052, 'Art', '7/17/2012', 7),(9201052, 'PE', '7/17/2012', 7),(9201052, 'PE', '7/18/2012', 7)I need an end result which looks like this:ID Category Week of Total Hours 4504498 GED Program 7/1/2012 26 4504498 GED Program 7/8/2012 23 4504498 High School 7/1/2012 19 4504498 High School 7/8/2012 28 9201052 Non Educational 7/15/2012 30ID Day_120_Hours_Reached 356485 6/30/2012 356485 11/15/2012 555666 10/12/2012 555666 2/25/2013I have been looking for examples of a Week function that will pull out the 'week of' from a date using MS Sql Server and I can't find much info. Any feedback is appreciated |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-25 : 19:14:08
|
Use DATEPART(Week,[Date]) to get the week of.[CODE]SELECT DOP_ID, Category, MIN([Date]) as [Week of], SUM([Hours]) as [Total Hours] FROM Attendance GROUP BY DOP_ID, Category, DATEPART(Week,[Date]);[/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-26 : 00:15:14
|
should be this as per OPs requirementSELECT DOP_ID, CASE WHEN Category IN ('Art','PE') THEN 'Non Educational' ELSE Category END, MIN([Date]) as [Week of], SUM([Hours]) as [Total Hours] FROM Attendance GROUP BY DOP_ID, CASE WHEN Category IN ('Art','PE') THEN 'Non Educational' ELSE Category END, DATEPART(Week,[Date]); ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-26 : 03:28:09
|
[code]-- Query 1WITH cteSource(DOP_ID, Category, WeekOf, [Hours])AS ( SELECT DOP_ID, CASE WHEN Category IN ('Art', 'PE') THEN 'Non educational' ELSE Category END AS Category, DATEADD(DAY, DATEDIFF(DAY, '19000101', [Date]) / 7 * 7 , '19000101') AS WeekOf, -- Monday [Hours] FROM dbo.Attendance)SELECT DOP_ID AS ID, Category, WeekOf AS [Week of], SUM([Hours]) AS [Total Hours]FROM cteSourceGROUP BY DOP_ID, Category, WeekOfORDER BY DOP_ID, Category, WeekOf;-- Query 2WITH cteSource(DOP_ID, WeekOf, [Hours])AS ( SELECT DOP_ID, DATEADD(DAY, DATEDIFF(DAY, '19000101', [Date]) / 7 * 7 , '19000101') AS WeekOf, -- Monday [Hours] FROM dbo.Attendance)SELECT DOP_ID AS ID, WeekOf AS [Day_120_Hours_Reached]FROM cteSourceGROUP BY DOP_ID, WeekOfHAVING SUM([Hours]) >= 120ORDER BY DOP_ID, WeekOf;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-26 : 03:48:54
|
if you just need to flag it, you could do it within same query itselfSELECT DOP_ID, CASE WHEN Category IN ('Art','PE') THEN 'Non Educational' ELSE Category END, MIN([Date]) as [Week of], SUM([Hours]) as [Total Hours],CASE WHEN SUM([Hours])>=120 THEN '120 Hours Reached' ELSE '' END FROM Attendance GROUP BY DOP_ID, CASE WHEN Category IN ('Art','PE') THEN 'Non Educational' ELSE Category END, DATEPART(Week,[Date]); ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-26 : 05:23:29
|
I think not, Visakh. You have Category included in the grouping. There is no sign of that in OP second result. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-26 : 05:39:35
|
Reading again i think this is what OP's after for second partSELECT DOP_ID,m.[Date] AS Day_120_Hours_Reached FROM Attendance mCROSS APPLY (SELECT SUM([Hours]) AS TotalHrs FROM Attendance WHERE DOP_ID = m.DOP_ID AND [Date] < m.[Date] )m1WHERE (m1.TotalHrs + m.Hours = 120OR (120 BETWEEN m1.TotalHrs AND m1.TotalHrs + m.Hours )) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|