Hi,
I have a table in which there are records about works accesing entrance doors.
DECLARE @doorStatistics TABLE
( id INT IDENTITY,
[user] VARCHAR(250),
accessDate DATETIME,
accessType VARCHAR(5)
)
Sample records:
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('John Wayne','2009-09-01 07:02:43.000','IN')
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('Bruce Willis','2009-09-01 07:12:43.000','IN')
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('Bruce Willis','2009-09-01 07:22:43.000','OUT')
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('John Wayne','2009-09-01 07:32:43.000','OUT')
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('John Wayne','2009-09-01 07:37:43.000','IN')
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('Bruce Willis','2009-09-01 07:42:43.000','IN')
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('John Wayne','2009-09-01 07:48:43.000','OUT')
INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('Bruce Willis','2009-09-01 07:52:43.000','OUT')
What I want to do is a query, which gives me following result (based on above example):
| user | date | inHour | outHour |
|--------------|------------|----------|----------|
| John Wayne | 2009-09-01 | 07:02:43 | 07:48:43 |
| Bruce Willis | 2009-09-01 | 07:12:43 | 07:22:43 |
| John Wayne | 2009-09-02 | 07:37:43 | 07:48:43 |
| Bruce Willis | 2009-09-02 | 07:42:43 | 07:52:43 |
Query I made is the following:
SELECT [user], accessDate AS [in date],
(SELECT MIN(accessDate)
FROM @doorStatistics ds2
WHERE accessType = 'OUT'
AND ds2.accessDate > ds.accessDate
AND ds.[user] = ds2.[user]) AS [out date]
FROM @doorStatistics ds
WHERE accessType = 'IN'
But it is not good, because when user forget to register his/her entrance it will produce for example something like this:
| user | date | inHour | outHour |
|--------------|------------|----------|----------|
| John Wayne | 2009-09-02 | 07:02:43 | 07:48:43 |
| John Wayne | 2009-09-02 | 07:02:43 | 09:26:43 |
While it should be
| user | date | inHour | outHour |
|--------------|------------|----------|----------|
| John Wayne | 2009-09-02 | 07:02:43 | 07:48:43 |
| John Wayne | 2009-09-02 | NULL | 09:26:43 |
The 2nd reason the query is not good is the performance. I have over 200 000 records and SELECT for every row slows down the query.
The possible solution may be to join two tables
SELECT * FROM @doorStatistics WHERE accessType = 'IN'
with
SELECT * FROM @doorStatistics WHERE accessType = 'OUT'
but I have no idea what conditions to put to get correct date. Maybe some MAX or MIN functions can be put there but I have no idea.
I don't want to create temporary table and use cursors.