Solved

# Calculating Weekly Overtime Biweekly

Posted on 2011-04-21
608 Views
Not sure how best to describe this so bear with me...

I have a table something like this:
Emp_Code    Period_End_Date  Work_Date   Reg_Hrs  Trav_Hrs
120         03/26/11         03/14/11    8        0
120         03/26/11         03/15/11    8        2
120         03/26/11         03/16/11    9        0
120         03/26/11         03/17/11    8.5      0
120         03/26/11         03/18/11    8        0
120         03/26/11         03/21/11    9        1
120         03/26/11         03/22/11    8        0
120         03/26/11         03/23/11    8.5      0
120         03/26/11         03/24/11    8        2
120         03/26/11         03/25/11    8        0

Of course there are a lot more records.  Basically I'm retrieving the sum of Reg_Hrs and Trav_Hrs for a particular Period_End_Date.  My query (so far) looks like this:
SELECT
Emp_Code,
Period_End_Date,
SUM(Reg_Hrs) AS Tot_Reg_Hours,
SUM(Trav_Hrs) AS Tot_Trav_Hrs,
SUM(Reg_Hrs) + SUM(Trav_Hrs) AS Tot_Hrs
FROM
Time_Card_Table
WHERE
Period_End_Date = '03/26/11'
GROUP BY
Emp_Code,
Period_End_Date
The problem is that I also have to return the overtime hours.  You'd think it would be an easy matter of using a case to find out if the total hours is greater than 80 then return the total hours minus 80.

Nope.

I have to figure out the over time per week (based on the Work_Date field.  I tried this:
SUM(
CASE
WHEN
AND
THEN
CASE
WHEN
(RegHrs +
TravHrs) > 80
THEN
(RegHrs +
TravHrs) - 80
ELSE
0
END
END
) AS OT_Week1,
SUM(
CASE
WHEN
AND
Period_End_Date
THEN
CASE
WHEN
(RegHrs +
TravHrs) > 80
THEN
(RegHrs +
TravHrs) - 80
ELSE
0
END
END
) AS OT_Week2,

But that didn't seem to work.

Any help?

TIA
0
Question by:Clif

LVL 11

Author Comment

I suppose I should point out that my query example produces the following result:
Emp_Code    Period_End_Date  Reg_Hrs  Trav_Hrs  Tot_Hrs
120         03/26/11         83.0     5.0       88.0

If I can get the query to work with the overtime calculation, it should look like this:
Emp_Code    Period_End_Date  Reg_Hrs  Trav_Hrs  Tot_Hrs  OT_Week1  OT_Week2
120         03/26/11         83.0     5.0       88.0     3.5       4.5

0

LVL 15

Accepted Solution

Give this a try..
SELECT
Emp_Code
,Period_End_Date
,SUM(Reg_Hrs) AS Tot_Reg_Hours
,SUM(Trav_Hrs) AS Tot_Trav_Hrs
,SUM(Reg_Hrs) + SUM(Trav_Hrs) AS Tot_Hrs
,SUM(overtimehours) AS OT_Hrs
FROM (
SELECT
Emp_Code
,Period_End_Date
, CASE WHEN SUM(Reg_Hrs) > 40 THEN SUM(Reg_Hrs)-40 ELSE 0 END as overtimehours
, SUM(Reg_Hrs) AS Reg_Hrs
, SUM(Trav_Hrs) AS Trav_Hrs
, DATEPART(week,work_date) as weeknumber
FROM
Time_Card_Table
GROUP BY
Emp_Code
,Period_End_Date
,DATEPART(week,work_date)
) w
GROUP BY
Emp_Code
,Period_End_Date

0

LVL 15

Expert Comment

OT broken out by weeks...
WITH ot AS (
SELECT
Emp_Code
,Period_End_Date
, CASE WHEN SUM(Reg_Hrs) > 40 THEN SUM(Reg_Hrs)-40 ELSE 0 END as overtimehours
, SUM(Reg_Hrs) AS Reg_Hrs
, SUM(Trav_Hrs) AS Trav_Hrs
, DATEPART(week,work_date) as weeknumber
FROM
Time_Card_Table
GROUP BY
Emp_Code
,Period_End_Date
,DATEPART(week,work_date)
)
SELECT
Emp_Code
,Period_End_Date
,SUM(Reg_Hrs) AS Tot_Reg_Hours
,SUM(Trav_Hrs) AS Tot_Trav_Hrs
,SUM(Reg_Hrs) + SUM(Trav_Hrs) AS Tot_Hrs
,sum(CASE WHEN weeknum = 1 THEN overtimehours ELSE 0 END) AS OT_Week1
,sum(CASE WHEN weeknum = 2 THEN overtimehours ELSE 0 END) AS OT_Week2
,sum(CASE WHEN weeknum = 3 THEN overtimehours ELSE 0 END) AS OT_Week3
,sum(CASE WHEN weeknum = 4 THEN overtimehours ELSE 0 END) AS OT_Week4
,sum(CASE WHEN weeknum = 5 THEN overtimehours ELSE 0 END) AS OT_Week5
FROM ot
GROUP BY Emp_Code, Period_End_Date

0

LVL 11

Author Closing Comment

That worked.

Thanks.
0

## Featured Post

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.