successive records with self join

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources