CODEWITHSUNDEEP
sqldatabaset-sql
Andreea Csiszor
Andreea Csiszor

Reputation: 1

How to determine weekly sum of worked hours in T-SQL?

I have a table with the following data: Date, Hours Worked. How do I get the sum of hours worked for the entire week as shown in the example? I have data for years going back.

Date    Hours worked    Weekly hours
1/9/2020    8              32
2/9/2020    8              32             
3/9/2020    8              32
4/9/2020    8              32
5/9/2020    0              0
6/9/2020    0              0
7/9/2020    8              40
8/9/2020    8              40
9/9/2020    8              40
10/9/2020   8              40
11/9/2020   8              40
12/9/2020   0              0
1/9/2021    0              0
2/9/2021    8              32
3/9/2021    8              32
4/9/2021    8              32
5/9/2021    8              32

Upvotes: 0

Views: 130

Answers (2)

Nuray Biliz
Nuray Biliz

Reputation: 166

It seems your output weeknumbers are ISO formatted. Therefore DATEPART function can be used with 'isowk' parameter as the query below. If SUM the hours_worked by DATEPART(isowk, "Date") and the "Year" the output will be as you pictured.

select 
    date,
    hours_worked,
    -----------
    Year(date) as "Year",
    Datepart(isowk,date) as weekOfYear,
    -----------
    /*if you don't want to see the SUM for hours_worked=0 then you can use CASE WHEN ...*/
    case 
        when hours_worked = 0 then 0 
        else 
        sum(hours_worked) over (partition by datepart(isowk,date),year(date)) 
    end as weekly_hours,
    ----------
    /*if you want to see for all days even if hours_worked=0 then this part will be useful */
    sum(hours_worked) over (partition by datepart(isowk,date),year(date)) as weekly_hours_total
    ----------
from test1 t
order by 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use window functions. But this is tricky because weeks can span years. One method is to subtract the weekday number from the current date:

select t.*,
       sum(hours_worked) over (partition by dateadd(day, - datepart(weekday, date), date)) as week_hours
from t;

Note: This uses the system configured first day of the week to define weeks. That seems like a reasonable approach.

Upvotes: 1

Related Questions