Selecting Records Between Time ranges on Dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Selecting Records Between Time ranges on Dates

Author

Topic

Tweter
Starting Member

1 Post

Posted - 2008-05-08 : 16:08:27

I'm looking for a way to link records that would be related to an employee that worked during that shift.

we have 2 shifts days and nights going from 6:00AM to 6:00PM

we have records that are written to a table as events happen on equipment on site.

"Equip_Trans"

"Start_time" "Equip_ID" "Shift_date" "Status" "End_Time"
Date-Time, int, Date, varchar(3), Date-time
5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03
5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM

I have another transaction table "Operator_Trans"
"Operator" "Date" "Shift"

jane Doe, 5/6/2008, 1
Pete Who, 5/6/2008, 2
Sam What, 5/7/2008, 1
Pete Who, 5/7/2008, 2
john Doe, 5/8/2008, 1
jane Doe, 5/8/2008, 2

and last a third table "Shift"
Shift, Start_Time, End_Time
1, 6:00AM, 6:00PM
2, 6:00PM, 6:00AM

I need out put that will show all Equipment status's that started during the shifts worked by an operator

Pete Who, 5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
Pete Who, 5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03 AM
Pete Who, 5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
Pete Who, 5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
john Doe, 5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
john Doe, 5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM

I am not completely sure the direction I should start off with. I have some SQL knowlege but I would still consider myself new.

Thanks

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-08 : 18:48:48

[code]
-- Create Table
CREATE TABLE #Equip_Trans (Start_time datetime,Equip_ID int,Shift_date datetime,Status varchar(3),End_Time datetime)
CREATE TABLE #Operator_Trans (Operator varchar(10),Date datetime,Shift int)
CREATE TABLE #Shift (Shift int, Start_Time datetime, End_Time datetime)

-- Populate Test Data
INSERT INTO #Equip_Trans
SELECT '5/8/2008 4:23:25 AM', 0200, '5/8/2008', 'M20', '5/8/2008 5:15:34 AM' UNION ALL
SELECT '5/8/2008 2:18:45 AM', 0206, '5/8/2008', 'M24', '5/8/2008 3:10:03 AM' UNION ALL
SELECT '5/8/2008 5:15:34 AM', 0200, '5/8/2008', 'M13', '5/8/2008 7:24:36 AM' UNION ALL
SELECT '5/8/2008 3:10:03 AM', 0206, '5/8/2008', 'M20', Null UNION ALL
SELECT '5/8/2008 7:24:36 AM', 0200, '5/8/2008', 'O21', '5/8/2008 7:55:34 AM' UNION ALL
SELECT '5/8/2008 7:55:34 AM', 0200, '5/8/2008', 'M24', '5/8/2008 8:36:34 AM'

INSERT INTO #Operator_Trans
SELECT 'jane Doe', '5/6/2008', 1 UNION ALL
SELECT 'Pete Who', '5/6/2008', 2 UNION ALL
SELECT 'Sam What', '5/7/2008', 1 UNION ALL
SELECT 'Pete Who', '5/7/2008', 2 UNION ALL
SELECT 'john Doe', '5/8/2008', 1 UNION ALL
SELECT 'jane Doe', '5/8/2008', 2

INSERT INTO #Shift
SELECT 1, '6:00AM', '6:00PM' UNION ALL
SELECT 2, '6:00PM', '6:00AM'

-- Drop Tables
DROP TABLE #Equip_Trans
DROP TABLE #Operator_Trans
DROP TABLE #Shift

-- Query
--With 12 hour shifts, 6 PM - 6 AM shift will always overlap to the next day, Hence the addition of 1 day to shift end date.

SELECT DT.Operator,ET.Start_time,ET.Equip_ID,ET.Shift_date,ET.Status,ET.End_Time
FROM #Equip_Trans ET LEFT JOIN
(
SELECT OT.Operator,CONVERT(varchar,OT.Date,102)date,(OT.DATE+ST.Start_Time) AS ShiftStartTime,
(CASE WHEN OT.Shift = 2 THEN DATEADD(Day,1,OT.Date) ELSE OT.Date END + ST.End_Time)AS ShiftEndTime
FROM #Operator_Trans OT INNER JOIN #Shift ST ON OT.Shift = ST.Shift
) DT
ON
ET.Start_time BETWEEN DT.ShiftStartTime AND DT.ShiftEndTime

-- Results
Operator Start_time Equip_ID Shift_Date Status End_Time
Pete Who 2008-05-08 04:23:25.000 200 2008-05-08 00:00:00.000 M20 2008-05-08 05:15:34.000
Pete Who 2008-05-08 02:18:45.000 206 2008-05-08 00:00:00.000 M24 2008-05-08 03:10:03.000
Pete Who 2008-05-08 05:15:34.000 200 2008-05-08 00:00:00.000 M13 2008-05-08 07:24:36.000
Pete Who 2008-05-08 03:10:03.000 206 2008-05-08 00:00:00.000 M20 NULL
john Doe 2008-05-08 07:24:36.000 200 2008-05-08 00:00:00.000 O21 2008-05-08 07:55:34.000
john Doe 2008-05-08 07:55:34.000 200 2008-05-08 00:00:00.000 M24 2008-05-08 08:36:34.000
[/code]

Thanks
Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources