accumulated total check

[antiskeptic]

I'm not sure the subject is quite descriptive enough, but here is my situation.

I have created a stored procedure (SQL Server 2000 - Using Query Analyzer)that gives me a daily record of employees' hours and workdays. I need to check this total on a daliy basis within this query. In other words, I need to add the work hours as each day passes and check that total to see if it's over 40 hours. These employees are eligible for overtime...but if they hit 40+ hours on the 7th consecutive day other factors come into play. So I want to "toss out" any one who made 40 hours BEFORE the 7th consecutive day. Basically, they have no bering in this query.

I was under a different impression of my goal when I first wrote my query...below is my temporary table that I started with...that picks out the employees with 7 consecutive days AND 40+ hours. But I'm still picking up people who hit over 40 hours on the 6th day.


SELECT distinct employee.serial_number,
employee.last_name, employee.first_name,
Manager.last_name ManagerLast_name, Manager.first_name ManagerFirst_Name,
department.department_id CommunityID, department.description CommunityDesc,
day,process_raw.item_date,process_raw.start_time,
process_raw.stop_time,process_raw.Week_ending,
(process_raw.stop_time-process_raw.start_time)/60 actualtime,
timecode_description.timecode_id,
process_raw.processing_pay_period

Into #WorkWeekHours

FROM
employee,
process_raw,
pay_policy,
department,
Employee AS manager,
timecode_description

WHERE employee.employee_sys_id = process_raw.employee_sys_id
and process_raw.timecode_sys_id = timecode_description.timecode_sys_id
and employee.pay_policy_sys_id = pay_policy.pay_policy_sys_id
and department.manager_sys_id *= manager.employee_sys_id
AND employee.effective_start_date = (SELECT MAX (EE.effective_start_date)
FROM employee AS EE
WHERE ee.employee_sys_id = employee.employee_sys_id)
and timecode_description.Timecode_id = 'REG'
and employee.department_sys_id = department.department_sys_id
and pay_policy.pay_policy_id in ('HF1KY','HT1KY','HP1KY')
ORDER BY employee.last_name,[ItemDate],[start_time]


SELECT distinct serial_number,weekendingdate,
count(distinct(itemDate))ItemDate,
sum(actualtime) ActualTime, first_name, last_name,communityid

INTO #EligbleForOT

FROM #WorkWeekHours

GROUP BY serial_number,weekendingdate, first_name, last_name,communityid
HAVING count(distinct(itemDate))>=7
and sum(actualtime)>40
--select distinct * from #EligbleForOT

I thought a LOOP or WHILE statement might do the trick, to add up the hours as each day passes. Then skip over all employees that hit 40 hours before the 7th day. But I don't know the syntax.
And I know I haven't explained this very good, so please ask any questions you need to understand this situation a little better. Thank you so much in advance for any help you can provide.

Sincerely,
Antiskeptic

 

[SQLSister]

You can write a query that finds the people who have not gotten 40 hours by day 6, can't you?

If so then I would create it as a derived table and join it to the other query.



Questions about posting. See faq183-874

 

[antiskeptic]

Day 6 is not the only day that 40 hours can be reached...if I used that logic, I would have to do the same "sub query" for day 1, 2, 3, etc...

Does no one know of a way to add the hours as each day passes and check when the 40 hour mark is hit and if the day count at that point is less than 7 then "kick it out"
and only keep those records of employees who hit over 40 hours ON day 7?

Please help.

Antiskeptic