February 3, 2009 at 7:47 pm
I Have a table with this fields: Event_ID (PK), UserID, UserName, EVENTDATETIME, IN_OUT
This table collect data each time an employee past the batch by the In card reader and Out Car reader in various doors. Each emplyee can have more thant one "IN" event and "OUT" event registered in the table. I want to perform a select query to return me only the first "IN" event and the last "OUT" event in the day for each employee. Example of data:
Event_ID User_ID EVENTDATETIME IN_OUT UserName
1 1 2009-02-03 1:35 IN Joe
2 2 2009-02-03 1:45 IN Clark
3 3 2009-02-03 1:50 IN Mary
4 2 2009-02-03 1:51 OUT Clark
5 2 2009-02-03 1:53 IN Clark
6 1 2009-02-03 1:54 OUT Joe
7 1 2009-02-03 1:59 IN Joe
8 3 2009-02-03 2:00 OUT Mary
9 1 2009-02-03 2:11 OUT Joe
10 2 2009-02-03 2:12 OUT Clark
11 3 2009-02-03 2:15 IN Mary
12 3 2009-02-03 2:16 OUT Mary
The select output will be:
Event_ID User_ID EVENTDATETIME IN_OUT USERNAME
1 1 2009-02-03 1:35 IN Joe
2 2 2009-02-03 1:45 IN Clark
3 3 2009-02-03 1:50 IN Mary
9 1 2009-02-03 2:11 OUT JOE
10 2 2009-02-03 2:12 OUT Clark
12 3 2009-02-03 2:16 OUT Mary
Anyone have idea how I can create the select statement?:w00t:
Thnks in advance
February 3, 2009 at 8:35 pm
First off, please take note of how I prepared your test data so that it could be used easily. You should read the articles in my signature to learn how to post data in a easy to use format.
Anyway here is a solution to your problem.
-- table to hold test data
DECLARE @test-2 TABLE
(
Event_ID INT,
USER_ID INT,
EVENTDATETIME DATETIME,
IN_OUT VARCHAR(3),
UserName VARCHAR(10)
)
-- populate table with test data
INSERT INTO @test-2
(
Event_ID,
[USER_ID],
EVENTDATETIME,
IN_OUT,
UserName
)
Select
1,
1,
'2009-02-03 1:35',
'IN',
'Joe'
UNION ALL
Select
2,
2,
'2009-02-03 1:45',
'IN',
'Clark'
UNION ALL
Select
3,
3,
'2009-02-03 1:50',
'IN',
'Mary'
UNION ALL
Select
4,
2,
'2009-02-03 1:51',
'OUT',
'Clark'
UNION ALL
Select
5,
2,
'2009-02-03 1:53',
'IN',
'Clark'
UNION ALL
Select
6,
1,
'2009-02-03 1:54',
'OUT',
'Joe'
UNION ALL
Select
7,
1,
'2009-02-03 1:59',
'IN',
'Joe'
UNION ALL
Select
8,
3,
'2009-02-03 2:00',
'OUT',
'Mary'
UNION ALL
Select
9,
1,
'2009-02-03 2:11',
'OUT',
'Joe'
UNION ALL
Select
10,
2,
'2009-02-03 2:12',
'OUT',
'Clark'
UNION ALL
Select
11,
3,
'2009-02-03 2:15',
'IN',
'Mary'
UNION ALL
Select
12,
3,
'2009-02-03 2:16',
'OUT',
'Mar' ;
SELECT
Event_ID,
[USER_ID],
EVENTDATETIME,
IN_OUT,
UserName
FROM
@test-2 AS T JOIN
-- derived table gets the Key data and the MIn and MAx
-- eventdatetimes for each user and in_out
(
SELECT
[USER_ID],
MAX(EVENTDATETIME) AS OUT_TIME,
MIN(EVENTDATETIME) AS IN_TIME,
IN_OUT
FROM
GROUP BY
[USER_ID],
IN_OUT) AS T2 ON
T.[USER_ID] = T2.[USER_ID] AND
T.IN_OUT = T2.IN_OUT AND
-- Case statement gets the correct time to join on.
CASE
WHEN T.in_Out = 'IN' THEN T2.IN_TIME
ELSE T2.OUT_TIME
END = T.EVENTDATETIME
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 5:40 am
Hi Jack
Thanks for your response, but one more question, this will work with a table with about 12,000 records?
February 4, 2009 at 6:14 am
It will work with a table of any size. It will take longer to run, but it will work. As an aside 12000 rows really isn't a large amount of data.
If you index correctly and maintain those indexes SQL Server will scale well beyond 12000 rows.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 8, 2009 at 2:38 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply