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

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

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

Open in new window


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

Open in new window

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 
            Work_Date BETWEEN DATEADD(day, -13, Period_End_Date)
                              AND
                              DATEADD(day, -7, Period_End_Date)
        THEN
            CASE
                WHEN 
                    (RegHrs +
                    TravHrs) > 80
                THEN
                    (RegHrs +
                    TravHrs) - 80
                ELSE
                    0
            END
    END
) AS OT_Week1,
SUM(
    CASE
        WHEN 
            Work_Date BETWEEN DATEADD(day, -6, Period_End_Date)
                              AND
                              Period_End_Date
        THEN
            CASE
                WHEN 
                    (RegHrs +
                    TravHrs) > 80
                THEN
                    (RegHrs +
                    TravHrs) - 80
                ELSE
                    0
            END
    END
) AS OT_Week2,

Open in new window


But that didn't seem to work.

Any help?

TIA
0
Clif
Asked:
Clif
  • 2
  • 2
1 Solution
 
ClifAuthor 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

Open in new window


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

Open in new window

0
 
danrosenthalCommented:
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

Open in new window

0
 
danrosenthalCommented:
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
		, DATEPART(week,work_date)-DATEPART(week,DATEADD(month,-1,period_End_Date)) AS weeknum
	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

Open in new window

0
 
ClifAuthor Commented:
That worked.

Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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