Nth Consecutive Day Select

[antiskeptic]

I have a Store Proc that pulls back employee and date information. I only need to display the 7th consecutive date and all it's corresponding information

example:
Employee ItemDate Start Stop WeekEnd #Hours
Barney Rubble 6/26/04 12:00 6:00 7/02/04 6
Barney Rubble 6/27/04 12:00 6:00 7/02/04 6
Barney Rubble 6/28/04 12:00 3:00 7/02/04 3
Barney Rubble 6/29/04 12:00 6:00 7/02/04 6
Barney Rubble 6/30/04 10:00 6:00 7/02/04 8
Barney Rubble 7/01/04 12:00 6:00 7/02/04 6
Barney Rubble 7/02/04 12:00 4:00 7/02/04 4
George Jetson 6/26/04 12:00 6:00 7/02/04 6
George Jetson 6/27/04 12:00 6:00 7/02/04 6
George Jetson 6/28/04 12:00 3:00 7/02/04 3
George Jetson 6/29/04 12:00 6:00 7/02/04 6
George Jetson 6/30/04 10:00 6:00 7/02/04 8
George Jetson 7/01/04 12:00 6:00 7/02/04 6
George Jetson 7/02/04 12:00 4:00 7/02/04 4

All I want to see out of this set of records, is the rows of the 7th consecutive days for each employee. So I would only have 2 rows for the 7/02/04 records. Does anyone have a clue how I would accomplish this?

Many thanks in advance for any help, or nudge in the right direction.

Thank you,
Antiskeptic

 

[JohnDTampaBay]

Will these rows always have ItemDate = WeekEnd or is this just a coincidence?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[MatthewPeters]

Would this work?

select Employee, ItemDate, Start, Stop, WeekEnd, #Hours
from yourTableName
where itemDate = weekend


matt

 

[antiskeptic]

No this was just coincidence...it will not always be this way. For example if someoen works only 6 days, and the 6th day is on the weekend date. I only want the records when 7 consecutive days have been reached. The weekending date was just one of my other fields...sorry for the confusion.

Sincerely,
Antiskeptic

 

[SQLSister]

Can you change your database structure at all?

You can solve this problem with a while loop or cursor, but they are notoriously slow. I would be more inclined to add a field that increments the number of consecutive days worked at the time the record is inserted and then query on that field.

Questions about posting. See faq183-874

 

[JohnDTampaBay]

You're looking for everyone who worked all seven days in the week? So if you had this additional data, you would expect to see zero rows for Yogi and two rows for Grape Ape (7-2 and 7-9)? Yes?

Yogi Bear 6/26/04 12:00 6:00 7/02/04 6
Yogi Bear 6/27/04 12:00 6:00 7/02/04 6
Yogi Bear 6/28/04 12:00 3:00 7/02/04 3
Yogi Bear 6/30/04 10:00 6:00 7/02/04 8
Yogi Bear 7/01/04 12:00 6:00 7/02/04 6
Yogi Bear 7/02/04 12:00 4:00 7/02/04 4
Grape Ape 6/26/04 12:00 6:00 7/02/04 6
Grape Ape 6/27/04 12:00 6:00 7/02/04 6
Grape Ape 6/28/04 12:00 3:00 7/02/04 3
Grape Ape 6/29/04 12:00 6:00 7/02/04 6
Grape Ape 6/30/04 10:00 6:00 7/02/04 8
Grape Ape 7/01/04 12:00 6:00 7/02/04 6
Grape Ape 7/02/04 12:00 4:00 7/02/04 4
Grape Ape 7/03/04 12:00 6:00 7/09/04 6
Grape Ape 7/04/04 12:00 6:00 7/09/04 6
Grape Ape 7/05/04 12:00 3:00 7/09/04 3
Grape Ape 7/06/04 12:00 6:00 7/09/04 6
Grape Ape 7/07/04 10:00 6:00 7/09/04 8
Grape Ape 7/08/04 12:00 6:00 7/09/04 6
Grape Ape 7/09/04 12:00 4:00 7/09/04 4

SELECT *
FROM YourData a
  INNER JOIN 
   (SELECT Employee, WeekEnd
    FROM YourData
    GROUP BY Employee, WeekEnd
    HAVING Count(*) = 7
    ) b ON a.Employee = b.Employee
       AND a.WeekEnd = b.WeekEnd

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[ESquared]

Do the 7 consecutive days have to end on a WeekEnd date?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[antiskeptic]

