January 4, 2006 at 12:47 pm
Hi there,
I am trying to sort data in a table that logs when people are in or out of the office in a select statement. So the table looks something like this:
ID Employee DateTime Status
== ======= ======= =====
1 Peter Jones 01/01/2005 In
2 Peter Jones 02/01/2005 In
3 Peter Jones 03/01/2005 Out
4 Peter Jones 04/01/2005 In
5 Peter Jones 05/01/2005 In
6 Peter Jones 06/01/2005 Out
7 Peter Jones 07/01/2005 Out
8 Jane Jones 01/01/2005 In
9 Jane Jones 02/01/2005 Out
I need the result to look something like this:
Peter Jones 01/01/2005 In
Peter Jones 03/01/2005 Out
Peter Jones 04/01/2005 In
Peter Jones 06/01/2005 Out
... and so on
Any help will be appreciated!!
Cheers
January 4, 2006 at 1:02 pm
Insuffient info.
Is the ID column always in ascending & sequential date order for each employee ?
What if Peter Jones' data had ended in an "In" ? Would you expect 4 records or 5 records in the final result ? i.e. do you just want In/Out pairs ?
January 4, 2006 at 1:26 pm
Are people going to be out all day or could they be out and in during the same 24-hour day?
Michelle
January 5, 2006 at 1:13 am
The ID column is ascending and is auto generated when a person logs in or out of the office.
You hit the nail on the head, I do just want the pairs so if Peter goes out today and comes back in 2 days I need the result of the query to be:
Peter Jones 05/01/2006 Out
Peter Jones 07/01/2006 In
January 5, 2006 at 4:05 am
SELECT Employee, [DateTime], Status
FROM @table a
WHERE NOT EXISTS(SELECT * FROM @table b
WHERE b.Employee = a.Employee
AND b.[DateTime] = a.[DateTime]-1
AND b.Status = a.Status)
ORDER BY Employee, [DateTime]
Far away is close at hand in the images of elsewhere.
Anon.
January 5, 2006 at 6:33 am
Excellent, thank you very much for the help!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply