Consecutive Hours Query

This will challenge you. I have a table that shows time in/ time out
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:
> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>