Geek Answers Handbook

Select strings ignoring narrow times

I have a table [EventLog]that contains the reading data written by a card reader that controls the shutter. However, the same card code [epc]can be read several times when the card holder holds for some time next to the reader.

I want to show readings for the same code in the same reader, but, for example, ignore reading for 2 minutes.

Example: EventLog

ID  EPC ReaderID    LogTime
1   1234    1   2016-04-15 12:33:55
2   1234    1   2016-04-15 12:34:05
3   1234    1   2016-04-15 12:34:10
4   4321    2   2016-04-15 12:34:12
5   4321    2   2016-04-15 12:34:14

Desired Result:

ID  EPC ReaderID    LogTime
1   1234    1   2016-04-15 12:33:55
4   4321    2   2016-04-15 12:34:12

Now I use the windows function LAGto determine the difference in minutes between each read and the previous:

SELECT EPC, ReaderName, PersonName, LogTime
FROM (
SELECT EPC, ReaderName, PersonName, LogTime,
    DATEDIFF(MINUTE, LAG(LogTime) OVER (PARTITION BY EPC, ReaderID ORDER BY LogTime), LogTime) diff_prev
FROM EventLog l
    LEFT OUTER JOIN Person p ON p.EPC = l.EPC
    INNER JOIN Reader r ON r.ID = l.ReaderID
) tbl
WHERE diff_prev IS NULL OR diff_prev >= @ignoreMinutes
ORDER BY LogTime

Where @ignoreMinutesis a parameter indicating how many minutes to ignore the same read.

, 3 . :

ID  EPC ReaderID    LogTime     diff_prev
1   1234    1   2016-04-15 12:33:55     NULL
2   1234    1   2016-04-15 12:34:05     0
3   1234    1   2016-04-15 12:34:10     0
4   1234    1   2016-04-15 12:34:32     0
5   1234    1   2016-04-15 12:34:54     0
6   1234    1   2016-04-15 12:35:14     0
7   1234    1   2016-04-15 12:35:34     0
8   1234    1   2016-04-15 12:35:54     0
9   1234    1   2016-04-15 12:36:04     0
10  1234    1   2016-04-15 12:36:15     0
11   4321   2   2016-04-15 12:44:12     NULL
12   4321   2   2016-04-15 12:44:14     0

, @ignoreMinutes = 1 ID = 1, 11, - diff_prev = 0. ID = 1, 6, 10, 11

? !

+4
1

"", . , , 1, 6, 10, 11.

DECLARE @intervalSeconds INT
SET @intervalSeconds = 60;

WITH EL AS
(
    -- Select first record for each EPC, this is the baseline for recursion
    SELECT
        ID,
        EPC,
        LogTime
    FROM EventLog
    WHERE LogTime = (SELECT MIN(LogTime) FROM EventLog IEL WHERE IEL.EPC = EventLog.EPC)
    -- Add following events
    UNION ALL
    SELECT
        ID,
        EPC,
        LogTime
    FROM
    (
        SELECT
            NextEvent.ID,
            NextEvent.EPC,
            NextEvent.LogTime,
            ROW_NUMBER() OVER(PARTITION BY NextEvent.EPC ORDER BY NextEvent.LogTime) eventNumber
        FROM EventLog NextEvent
        JOIN
        (
            SELECT 
                ID,
                ROW_NUMBER() OVER(PARTITION BY EPC ORDER BY LogTime DESC) eventNumber, -- Reverse numbering to get last row by readNumber = 1
                EPC,
                LogTime
            FROM EL -- Recursion
        ) PreviousEvent -- Here we have all already selected events wich we're interested in
            ON PreviousEvent.EPC = NextEvent.EPC
                AND PreviousEvent.eventNumber = 1 -- We need only the last one for each EPC
        WHERE DATEDIFF(SECOND, PreviousEvent.LogTime, NextEvent.LogTime) > @intervalSeconds

    ) NextCandidateEvents -- Here we have all events with desired interval offset for each EPC
    WHERE NextCandidateEvents.eventNumber = 1 -- We need only the first one for each EPC
)
SELECT * FROM EL
ORDER BY EPC, LogTime
+2

More articles:


All Articles