CODEWITHSUNDEEP
sql-serverdatetimesum
user2270911
user2270911

Reputation: 305

Sum the Attendance Hours by Category and then Group by 'Week of'

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 (
    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

Upvotes: 1

Views: 962

Answers (1)

Andomar
Andomar

Reputation: 238058

To sum by week:

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

To find the first day a student reaches 120 hours:

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

Live example at SQL Fiddle (with the sum at 60 instead of 120, so one student reaches it.)

If you're using SQL Server 2005 or older, you can't use sum() over (). Try a join instead:

select  ID
,       min(Date)
from    (
        select  t1.ID
        ,       t1.Date
        ,       sum(t2.Hours) as RunningSum
        from    Attendance t1
        join    Attendance t2
        on      t1.ID = t2.ID
                and t2.Date <= t1.Date
        group by
                t1.ID
        ,       t1.Date
        ) as SubQueryAlias
where   RunningSum >= 60
group by
        ID;

SQL Server 2005 example at SQL Fiddle.

Upvotes: 1

Related Questions