Search code examples
sql-serversql-timestamp

Retrieve the immediate previous row based on Timestamp in SSMS

I need to retrieve a set of rows based on a filtering criteria on Timestamp, which is - Timestamps with Time > 06:OO AM However, I also need the immediate previous record less than 06:00 AM to calculate event duration's correctly

How can I retrieve the rows having Time > 6:00 AM AND the last row which is less than 6:00 AM?

Sample: I have these rows in the database and i have to calculate Event Duration's. If I just filter on Timestamp > 6:00 AM, the second also gets filtered out which i need for further calculations

Actual:

  1. Event1 | Resource1 | 2019-05-26 04:38:16.1156432
  2. Event2 | Resource1 | 2019-05-26 05:51:23.2356984
  3. Event3 | Resource1 | 2019-05-26 06:01:32.1033333
  4. Event4 | Resource1 | 2019-05-26 06:03:12.3245614

Applying the 'greater than 6:00AM' AND 'Previous row < 06:00AM' criteria, the result should look like:

1. Event2 | Resource1 | 2019-05-26 05:51:23.2356984
2. Event3 | Resource1 | 2019-05-26 06:01:32.1033333
3. Event4 | Resource1 | 2019-05-26 06:03:12.3245614

Solution

  • Considering we have very little to go on here, the best I can offer is Pseudo-SQL.

    Anyway, seems like what you want is a CTE and LEAD:

    WITH CTE AS(
    SELECT {Your Columns},
           LEAD({Your Date & Time Column}) OVER (/*PARTITION BY {Some Column(s)}*/ ORDER BY {Your Date & Time Column} AS NextTime --Uncomment/Remove PARTITION BY Clause as needed
    FROM {Your TABLE} YT)
SELECT {Your Columns}
FROM CTE
WHERE NextTime >= '2019-05-26T06:00:00';

    You'll need to replace the appropriate parts in the Braces ({}).