SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum the Hours by Category and then Group Week of
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mgarret
Starting Member

14 Posts

Posted - 07/25/2013 :  18:23:55  Show Profile  Reply with Quote
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 30

ID Day_120_Hours_Reached
356485 6/30/2012
356485 11/15/2012
555666 10/12/2012
555666 2/25/2013

I 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

547 Posts

Posted - 07/25/2013 :  19:14:08  Show Profile  Reply with Quote
Use DATEPART(Week,[Date]) to get the week of.


SELECT DOP_ID, Category, MIN([Date]) as [Week of], SUM([Hours]) as [Total Hours]
	FROM Attendance GROUP BY DOP_ID, Category, DATEPART(Week,[Date]);


Edited by - MuMu88 on 07/25/2013 19:16:02
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/26/2013 :  00:15:14  Show Profile  Reply with Quote
should be this as per OPs requirement


SELECT 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/26/2013 :  03:28:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Query 1
WITH 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		cteSource
GROUP BY	DOP_ID,
		Category,
		WeekOf
ORDER BY	DOP_ID,
		Category,
		WeekOf;

-- Query 2
WITH 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		cteSource
GROUP BY	DOP_ID,
		WeekOf
HAVING		SUM([Hours]) >= 120
ORDER BY	DOP_ID,
		WeekOf;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/26/2013 :  03:48:54  Show Profile  Reply with Quote
if you just need to flag it, you could do it within same query itself


SELECT 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/26/2013 :  05:23:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/26/2013 :  05:39:35  Show Profile  Reply with Quote
Reading again i think this is what OP's after for second part

SELECT DOP_ID,m.[Date] AS Day_120_Hours_Reached 
FROM  Attendance m
CROSS APPLY (SELECT SUM([Hours]) AS TotalHrs
             FROM Attendance
             WHERE DOP_ID = m.DOP_ID
             AND [Date] < m.[Date]
            )m1
WHERE (m1.TotalHrs + m.Hours  = 120
OR (120 BETWEEN m1.TotalHrs AND m1.TotalHrs + m.Hours ))






------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000