Calculating employees weekly hours

The pay period begins on a sunday and ends on a saturday. Some
employees work on the wends, some don't.
How can I calculate by w how many hours an employee has worked?
Date EmpNumber Hours
20050522(sun) 123 7.5
20050523(mon) 123 8
20050524(tues) 123 8.5
20050523(mon) 246 9
20050524(tues) 246 6.5
20050526(thur) 246 7.5
20050529(sun) 345 8
20050530(mon) 345 7
20050531(tues) 345 6.75
I need the outcome to look like this:
PayPeriodW EmpNumber Hours
20050522 123 24
20050522 246 23
20050529 345 21.75Hi ninel,
try this:
****************************************
**********
create table test_data(
MyDate datetime,
EmpNumber int,
Hours decimal(9,2)
)
go
insert into test_data (MyDate, EmpNumber, Hours)
values ('22 May 2005', 123, 7.5)
insert into test_data (MyDate, EmpNumber, Hours)
values ('23 May 2005', 123, 8)
insert into test_data (MyDate, EmpNumber, Hours)
values ('24 May 2005', 123, 8.5)
insert into test_data (MyDate, EmpNumber, Hours)
values ('23 May 2005', 246, 9)
insert into test_data (MyDate, EmpNumber, Hours)
values ('24 May 2005', 246, 6.5)
insert into test_data (MyDate, EmpNumber, Hours)
values ('26 May 2005', 246, 7.5)
insert into test_data (MyDate, EmpNumber, Hours)
values ('29 May 2005', 345, 8)
insert into test_data (MyDate, EmpNumber, Hours)
values ('30 May 2005', 345, 7)
insert into test_data (MyDate, EmpNumber, Hours)
values ('31 May 2005', 345, 6.75)
go
select * from test_data
go
select min(MyDate) as min_MyDate, EmpNumber, datepart(ww, MyDate), sum(Hours
)
from test_data
group by EmpNumber, datepart(ww, MyDate)
****************************************
**********
SQL Server has Date Function to get work w of a year.
please refer BOL to check out DatePart function.
However, if your first day of w is different, you may need to use other
way to calculate.
Leo Leong
"ninel" wrote:

> The pay period begins on a sunday and ends on a saturday. Some
> employees work on the wends, some don't.
> How can I calculate by w how many hours an employee has worked?
> Date EmpNumber Hours
> 20050522(sun) 123 7.5
> 20050523(mon) 123 8
> 20050524(tues) 123 8.5
> 20050523(mon) 246 9
> 20050524(tues) 246 6.5
> 20050526(thur) 246 7.5
> 20050529(sun) 345 8
> 20050530(mon) 345 7
> 20050531(tues) 345 6.75
> I need the outcome to look like this:
> PayPeriodW EmpNumber Hours
> 20050522 123 24
> 20050522 246 23
> 20050529 345 21.75
>