The pay period begins on a sunday and ends on a saturday. Some employees work on the weekends, some don't. How can I calculate by week 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: PayPeriodWeek     EmpNumber     Hours 20050522            123                  24 20050522            246                  23 20050529            345                  21.75

Hi 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 week of a year.
please refer BOL to check out DatePart function.
However, if your first day of week is different, you may need to use other
way to calculate.

Leo Leong


Show quoteHide quote

SET IDENTITY_INSERT
Is this possible?
Are 'inserted' and 'deleted' tables or views?
it cant be that easy
Executing result without copying and pasting
Calculating employees weekly hours
ANSI NULLs set at table level?
are old school joins slower
help with locking
Selecting records from UNION