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

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

Thanks for your help.
0
edc
Asked:
edc
1 Solution
 
sshah254Commented:
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
 
edcAuthor 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
 
HainKurtSr. 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rajkumar GsSoftware 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

Open in new window


Raj
0
 
edcAuthor 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
 
HainKurtSr. 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

Open in new window

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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