SQL Server DATEDIFF Computed Column Between Rows

Question

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

on the fly? How do you mean? Also, sorry for delay, I'm working through the answers... — Jamsandwich, Sep 16 '16 at 10:53

gofr1 · Accepted Answer · 2016-09-16T11:22:45.290

5

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

edited Sep 16 '16 at 11:22

answered Sep 16 '16 at 10:47

gofr1

15,741
11
42
52

Hey @gofr1, This is a very clear answer (and works!). But could you explain why it is better to create the view? Is it more efficient than a UDF and calculated column? – Jamsandwich Sep 16 '16 at 11:54
2

@swe Well, it is not that new :) Get some time to read articles and blogs like ([SQL authority](http://blog.sqlauthority.com/), [SQLServerCentral](http://www.sqlservercentral.com/), etc) and reading answers/questions here on SO as well! – gofr1 Sep 16 '16 at 12:19
Thanks for the answer @gofr1. I'll have start thinking more about using views rather than adding computed columns. It's certainly easier to write at least. – Jamsandwich Sep 16 '16 at 12:30
@Jamsandwich Sorry for delay in comments. From my experience using UDF in computed columns (or even inside the views) may cause great performance issues. But if you table will not grow dramatically and insert/update rate will not be that big - you can use UDF without any problems. – gofr1 Sep 16 '16 at 12:38
@gofr1 No bother. My tables are small for now, but they'll likely grow as time goes on. I'm inserting/updating every 10 mins for each table. Don't know how that compares to the norm, but I'll go with views just to be on the safe side. It also sounds like a useful way to think about the operations, especially since it appears to be easier to code! – Jamsandwich Sep 16 '16 at 12:42

score 3 · Answer 2 · edited Sep 16 '16 at 12:17

3

How about:

CREATE FUNCTION GetTimeDiff (@time datetime)
RETURNS INT 
AS BEGIN
    declare @t1 datetime 

    DECLARE @Ret int

    SELECT @t1 = RecordTime 
    FROM (
        SELECT  RecordTime, 
                ROW_NUMBER() OVER (ORDER BY RecordTime DESC) AS rn
        FROM Table1
        WHERE RecordTime < @time
    ) i
    WHERE i.rn = 1

    SELECT @Ret = DATEDIFF(second, @t1, @time)
    RETURN @Ret
END
GO

ALTER TABLE Table1
ADD TimeDiff AS dbo.GetTimeDiff(RecordTime) 
GO

You need to add logic to the function to handle NULL @t1 etc.

Update to make it work with the next row:

 ROW_NUMBER() OVER (ORDER BY RecordTime ASC) AS rn
        FROM Table1
        WHERE RecordTime > @time

And this to make all values positive rather than negative:

SELECT @Ret = ABS(DATEDIFF(second, @t1, @time))

edited Sep 16 '16 at 12:17

Jamsandwich

634
1
5
25

answered Sep 16 '16 at 10:29

artm

8,554
3
26
43

I get a couple of errors with this. On line 12 - "Select statements within function cannot return data to a client" & "Last statement in a function must be a return statement". – Jamsandwich Sep 16 '16 at 10:56
Hey artm. This function now works. But the TimeDiff appears offset by 1 row. So the first row shows nulls and then it's calculating difference from previous row. Is it possible to calculate difference from next row? – Jamsandwich Sep 16 '16 at 11:53
Awesome. That works. Was getting negative values until until I added ABS function to the final Select. What's your opinion on using this over a view as suggested by Gofr1? – Jamsandwich Sep 16 '16 at 12:16
@Jamsandwich I was just trying to answer the computed column part of the question but I think Gofr1 's answer (+1) is a better option. – artm Sep 16 '16 at 12:23
OK, great. I'll go with the view then. +1 though, for showing me how this can be done as a computed column. It's great to see an example of a UDF that I can relate my own data to. – Jamsandwich Sep 16 '16 at 12:27
@Jamsandwich I can relate to that, no worries, happy to help. – artm Sep 16 '16 at 12:30

SQL Server DATEDIFF Computed Column Between Rows

2 Answers2

Linked