• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Conditional Sums within SQL

I am attempting to calculate overtime hours worked from a query that obtains hours worked over a given time period.  The issue I'm having is that the query pulls back multiple associates for each date in the given date range and groups by 8 of the 9 columns in the data set.  So I'm at a loss at trying to determine how I can calculate each associate's overtime (if they worked any).

I am attaching the code I'm using as of now to pull back the hours worked.
SELECT	a.timeclock_date,
	a.employee_id, 
	b.payroll_id, 
	CASE WHEN a.work_code = 1 THEN 'WORKED HOURS' 
	WHEN a.work_code = 10 THEN 'VACATION' 
	WHEN a.work_code = 11 THEN 'FUNERAL' 
	WHEN a.work_code = 15 THEN 'SICK' 
	WHEN a.work_code = 16 THEN 'PERSONAL' ELSE a.work_code END   as work_code , 
	b.location_nmbr, 
	b.lname, 
	b.fname, 
	c.department_ID, 
	SUM(total_hours) AS total_hours
							
FROM 	edw1.timeclock a, 
	edw1.employee b, 
	(SELECT a.employee_ID AS payroll_id, 
	        a.department_ID, 
	        a.JOB, 
	        MAX(a.work_dt) AS work_dt
	 FROM   edw1.payroll_job a, 
	        (SELECT employee_ID AS payroll_id, 
		       MAX(work_dt) AS work_dt 
	         FROM   edw1.payroll_job 
		WHERE LOCATION = 01002 
                  GROUP BY 1) b 
          WHERE 	a.LOCATION = 01002 
          AND      a.employee_ID = b.payroll_id 
          AND	a.action_dt= b.action_dt
          GROUP BY 1,2,3) c 
							 
WHERE a.employee_id = b.employee_id 
AND b.payroll_id= c.payroll_id 
AND b.loc_num = 1002 
AND a.timeclock_date BETWEEN '2008-10-01' AND '2008-10-29'
GROUP BY 1,2,3,4,5,6,7,8

Open in new window

0
SpeedsterZ28
Asked:
SpeedsterZ28
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Can you just make your original query derived and then do formulat outside.  For example if overtime is anything over 160 hours (40 hours x 4 work weeks):
SELECT *
, CAST(CASE WHEN total_hours > 160 THEN 1 ELSE 0 END AS BIT) AS WorkedOvertime
FROM (
SELECT	a.timeclock_date,
	a.employee_id, 
	b.payroll_id, 
	CASE WHEN a.work_code = 1 THEN 'WORKED HOURS' 
	WHEN a.work_code = 10 THEN 'VACATION' 
	WHEN a.work_code = 11 THEN 'FUNERAL' 
	WHEN a.work_code = 15 THEN 'SICK' 
	WHEN a.work_code = 16 THEN 'PERSONAL' ELSE a.work_code END   as work_code , 
	b.location_nmbr, 
	b.lname, 
	b.fname, 
	c.department_ID, 
	SUM(total_hours) AS total_hours
							
FROM 	edw1.timeclock a, 
	edw1.employee b, 
	(SELECT a.employee_ID AS payroll_id, 
	        a.department_ID, 
	        a.JOB, 
	        MAX(a.work_dt) AS work_dt
	 FROM   edw1.payroll_job a, 
	        (SELECT employee_ID AS payroll_id, 
		       MAX(work_dt) AS work_dt 
	         FROM   edw1.payroll_job 
		WHERE LOCATION = 01002 
                  GROUP BY 1) b 
          WHERE 	a.LOCATION = 01002 
          AND      a.employee_ID = b.payroll_id 
          AND	a.action_dt= b.action_dt
          GROUP BY 1,2,3) c 
							 
WHERE a.employee_id = b.employee_id 
AND b.payroll_id= c.payroll_id 
AND b.loc_num = 1002 
AND a.timeclock_date BETWEEN '2008-10-01' AND '2008-10-29'
GROUP BY 1,2,3,4,5,6,7,8
) derived

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
If it needs to be determined based on aggregate date from some of the other records that are showing as independing rows because of the grouping and you are running SQL 2005 you can try summing in an OVER statement that only groups by employeeID.

SUM(total_hours) OVER (PARTITION BY a.employeeID ORDER BY a.timeclock_date) AS total_emp_hours

You can then analyze that in outer query or try putting that in CASE statement -- don't have SQL Manager to try at the moment.

CASE WHEN (SUM(total_hours) OVER (PARTITION BY a.employeeID ORDER BY a.timeclock_date)) > 160 THEN 1 ELSE 0 END AS WorkedOvertime
0
 
SpeedsterZ28Author Commented:
Thank you very much!  Just what I needed!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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