SQL STATEMENT

[Kent Prokopy]

I have a table with data that looks like this:

EMP# DATETIME_STAMP EVENT_TYPE
3118 2/8/2004 9:00:10 AM 1
3118 2/8/2004 11:30:26 AM 2
3118 2/8/2004 11:30:54 AM 1
3118 2/8/2004 2:01:34 PM 2
3118 2/8/2004 3:01:14 PM 1
3118 2/8/2004 8:00:25 PM 2
3119 2/8/2004 9:00:00 AM 1
3119 2/8/2004 2:01:34 PM 2

FYI:
Event_Type 1=Signin
Event_Type 2=Signout

I need an sql statement that will return the results like this:
EMP# START END
DURATION
3118 2/8/2004 9:00:00 AM 2/8/2004 11:00:00 AM 7200
3118 2/8/2004 11:30:00 AM 2/8/2004 2:00:00 PM 9000
3118 2/8/2004 3:00:00 PM 2/8/2004 8:00:00 PM 18000
3119 2/8/2004 9:00:00 AM 2/8/2004 2:00:00 PM 18000

Thank you in advance...

 

[Allen Browne]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

 

[Allen Browne]

Use a subquery.

This example shows how to select the next date/time value for the employee.
You need to do more if you have to cope with the possibility that someone
forgot to sign out.

SELECT Table3.[EMP#], Table3.DateTime_Stamp AS START,
(SELECT Min(Dupe.DateTime_Stamp) FROM Table3 AS Dupe
WHERE (Dupe.[EMP#] = Table3.[EMP#])
AND (Dupe.Event_Type = 2)
AND (Dupe.DateTime_Stamp > Table3.DateTime_Stamp) ) AS [END],
DateDiff("s",[START],[END]) AS DURATION
FROM Table3
WHERE Table3.Event_Type = 1;

 

[Kent Prokopy]

Thank you Allen, I will test this first thing tomorrow morning.

Kent Prokopy

Use a subquery.

This example shows how to select the next date/time value for the employee.
You need to do more if you have to cope with the possibility that someone
forgot to sign out.

SELECT Table3.[EMP#], Table3.DateTime_Stamp AS START,
(SELECT Min(Dupe.DateTime_Stamp) FROM Table3 AS Dupe
WHERE (Dupe.[EMP#] = Table3.[EMP#])
AND (Dupe.Event_Type = 2)
AND (Dupe.DateTime_Stamp > Table3.DateTime_Stamp) ) AS [END],
DateDiff("s",[START],[END]) AS DURATION
FROM Table3
WHERE Table3.Event_Type = 1;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with data that looks like this:

EMP# DATETIME_STAMP EVENT_TYPE
3118 2/8/2004 9:00:10 AM 1
3118 2/8/2004 11:30:26 AM 2
3118 2/8/2004 11:30:54 AM 1
3118 2/8/2004 2:01:34 PM 2
3118 2/8/2004 3:01:14 PM 1
3118 2/8/2004 8:00:25 PM 2
3119 2/8/2004 9:00:00 AM 1
3119 2/8/2004 2:01:34 PM 2

FYI:
Event_Type 1=Signin
Event_Type 2=Signout

I need an sql statement that will return the results like this:
EMP# START END
DURATION
3118 2/8/2004 9:00:00 AM 2/8/2004 11:00:00 AM 7200
3118 2/8/2004 11:30:00 AM 2/8/2004 2:00:00 PM 9000
3118 2/8/2004 3:00:00 PM 2/8/2004 8:00:00 PM 18000
3119 2/8/2004 9:00:00 AM 2/8/2004 2:00:00 PM 18000

Thank you in advance...