Calculate the difference in alternate columns in time format?

Question

Status  Date    Time
1   2016-03-16  8:00:00
0   2016-03-16  12:00:00
1   2016-03-16  16:00:00
0   2016-03-16  20:00:00
1   2016-03-16  23:55:00
0   2016-03-17  01:16:00
1   2016-03-17  02:20:00
0   2016-03-17  04:00:00
1   2016-03-17  08:00:00
0   2016-03-17  12:00:00
1   2016-03-17  04:00:00
0   2016-03-17  06:00:00

I am having above mentioned table.All I want to do is I want calculate the difference between the time where status is changing Eg In first two columns it should return time diff of 4 hrs Then i dont want to calculate the difference between 2nd and 3rd column i want to calculate the difference between 3rd and 4th column and so on....... The time difference should be in time format like 4 hrs or 58 minutes like that overall when there is change in status from 1 to 0 I will calculate the difference Please help.

Are you absolutely sure that there is always an alternating row? No rows will ever be missing (due to some kind of error)? — Shnugo, Mar 16 '16 at 08:09
SQL Server or Access? They are *very* different. In SQL Server you could use the [LAG](https://msdn.microsoft.com/en-us/library/hh231256.aspx) function — Panagiotis Kanavos, Mar 16 '16 at 08:09
In sql server it's fairly easy, even easier in 2012 version or higher. I didn't work with ms-access in the last 6-7 years so I have no clue about it. — Zohar Peled, Mar 16 '16 at 08:13
Also, you have 2 conflicting rows, where the status in one is 0 and in the other it's 1, on 2016-03-17 04:00:00. — Zohar Peled, Mar 16 '16 at 08:21
What database? What version? In SQL Server 2012+ `lag(time,1,NULL) over(order by date,time)` will return the previous status time — Panagiotis Kanavos, Mar 16 '16 at 08:23
@PanagiotisKanavos it can be either ms access also can be SQL — Lalit, Mar 16 '16 at 08:26
@Lalit no it can't. Different databases, different answers. Either you are targeting one or the other. Unless this is a school question, which is *out-of-scope* for SO. Such questions are quickly closed — Panagiotis Kanavos, Mar 16 '16 at 08:27
Edited tags based on [this comment](http://stackoverflow.com/questions/36029917/calculate-the-difference-in-alternate-columns-in-time-format#comment59710347_36030015) — Zohar Peled, Mar 16 '16 at 08:32

Shnugo · Answer 1 · 2016-03-16T08:30:33.997

Try it like this:

EDIT: The combination of DATE and TIME to a DATETIME is changed to an easier way

DECLARE @tbl TABLE([Status] INT,  [Date] DATE,    [Time] TIME);
INSERT INTO @tbl VALUES
 (1,'2016-03-16','8:00:00')
,(0,'2016-03-16','12:00:00')
,(1,'2016-03-16','16:00:00')
,(0,'2016-03-16','20:00:00')
,(1,'2016-03-16','23:55:00')
,(0,'2016-03-17','01:16:00')
,(1,'2016-03-17','02:20:00')
,(0,'2016-03-17','04:00:00')
,(1,'2016-03-17','08:00:00')
,(0,'2016-03-17','12:00:00')
,(1,'2016-03-17','04:00:00')
,(0,'2016-03-17','06:00:00');

WITH AllStarts AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Date],[Time]) AS RowInx
          ,CAST([date] AS DATETIME)+CAST([time] AS DATETIME) AS TimePoint
    FROM @tbl
    WHERE [Status]=1
)
,AllEnds AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Date],[Time]) AS RowInx
          ,CAST([date] AS DATETIME)+CAST([time] AS DATETIME) AS TimePoint
    FROM @tbl
    WHERE [Status]=0
)
SELECT AllStarts.RowInx
      ,AllStarts.TimePoint AS StartPoint
      ,AllEnds.TimePoint AS [EndPoint]
      ,CAST(AllEnds.TimePoint - AllStarts.TimePoint AS TIME) AS TimeDiff
FROM AllStarts
INNER JOIN AllEnds ON AllStarts.RowInx=AllEnds.RowInx

The result:

1   2016-03-16 08:00:00.000    2016-03-16 12:00:00.000     04:00:00.0000000
2   2016-03-16 16:00:00.000    2016-03-16 20:00:00.000     04:00:00.0000000
3   2016-03-16 23:55:00.000    2016-03-17 01:16:00.000     01:21:00.0000000
4   2016-03-17 02:20:00.000    2016-03-17 04:00:00.000     01:40:00.0000000
5   2016-03-17 04:00:00.000    2016-03-17 06:00:00.000     02:00:00.0000000
6   2016-03-17 08:00:00.000    2016-03-17 12:00:00.000     04:00:00.0000000

