Search code examples
sqlsql-serversql-server-2016window-functions

Selecting a value from next row into the current row

I've been trying to write a query in SQL Server which would help me get the next action time of the user as the action end of the previous record. How could i query to get the next action end date, and last record of the query will not have another record then i am trying to get the sysdate

SELECT
  UL.UL_US_ID,
  UL_ACTION_TIME
FROM USER_LOG UL
JOIN USER_MAIN UM
  ON UL.UL_US_ID = UM.US_ID
WHERE UL.UL_ACTION IN ('LI')
AND UM.US_UT_ID = '1'
AND CONVERT(varchar(10), UL.UL_ACTION_TIME, 111) = CONVERT(varchar(10), GETDATE(), 111)
ORDER BY ul.UL_US_ID, ul.UL_ACTION_TIME;

***Existing Data Sample***
+----------+-----------------+
|   user   |   acton_time    |
+----------+-----------------+
| hudhaifa | 8/27/2019 9:03  |
| hudhaifa | 8/27/2019 9:40  |
| hudhaifa | 8/27/2019 9:51  |
| hudhaifa | 8/27/2019 10:41 |
| hudhaifa | 8/27/2019 10:56 |
+----------+-----------------+

***Required Data Sample***
+----------+-----------------+-----------------+
|   user   |   acton_time    |   action_end    |
+----------+-----------------+-----------------+
| hudhaifa | 8/27/2019 9:03  | 8/27/2019 9:40  |
| hudhaifa | 8/27/2019 9:40  | 8/27/2019 9:51  |
| hudhaifa | 8/27/2019 9:51  | 8/27/2019 10:41 |
| hudhaifa | 8/27/2019 10:41 | 8/27/2019 10:56 |
| hudhaifa | 8/27/2019 10:56 | sysdate         |
+----------+-----------------+-----------------+

enter image description here

Solution

  • Assuming SQL server (or any RDBMS that supports analytic functions) just use the LEAD function:

    SELECT user
     , action_time
     , LEAD(action_time, 1, CURRENT_TIMESTAMP) OVER (PARTITION BY user ORDER BY action_time ASC) AS action_end
FROM t