-1

I need difference between rows of Table. I am getting result but first row coming null so due that the expected results is move in next row. How remove null values from first column of time difference.
Below is my query

SELECT 
    T1.[ID] 
    ,T1.[url_rec] 
    , '' As Diff
    ,record_timestamp
    ,CONVERT(VARCHAR(8),record_timestamp - lag(record_timestamp, 1) OVER (ORDER BY T1.[ID]),108)
FROM (
    SELECT 
        [ID] 
        ,[url_rec] 
        ,[record_timestamp]
        ,ROW_NUMBER() OVER (ORDER BY [ID]) AS 'RowNum'
        
    FROM raw_activity_log  T 
) T1 
LEFT JOIN (
    SELECT 
        [ID] 
        ,[url_rec] 
        ,ROW_NUMBER() OVER (ORDER BY [ID]) AS 'RowNum' 
    FROM raw_activity_log T 
) T2 
    ON T2.[RowNum] + 1 = T1.[RowNum] 
WHERE T1.[url_rec] <> ISNULL(T2.[url_rec], '') 
ORDER BY T1.[ID];
d URL Record_Time Difference
1 https://sabezyessent.teramind.co/#/welcome 2022-05-09 09:44:22.000 NULL
2 https://sabezyessent.teramind.co/#/report/4 2022-05-09 09:47:42.000 00:03:20
3 https://sabezyessent.teramind.co/#/clock 2022-05-09 09:47:37.000 23:59:55
4 https://sabezyessent.teramind.co/#/manage/users/profile/2 2022-05-09 09:47:45.000 00:00:08

Query are written above and result is showing. Why this null values coming I don't know. The values coming in second row that would be first row value. This way every row values showing in next row.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Rakesh Thakre
  • 40
  • 5
  • Are you really using SQL Server Compact Edition? It hasn't been supported for a *long* time, and supported version of SQL Server have access to `LEAD` and `LAG`; making tasks such as this trivial. – Thom A Sep 19 '22 at 13:38
  • @Larnu. Yes I am using Ms SQL Server 2019. Can you please explain LEAD & LAG in details. – Rakesh Thakre Sep 19 '22 at 13:42
  • If you are using SQL Server 2019 then you **aren't** using SQL Server Compact Edition... – Thom A Sep 19 '22 at 13:44
  • As for the functions: [`LEAD`](https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver16) / [`LAG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16) – Thom A Sep 19 '22 at 13:45
  • @Larnu please explain in details. – Rakesh Thakre Sep 19 '22 at 13:48
  • 1
    The reason you are getting `NULL` for the first row's value for `DIfference` is that there is no prior value. Thus the difference between the `Record_Time` and `NULL` is `NULL` – Thom A Sep 19 '22 at 13:56
  • What value do you expect for the `NULL`? There is no previous row os it's going to be null. Also, why the join with `ROW_NUMBER`, why not just use `LAG(URL)...`? – Charlieface Sep 19 '22 at 14:04

1 Answers1

-2

You can use CURSOR inorder to traverse the above query result as a dynamic query , this might help you compute the time difference between each record and you can update each record with the TIMSDIFFERENCE

follow the link for example : Using a cursor with dynamic SQL in a stored procedure

fahad
  • 154
  • 6
  • 1
    A cursor is probably the worst way to do this – Charlieface Sep 19 '22 at 14:05
  • yes it may cost performance but the task you are trying to achieve is not straight forward , you can do it in sub queries like you can use MERGE statement to compute the time difference you can write SSIS DB tasks for it or you can use SQL CURSOR to compute it. – fahad Sep 19 '22 at 14:11