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.75
Message posted via http://www.webservertalk.comBest way ould be to use a calendar table to join to and evaluate the
results:
Then you could be with:
Select ct.wno,sum(Hours)
From TimeTable tt
inner join Calendartable ct
on ct.date = tt.date
The first entry of the w can be easily extracted from the calendar table,
depending if you want to show the day (sunday) wheter a employee has worked
there ( --> inner join) or even if no employee worked there ((perhpas if
nobody worked on sunday the monday will be displayed -- left join / outer
join)
See the functions for calendar tale here at :
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ninel gorbunov via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:6d37fd5c688b46efbabd32da05b5f85f@.SQ
webservertalk.com...
> 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
> --
> Message posted via http://www.webservertalk.com|||First, check your server to see if the first day of the w is set as
Sunday. You can do this using @.@.DATEFIRST. If you get 1 for SELECT
DATEPART( dw, '20050522' ), you can do:
SELECT dt - DATEPART( dw, dt ) + 1, EmpNumber, SUM( Hours )
FROM tbl
GROUP BY EmpNumber, dt - DATEPART( dw, dt ) + 1 ;
Anith|||Assuming Sunday is set as your first day of the w, you can try this:
SELECT
DATEADD(d, -DATEPART(dw, MIN(CAST(WorkDay AS DATETIME))) + 1,
MIN(CAST(WorkDay AS DATETIME))) PayPeriodW,
EmpNumber,
SUM(Hours)
FROM Test1
GROUP BY
DATEPART(ww, CAST(WorkDay AS DATETIME)),
EmpNumber
Lionel Chacon
"ninel gorbunov via webservertalk.com" 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
> --
> Message posted via http://www.webservertalk.com
>|||Here's a query that will aggregate by the w, with Sunday being considered
thee first day of the w, independent of the DATEFIRST setting:
select empnumber, datecol - datepart(dw, datecol - @.@.datefirst - 7) + 1 as
wstart, sum(hours) as cnt
from yourtable
group by empnumber, datecol - datepart(dw, datecol - @.@.datefirst - 7) + 1
The trick with @.@.datefirst neutralizes the effect of the DATEFIRST setting.
I subtracted the constant 7 to consider Sunday as the first w day. Since
wdays follow a cyclic axis from 1 through 7, I could have just omitted
the - 7 part (equal to -0).
But it's important to figure out that you subtract whichever number stands
for the logical DATEFIRST YOU want to set. If you want Monday to be
considered the first wday, simply subtract 1 instead of 7. You can find
in BOL under DATEFIRST which number represents which first wday.
BG, SQL Server MVP
www.SolidQualityLearning.com
"ninel gorbunov via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:6d37fd5c688b46efbabd32da05b5f85f@.SQ
webservertalk.com...
> 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
> --
> Message posted via http://www.webservertalk.com