This is just a test scenario... You must only replace the table's name `@tbl` with your actual table... — Shnugo, Mar 16 '16 at 08:29
@Shnugo 1) you could use `cast(@Date as datetime) + cast(@Time as datetime)` as [shown here](http://stackoverflow.com/questions/7289753/combining-date-and-time-fields-to-datetime-sql-server-2008). 2) using `LAG` would allow you to retrieve the previous value (and diff) in a single CTE, then filter by Status 0 to get the end rows only — Panagiotis Kanavos, Mar 16 '16 at 08:31
@PanagiotisKanavos The `cast` approach has been changed before you wrote this :-) I had found the same link :-) and about `LAG`: Yes, I thought about it, but - as the OP obviosly is not really sure where this is running, I wanted to keep in "lower" areas ... — Shnugo, Mar 16 '16 at 08:34

Zohar Peled · Answer 2 · 2016-03-19T18:02:20.300

0

An easy solution for sql server 2012+, but it would probably not work with ms-access 2007.

;WITH cte as
(
SELECT  [Status], 
        [Date], 
        [Time], 
        CAST([Date] as datetime) + CAST([Time] As datetime) As [datetime]
FROM MyTable 
WHERE [Date] >= @StartDate
AND [Date] <= @EndDate
)

SELECT  [Status], 
        [Date], 
        [Time], 
        CASE WHEN [Date] = LAG([Date]) OVER (ORDER BY [datetime]) THEN
            CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, LAG([datetime]) OVER (ORDER BY [datetime]), [datetime]), '00:00:00') as time) 
        ELSE 
            NULL
        END
        As TimeDifference
FROM cte

Results (based on the provided data)

Status Date       Time             TimeDifference
------ ---------- ---------------- ----------------
1      2016-03-16 08:00:00.0000000 NULL
0      2016-03-16 12:00:00.0000000 04:00:00.0000000
1      2016-03-16 16:00:00.0000000 04:00:00.0000000
0      2016-03-16 20:00:00.0000000 04:00:00.0000000
1      2016-03-16 23:55:00.0000000 03:55:00.0000000
0      2016-03-17 01:16:00.0000000 01:21:00.0000000
1      2016-03-17 02:20:00.0000000 01:04:00.0000000
0      2016-03-17 04:00:00.0000000 01:40:00.0000000
1      2016-03-17 04:00:00.0000000 00:00:00.0000000
0      2016-03-17 06:00:00.0000000 02:00:00.0000000
1      2016-03-17 08:00:00.0000000 02:00:00.0000000
0      2016-03-17 12:00:00.0000000 04:00:00.0000000

edited Mar 19 '16 at 18:02

answered Mar 16 '16 at 08:36

Zohar Peled

79,642
10
69
121

please let me check i dont have SQL 2012 Will check and update on your answer Thanxin advance – Lalit Mar 16 '16 at 14:23
But i want to exexte this CTE from vb.net form and there will be the start date and end date how to execute the same – Lalit Mar 17 '16 at 04:26
Dear @Zohar 0 2016-03-17 01:16:00.0000000 01:21:00.0000000 Idont want to display this record how to go about it – Lalit Mar 17 '16 at 04:43
I've edited my answer to show how you can use variables in the where clause of the cte to select a specific date range of the table. what's special in the record you don't want to show? – Zohar Peled Mar 17 '16 at 05:37
Thanks Zohar But i Ma having a query i dont want to calculate the difference when day changes ie last record of 2016-03-16 and first Record of 2016-03-17 i e 1 2016-03-16 23:55:00.0000000 03:55:00.0000000 0 2016-03-17 01:16:00.0000000 01:21:00.0000000 Please update – Lalit Mar 18 '16 at 16:42
getting an error for As keyword in edited query.... Msg 156, Level 15, State 1, Line 16 Incorrect syntax near the keyword 'As'. please update – Lalit Mar 19 '16 at 12:04
There was a missing `end`. Fixed it. Try now. – Zohar Peled Mar 19 '16 at 18:03
Glad to help. Please accept the answer so that people will know that the problem is solved. – Zohar Peled Mar 20 '16 at 05:47

Calculate the difference in alternate columns in time format?

2 Answers2