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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy