Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sum the Attendance Hours by Category and then Group by 'Week of' Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 4:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107

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.



CREATE TABLE Attendance (
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.
Post #1477794
Posted Thursday, July 25, 2013 5:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 3,559, Visits: 7,681
Have you tried to use DATEPART(wk, Date)?
What have you tried?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477802
Posted Thursday, July 25, 2013 5:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107

I have tried datepart(wk, Date). It groups by week (datepart(wk, Date)) and then displays the first entry of that week, not the first day in the week, necessarily


  
select DOP_ID,
(First_Name + ' ' + Last_Name) as Name,
Category,
min(Date) as WeekOf,
sum(Hours) as TotalHours
FROM [GW_PPP].[dbo].[SLAM_Attendence]
where DOP_ID in (select DOP_ID from [GW_PPP].[dbo].[SLAM_Status] where STATUS ='Enrolled')
and Category in ('GED Program', 'Lit/Num Combined (Pre-GED)')
group by DOP_ID,Category, datepart(wk, Date)

Post #1477803
Posted Thursday, July 25, 2013 5:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 3,559, Visits: 7,681
I misread a part of your original post and became more clear now.
You could use DATEADD(wk, DATEDIFF(wk,0,Date), 0) to get the first day of the week and be able to group them.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477806
Posted Thursday, July 25, 2013 6:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 3,559, Visits: 7,681
This works for me.
SELECT ID,
CASE WHEN Category IN( 'Art', 'PE') THEN 'Non Educational' ELSE Category END Category,
DATEADD( dd, -1, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) Week,
SUM(Hours) Total_Hours
FROM Attendance
GROUP BY ID,
CASE WHEN Category IN( 'Art', 'PE') THEN 'Non Educational' ELSE Category END ,
DATEADD( dd, -1, DATEADD(wk, DATEDIFF(wk,0,Date), 0))

However, I'm not sure about your second requirement. It seems that you might need a running total calculation. For information on this article Solving the Running Total and Ordinal Rank Problems



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477811
Posted Thursday, July 25, 2013 8:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107
Hi. The above is great, Thanks! Just want I need. Below was the original example code I was working off of to try and get the 2nd requirement (with the sum at 60 instead of 120, so one student reaches it, using the sample dataset.). I am having trouble modifying it to get it to work.


    select  ID
, Category
, min(Date) as WeekOf
, sum(Hours) as TotalHours
from Attendance
group by
ID
, Category
, datepart(wk, Date);

select ID
, min(Date)
from (
select ID
, Date
, sum(Hours) over (partition by ID order by Date) RunningSum
, Hours
from Attendance
) as SubQueryAlias
where RunningSum >= 60
group by
ID;

Post #1477835
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse