Creating Subtotals - SQL Server 2008

Question

I have the below data set, which is reporting employee hours for each day, and each day has a week number (1 or 2) as running for two weeks.

    SiteId  SiteName  EmployeeId  FirstName   SurName  Date        WorkedHours  WorkedMinutes  WeekNo
    4       Test      7           Steve       Stone    2014-03-01  4.5          270              1
    4       Test      7           Steve       Stone    2014-03-02  9            540              1
    4       Test      7           Steve       Stone    2014-03-03  4.5          270              1
    4       Test      7           Steve       Stone    2014-03-08  4.5          270              2
    4       Test      7           Steve       Stone    2014-03-09  9            540              2
    4       Test      7           Steve       Stone    2014-03-10  4.5          270              2

I would like to add a sub total of workedhours and workedminutes for each employee for each week. So the desired outcome would have a row at the end of each section of weeks for the sub total, which would give a total for week 1 and then again for week 2

Answer 1

If you are using SQL Server 2008 or above you should take a look at GROUPING SETS. They are much faster than Rollup. Its an awesome feature to do such tasks.

I recently implemented similar logic

select EmpId, Year, FaciLeaveType, Month, DateValue , Sum(Leaves)
FROM EmplLeave
GROUP BY 
GROUPING SETS(
                  ( EmpId,Year,FaciLeaveType,Month,DateValue )
                 ,( EmpId,Year,FaciLeaveType,Month )
                 ,( EmpId,Year,FaciLeaveType )
      )

https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

Answer 2

Rollup was the best way forward Here.