Jamsandwich Jamsandwich - 2 months ago 14
SQL Question

SQL Server DATEDIFF Computed Column Between Rows

I currently have a table similar to this -

RecordTime Running Fault
-----------------------------------------------
2016-09-15 11:32:01.00 0 202
2016-09-15 11:32:08.00 1 202
2016-09-15 11:39:31.00 0 21
2016-09-15 11:40:07.00 1 4
2016-09-15 11:42:11.00 0 21
2016-09-15 11:42:39.00 1 45


I then wanted to calculate the time difference between the RecordTimes for each record. For this I am using the following -

WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY RecordTime) AS rn
FROM dbo.Table1
)
SELECT DATEDIFF(second, mc.RecordTime, mp.RecordTime)
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1


Which returns -

(No column name)
----------------
7
443
36
124
28
303


What I actually want to do, though, is create a computed column in the original table that gives me these values. Is this possible?

I thought that I might be able to convert the query into a UDF and then reference that in the column, but I'm not very experienced with that kind of work.

---edit---

Final result should be -

RecordTime Running Fault Diff
--------------------------------------------------------
2016-09-15 11:32:01.00 0 202 7
2016-09-15 11:32:08.00 1 202 443
2016-09-15 11:39:31.00 0 21 36
2016-09-15 11:40:07.00 1 4 124
2016-09-15 11:42:11.00 0 21 28
2016-09-15 11:42:39.00 1 45 303

Answer

I recommend you to use a view for this purpose:

CREATE VIEW Table1_vw 
AS
WITH cte AS (
    SELECT  *, 
            ROW_NUMBER() OVER (ORDER BY RecordTime) AS rn
    FROM    dbo.Table1
)
SELECT  mc.RecordTime,
        mc.Running,
        mc.Fault,
        DATEDIFF(second, mc.RecordTime, mp.RecordTime) Diff
FROM cte mc
LEFT JOIN cte mp
ON mc.rn = mp.rn - 1

Since you are using SQL Server 2012 you can use LEAD function:

CREATE VIEW Table1_vw 
AS
SELECT  RecordTime,
        Running,
        Fault,
        DATEDIFF(second,RecordTime,LEAD(RecordTime,1,NULL) OVER (ORDER BY RecordTime ASC) ) as Diff
FROM Table1
GO
Comments