Well, because there are only 7 days in the week...working the seven consecutive days, makes it impossible to end on any other day. SO I guess that kind of makes it a little easier. But only if all 7 days are worked. I need the information (details) for the 7th day only however.
I tried the solution displayed by John above, and it doesn't exactly work the way I need it.
Maybe becuase I've messed up explaining it. However, John, the solution you provided, made perfect sense and should work. But something is not right in the mix, because I get no records returned. (But I have to say...I love the characters you picked. *ha* A true Hanna-Barbera fan! )

Here is my code up till now: (maybe this will help anyone understand my plight.)

Each employee who works 7 consecutive days gets OT for any hours past 40 hours for the week, automatically. Then all other hours worked on the 7th day has to be entered mannually by someone as an SREG & OTKY records (timecode_id).
So anyone with hours on the seventh consecutive days should have at least 3 records with the same start & stop time and date and timecode_id's of REG,SREG,OTKY. And if they go over 40 hours on that day or before, then all hours over that get an OT record. Does this make sense?
Let me know if I've just made it worse. *ha*

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,
convert(datetime,(dateadd(day,process_raw.item_date,'1/1/1930')))
[ItemDate],process_raw.start_time,process_raw.stop_time,
convert(datetime,(dateadd(day,process_raw.Week_ending,'1/1/1930'))) WeekEndingDate,
substring(convert( varchar, dateadd( mi, process_raw.start_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [starttime],
substring(convert( varchar, dateadd( mi, process_raw.stop_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [stoptime], 
((process_raw.stop_time - process_raw.start_time)/60) ActualTime,
timecode_description.timecode_id,
convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930')))
PayPeriodEndDate
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

--SREG records
Drop Table #SREGRecords
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,
convert(datetime,(dateadd(day,process_raw.item_date,'1/1/1930')))
[ItemDate],process_raw.start_time,
process_raw.stop_time,
convert(datetime,(dateadd(day,process_raw.Week_ending,'1/1/1930'))) WeekEndingDate,
substring(convert( varchar, dateadd( mi, process_raw.start_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [starttime],
substring(convert( varchar, dateadd( mi, process_raw.stop_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [stoptime], 
((process_raw.stop_time - process_raw.start_time)/60) ActualTime,
timecode_description.timecode_id,
convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930')))
PayPeriodEndDate
Into #SREGRecords
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 = 'SREG'
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]

--OT Records
Drop Table #OTRecords
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,
convert(datetime,(dateadd(day,process_raw.item_date,'1/1/1930')))
[ItemDate],process_raw.start_time,
process_raw.stop_time,
convert(datetime,(dateadd(day,process_raw.Week_ending,'1/1/1930'))) WeekEndingDate,
substring(convert( varchar, dateadd( mi, process_raw.start_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [starttime],
substring(convert( varchar, dateadd( mi, process_raw.stop_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [stoptime], 
((process_raw.stop_time - process_raw.start_time)/60) ActualTime,
timecode_description.timecode_id,
convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930')))
PayPeriodEndDate
Into #OTRecords
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 = 'OT'
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]

--OTKY Records
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,
convert(datetime,(dateadd(day,process_raw.item_date,'1/1/1930')))
[ItemDate],process_raw.start_time,
process_raw.stop_time,
convert(datetime,(dateadd(day,process_raw.Week_ending,'1/1/1930'))) WeekEndingDate,
substring(convert( varchar, dateadd( mi, process_raw.start_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [starttime],
substring(convert( varchar, dateadd( mi, process_raw.stop_time, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6) [stoptime], 
((process_raw.stop_time - process_raw.start_time)/60) ActualTime,
timecode_description.timecode_id,
convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930')))
PayPeriodEndDate
Into #OTKYRecords
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 = 'OTKY'
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]

Thanks,
Antiskeptic

 

[ESquared]

JohnDTampaBay's solution should work.

Oh, you need to consider what you want to happen for any employee who works past midnight. Is it counted as another day or does only the start time matter?

In California there are all sorts of rules about number of hours in a day and hours between shifts and that sort of thing. I'm guessing you're doing this for a state with simpler rules?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[antiskeptic]

well, I tried John's solution and it produced no records. So I guess I'm still at a loss. Ah well, thank you anyway, and I'll keep plugging away at it.

Yes, I am doing this for the state of Kentucky. Payroll rules are just SO much fun! ~sacrastic~ *ha*

Thank you,
Antiskeptic