[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 633

# Calculating Weekly Overtime Biweekly

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
Clif
• 2
• 2
1 Solution

Author Commented:
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

Commented:
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

Commented:
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

Author Commented:
That worked.

Thanks.
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.