Calculating employees weekly hours

2 Jun 2005 8:59 PM

ninel gorbunov via SQLMonster.com

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

--
Message posted via http://www.sqlmonster.com

2 Jun 2005 9:14 PM

Jens Süßmeyer

Best way ould be to use a calendar table to join to and evaluate the
results:

Then you could be with:

Select ct.weekno,sum(Hours)
From TimeTable tt
inner join Calendartable ct
on ct.date = tt.date

The first entry of the week 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
---

Show quoteHide quote

"ninel gorbunov via SQLMonster.com" <forum@nospam.SQLMonster.com> schrieb im
Newsbeitrag news:6d37fd5c688b46efbabd32da05b5f85f@SQLMonster.com...
> 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
>
> --
> Message posted via http://www.sqlmonster.com

2 Jun 2005 9:18 PM

Anith Sen

First, check your server to see if the first day of the week 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

3 Jun 2005 2:30 AM

Lionel Chacon

Assuming Sunday is set as your first day of the week, you can try this:
SELECT
    DATEADD(d, -DATEPART(dw, MIN(CAST(WorkDay AS DATETIME))) + 1,
MIN(CAST(WorkDay AS DATETIME))) PayPeriodWeek,
    EmpNumber,
    SUM(Hours)
FROM Test1
GROUP BY
    DATEPART(ww, CAST(WorkDay AS DATETIME)),
    EmpNumber

--
Lionel Chacon

Show quoteHide quote

"ninel gorbunov via SQLMonster.com" wrote:

> 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
>
> --
> Message posted via http://www.sqlmonster.com
>

3 Jun 2005 6:46 AM

Itzik Ben-Gan

Here's a query that will aggregate by the week, with Sunday being considered
thee first day of the week, independent of the DATEFIRST setting:

select empnumber, datecol - datepart(dw, datecol - @@datefirst - 7) + 1 as
weekstart, 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 week day. Since
weekdays 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 weekday, simply subtract 1 instead of 7. You can find
in BOL under DATEFIRST which number represents which first weekday.

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Show quoteHide quote

"ninel gorbunov via SQLMonster.com" <forum@nospam.SQLMonster.com> wrote in
message news:6d37fd5c688b46efbabd32da05b5f85f@SQLMonster.com...
> 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
>
> --
> Message posted via http://www.sqlmonster.com