• Status: Solved
• Priority: Medium
• Security: Public
• Views: 565

# Get a sum of hours worked in a week

Hi all,
I'm not sure why, but I can't seem to get my head around the SQL for this one.  What I need to do is to get a sum of hours worked in a week where the work week is Saturday to Friday.  There may be no entry for any given day, and the hours are broken down into regular and overtime hours.  So, without the constraint of grouping hours by work week the SQL looks something like

SELECT Name, SUM(RegularHours + OvertimeHours)
FROM Hours
GROUP BY Name

0
edc
1 Solution

Commented:
select name sum(isnull(RegularHours, 0) + isnull(OvertimeHours))
from hours
group by name

You'll need the where clause in here to specify the week.

BTW - this most likely is not the 'legal' way to do things.  Check up with your HR/Payroll departments.

Ss
0

Author Commented:
Hi sshah254.
Thanks for your reply.  I appreciate you taking the time to share your knowledge with me.  The primary hurdle I am facing is actually how to group these results into weeks.  No worries about the legality of this.  It's a small out-of-band app for some folks to manage their own time.

Thanks again.
0

Sr. System AnalystCommented:
i could not get what you are asking for...
please post a sample data and a result that you are looking for...
0

Software EngineerCommented:
Do you mean something like this ?
``````create table #table
(
date	datetime,
mark	int
)

insert into #table
select getdate(), 100 union all
select getdate()-3, 11 union all
select getdate()-8, 22 union all
select getdate() -15, 22

-- Main Query
SELECT sum(mark) as [Total]       ,
Week = DateAdd(day, -1 * datepart(dw, CONVERT(varchar,date,101)),    CONVERT(varchar,date,101))  FROM #table
GROUP BY DateAdd(day, -1 * datepart(dw, CONVERT(varchar,date,101)), CONVERT(varchar,date,101))
ORDER BY [Week] DESC

drop table #table
``````

Raj
0

Author Commented:
Hi RajkumarGS,
Sure.  Let's say John Smith has the following hours worked:

Sat 4/16: 0 hrs / 0 overtime
Sun 4/17: 0 hrs / 0 overtime
Mon 4/18: 8 hrs / 0 overtime
Tues 4/19: 5 hrs / 0 overtime
Wed 4/20: 8 hrs / 3 hrs overtime
Thur 4/21: 8 hrs / 1 hrs overtime
Fri 4/22: 3 hrs / 0 overtime

Sat 4/23: 0 hrs / 0 overtime
Sun 4/24: 5 hrs / 0 overtime
Mon 4/25: 8 hrs / 0 overtime
Tues 4/26: 8 hrs / 2 overtime
Wed 4/27: 8 hrs / 0 hrs overtime
Thur 4/28: 8 hrs / 0 hrs overtime
Fri 4/29: 8 hrs / 0 overtime

I want to group week 1 (4/16 to 4/22) regular and overtime hours into a single number.  In this case 36 hours total, and week 2 (4/23 to 4/29) hours into a second number.  In this case 47 hours.

So when I submit the query the results would be:
4/22/2011 | 36
4/29/2011 | 47
0

Sr. System AnalystCommented:
you mean this
``````create table hours
(
name	varchar(10),
date	date,
hrs	int,
overtime	int
)
--delete from hours;

insert into hours
select 'Hain', getdate()-8, 2,2 union all
select 'Hain', getdate()-7, 3,0 union all
select 'Hain', getdate()-6, 5,0 union all
select 'Hain', getdate()-5, 6,3 union all
select 'Hain', getdate()-4, 7,1 union all
select 'Hain', getdate()-3, 4,0 union all
select 'Hain', getdate()-2, 5,0 union all
select 'Hain', getdate()-1, 4,0 union all
select 'Hain', getdate()-0, 0,0 union all
select 'Kurt', getdate()-8, 4,1 union all
select 'Kurt', getdate()-7, 5,0 union all
select 'Kurt', getdate()-6, 3,0 union all
select 'Kurt', getdate()-5, 6,2 union all
select 'Kurt', getdate()-4, 7,2 union all
select 'Kurt', getdate()-3, 5,0 union all
select 'Kurt', getdate()-2, 6,0 union all
select 'Kurt', getdate()-1, 8,2 union all
select 'Kurt', getdate()-0, 0,0

-- Main Query
select name, DATEPART(week,date) week, SUM(hrs)+sum(overtime) totalHrs from hours
group by name, datepart(week,date)

name	week	totalHrs
Hain	17	12
Hain	18	30
Kurt	17	13
Kurt	18	38
``````
0

Author Commented:
Yes, that will work nicely.  Thank you.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.