Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 successive records with self join

Author  Topic 

barnabeck
Posting Yak Master

236 Posts

Posted - 2014-09-22 : 11:39:29
We are using a presence checker to register when our employees show up at work, when they leave and whenever they change their working section.
The raw data has this format:
TABLE
-----

TIMESTAMP USERID SECTION INCIDENCE
------------------------------------------------------
2014-06-27 14:52:40.000 77675669 7 0
2014-06-27 15:08:33.000 77675669 6 8
2014-06-27 15:30:58.000 77675669 7 12
2014-06-27 18:52:00.000 77675669 11 15
2014-06-27 19:47:38.000 77675669 7 12
2014-06-27 23:30:18.000 77675669 7 0
In order to determine the exact time people had been working in the sections I have to match every record with the record of the successive timestamp I do:
SELECT TIMESTAMP as START, USERID, SECTION, INCIDENCE, MIN(TIME_END) as STOP
FROM (
SELECT *, TABLE2.TIMESTAMP as TIME_END
FROM TABLE
LEFT OUTER JOIN TABLE as TABLE2 on TABLE.USERID = TABLE2.USERID and
TABLE2.TIMESTAMP > Table.TIMESTAMP and
Convert(nvarchar(20),Table.TIMESTAMP,104) = Convert(nvarchar(20),Table2.TIMESTAMP,104) => same day
)a
GROUP BY TIMESTAMP, USERID, SECTION, INCIDENCE
This works much faster than my first solution where I was following the approach:
SELECT *, (SELECT TOP 1 TIMESTAMP
FROM TABLE as TABLE2
WHERE TABLE.USERID = TABLE2.USERID and
TABLE2.TIMESTAMP > Table.TIMESTAMP and
Convert(nvarchar(20),Table.TIMESTAMP,104) = Convert(nvarchar(20),Table2.TIMESTAMP,104)
ORDER BY TIMESTAMP)
FROM TABLE
Now here goes my question: How do I get the incidence of the successive timestamp in my query?

The key trick is that I first do a self join which increases the amount of records, including senseless matches from the first timestamp with the last one of that day.
Afterwards I group by all columns of TABLE in order to get only the successive timestamp.

But once I include TABLE2.INCIDENCE in the GROUP BY clause, of course the query stops working correctly.

I can't use the TIMESTAMP isself as it is not singular (there are several terminals in the plant); and I prefer not to use Timestamp in combination with Userid, as the userid is actually not stored directly and has to be retrieved through a couple of joined tables

My stomach feeling is that there must be a smarter way to include a second column once the successive timestamp has been determined.

Any ideas?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-09-22 : 12:14:07
[code]
-- *** Test Data ***
CREATE TABLE #t
(
StartTime datetime NOT NULL
,UserID int NOT NULL
,Section int NOT NULL
,Incidence int NOT NULL
);
INSERT INTO #t
VALUES('20140627 14:52:40.000', 77675669, 7, 0)
,('20140627 15:08:33.000', 77675669, 6, 8)
,('20140627 15:30:58.000', 77675669, 7, 12)
,('20140627 18:52:00.000', 77675669, 11, 15)
,('20140627 19:47:38.000', 77675669, 7, 12)
,('20140627 23:30:18.000', 77675669, 7, 0);
-- *** End Test Data ***

-- With SQL2012 and above
SELECT *
,LEAD(StartTime, 1) OVER (PARTITION BY UserID, DATEADD(day, DATEDIFF(day, 0, StartTime), 0) ORDER BY StartTime) AS EndTime
FROM #t;

-- With SQL2005 and above
WITH UserDays
AS
(
SELECT *
,DATEADD(day, DATEDIFF(day, 0, StartTime), 0) As UserDay
FROM #t
)
,OrderTime
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY UserID, UserDay ORDER BY StartTime) AS rn
FROM UserDays
)
SELECT T1.StartTime, T1.UserID, T1.Section, T1.Incidence, T2.StartTime AS EndTime
FROM OrderTime T1
LEFT JOIN OrderTime T2
ON T1.UserID = T2.UserID
AND T1.UserDay = T2.UserDay
AND T1.rn = T2.rn -1;
[/code]
Go to Top of Page

barnabeck
Posting Yak Master

236 Posts

Posted - 2014-09-22 : 18:25:48
wauuu.... I love this. I haven't applied it yet to my monster queries and therefore can't say anything about speed performance, but this is really... beautiful.
Great solution!

I am using SQL2008 but seeing that with SQL2012 you can bring that query down to one single line is just totally amazing
Go to Top of Page
   

- Advertisement -