Solved

# T-SQL Calculate minutes between rows

Posted on 2010-09-09
417 Views
Hi EE,

I need to know how to display the difference in minutes between 2 columns, 1 row apart.
My Table look like the following...

PrimaryID (int)  SchedDate (smalldatetime)  SchedStart (smalldatetime) SchedEnd (smalldatetime)
1                      9/11/2010                               9/11/2010 9:00 am                9/11/2010 10:00 am
2                      9/11/2010                               9/11/2010 10:15 am                9/11/2010 11:00 am
3                      9/11/2010                               9/11/2010 1:30 pm                9/11/2010 2:00 pm
4                      9/11/2010                               9/11/2010 2:00 pm                9/11/2010 2:30 pm
5                      9/11/2010                               9/11/2010 3:00 pm                9/11/2010 4:00 pm

I  need to find the difference in minutes between Row 2 (SchedStart) and Row 1 (SchedEnd)

My result set should be

1
2    15 min
3     150 min
4    0 min
5    30 min

I have tried several examples over the net, but can't get the correct values.  My latest attempt is below.

Thank you
``````SELECT a.SchedDate, DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) AS AvailTime

FROM  dbo.Schedules AS a INNER JOIN

dbo.Schedules AS b ON a.SchedID = b.SchedID

WHERE (a.SchedDate = CONVERT(DATETIME, '2010-09-11 00:00:00', 102))
``````
0
Question by:Sheritlw
• 11
• 9
• 8
• +2

LVL 41

Expert Comment

ID: 33638971
try
``````select a.PrimaryID, datediff(n, a.SchedEnd, b.SchedStart) as mtes

from schedules a

left join schedules b on a.PrimaryID = b.PrimaryID - 1
``````
0

LVL 92

Expert Comment

ID: 33639006
1) Is there ever ANY possibility of overlapping intervals?  Such as...

PrimaryID (int)  SchedDate (smalldatetime)  SchedStart (smalldatetime) SchedEnd (smalldatetime)
998                   12/30/2010                              12/30/2010 9:00 am               12/30/2010 11:00 am
999                   12/30/2010                              12/30/2010 10:00 am               12/30/2010 12:00 pm

2) Assuming overlap is impossible, can we ALWAYS assume that an interval with a lower ID value also always occurs earlier in time than any other interval with a greater ID value?
0

LVL 13

Expert Comment

ID: 33639020
If you don't have any holes in the sequence of ScheduleId, you can use something like below:

SELECT a.SchedDate, DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) AS AvailTime
FROM  dbo.Schedules AS a INNER JOIN
dbo.Schedules AS b ON a.SchedID + 1 = b.SchedID
WHERE (a.SchedDate = CONVERT(DATETIME, '2010-09-11 00:00:00', 102))

Otherwise, if you're on SQL 2008/2005, you can use CTE implementation:

;with CTE(SchedDate, SchedStartDateTime, SchedEndDateTime, RowNum)
as
(
select SchedDate, SchedStartDateTime, SchedEndDateTime, ROW_NUMBER() over (order by SchedId)
from dbo.Schedules
)
select a.SchedDate, DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) AS AvailTime
FROM  CTE AS a INNER JOIN
CTE AS b ON a.RowNum + 1 = b.RowNum
WHERE (a.SchedDate = CONVERT(DATETIME, '2010-09-11 00:00:00', 102))
0

Author Comment

ID: 33639332
Unfortunately gaps and overlaps are possible.
Someone may delete an appointment, or double-book.
I would like to avoid using a cursor to walk through and deposit differences in a temporary table, if at all possible.
Thanks

0

LVL 41

Expert Comment

ID: 33639506
I guess you should provide some examples of what the overlaps and the double book looks like and what is the expected result in those cases.
0

LVL 3

Expert Comment

ID: 33639565
What do you expect when there is an overlap? Not considering overlaps, one answer is the following sql:
``````SELECT a.SchedDate, DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) AS AvailTime
FROM dbo.Schedules AS a, dbo.Schedules AS b
WHERE a.PrimaryID = (Select Top 1 PrimaryID from dbo.Schedules c
where b.SchedStartDateTime > c.SchedEndDateTime
order by c.SchedEndDateTime desc)
``````
0

Author Comment

ID: 33639898
An overlap or gap would look something like...

PrimaryID (int)  SchedDate (smalldatetime)  SchedStart (smalldatetime) SchedEnd (smalldatetime)
1                      9/11/2010                               9/11/2010 9:00 am                9/11/2010 10:00 am
3                      9/11/2010                               9/11/2010 9:30 am                9/11/2010 11:00 am
4                      9/11/2010                               9/11/2010 1:30 pm                9/11/2010 2:00 pm
5                      9/11/2010                               9/11/2010 2:00 pm                9/11/2010 2:30 pm
7                      9/11/2010                               9/11/2010 2:15 pm                9/11/2010 4:00 pm

Any overlap would just return 0 or a negative number.
I will be filtering by the SchedDate where @datetocheck = SchedDate
I imagine I would need to sort by the endtime (SchedEnd) and compare that way.

Thanks
0

LVL 3

Expert Comment

ID: 33640001
So, for your example rows, the result is:

1  null
2  0 min
3  150 min
4  0 min
5  0 min

Is it right? Do you need to consider PrimaryID and sort data by it before extract differences? or you must sort your data by one of SchedEnd and SchedStart columns?
0

Author Comment

ID: 33640027
Correct, but if null, I will return 0.

Don't need to consider primaryid.

Thanks
0

LVL 41

Expert Comment

ID: 33640065
I would update the solution from dwork then

``````;with CTE

as

(

select SchedID, SchedDate, SchedStartDateTime, SchedEndDateTime, ROW_NUMBER() over (order by SchedId)

from dbo.Schedules

)

select a.SchedID, case when DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) < 0 then 0 else

DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) end AS AvailTime

FROM  CTE AS a INNER JOIN

CTE AS b ON a.RowNum + 1 = b.RowNum
``````
0

LVL 41

Expert Comment

ID: 33640094
typos there:

``````;with CTE

as

(

select SchedID, SchedDate, SchedStartDateTime, SchedEndDateTime, ROW_NUMBER() over (order by SchedId) RowNum

from dbo.Schedules

)

select a.SchedID, case when DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) < 0 then 0 else

DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) end AS AvailTime

FROM  CTE AS a INNER JOIN

CTE AS b ON a.RowNum + 1 = b.RowNum
``````
0

LVL 3

Expert Comment

ID: 33640117
This must work:
``````SELECT b.SchedDate, case when a.SchedEndDateTime is null or a.SchedEndDateTime >= b.SchedStartDateTime then 0 else DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) end AS AvailTime

FROM dbo.Schedules AS b left outer join dbo.Schedules AS a

on a.PrimaryID = (Select Top 1 PrimaryID from dbo.Schedules c

where b.SchedEndDateTime > c.SchedEndDateTime

order by c.SchedEndDateTime desc)
``````
0

LVL 41

Expert Comment

ID: 33640220
to handle the null, just do like this
``````;with CTE

as

(

select SchedID, SchedDate, SchedStart, SchedEnd, ROW_NUMBER() over (order by SchedId) RowNum

from dbo.Schedules

)

select a.SchedID, case when isnull(DATEDIFF(minute, a.SchedEnd, b.SchedStart), 0) <= 0 then 0 else

DATEDIFF(minute, a.SchedEnd, b.SchedStart) end AS AvailTime

FROM  CTE AS a INNER JOIN

CTE AS b ON a.RowNum + 1 = b.RowNum
``````
0

Author Comment

ID: 33640336
So far I have mehdi_javan is working for the first set of times, however I haven't been able to pull the correct values when I try to filter by a date after the first one.
For example - filtering by 9/11/2010 gives me the correct results, Filtering by 9/12/2010 gives me strange results.

Table is as follows
ID     SchedDate                          SchedStart                          SchedEnd
5      9/11/2010 12:00:00 AM        9/11/2010 9:00:00 AM        9/11/2010 10:00:00 AM
6      9/11/2010 12:00:00 AM        9/11/2010 10:30:00 AM        9/11/2010 11:00:00 AM
7      9/11/2010 12:00:00 AM        9/11/2010 1:15:00 PM        9/11/2010 2:00:00 PM
8      9/11/2010 12:00:00 AM        9/11/2010 3:00:00 PM        9/11/2010 3:30:00 PM
9      9/12/2010 12:00:00 AM        9/12/2010 9:00:00 AM        9/11/2010 10:00:00 AM
10      9/12/2010 12:00:00 AM        9/12/2010 10:30:00 AM   9/11/2010 11:00:00 AM
11      9/12/2010 12:00:00 AM        9/12/2010 1:15:00 PM        9/11/2010 2:00:00 PM
12      9/12/2010 12:00:00 AM        9/12/2010 3:00:00 PM        9/11/2010 3:30:00 PM

Thanks
``````SELECT b.SchedDate, CASE WHEN a.SchedEndDateTime IS NULL OR

a.SchedEndDateTime >= b.SchedStartDateTime THEN 0 ELSE DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) END AS AvailTime,

b.SchedEndDateTime

FROM  dbo.Schedules AS b LEFT OUTER JOIN

dbo.Schedules AS a ON a.SchedID =

(SELECT TOP (1) SchedID

FROM   dbo.Schedules AS c

WHERE (b.SchedEndDateTime > SchedEndDateTime)

ORDER BY SchedEndDateTime DESC)
``````
0

LVL 41

Expert Comment

ID: 33640534
Why are you saying that my suggestion is not working? Can you please post some samples of what the query returns and why is not correct?
The below should produce correct results

``````;with CTE

as

(

select SchedID, SchedDate, SchedStart, SchedEnd, ROW_NUMBER() over (order by SchedId) RowNum

from dbo.Schedules

)

select a.SchedID, case when isnull(DATEDIFF(minute, a.SchedEnd, b.SchedStart), 0) <= 0 then 0 else

DATEDIFF(minute, a.SchedEnd, b.SchedStart) end AS AvailTime

FROM  CTE AS a

LEFT JOIN CTE AS b ON a.RowNum = b.RowNum - 1
``````
0

LVL 3

Expert Comment

ID: 33640565
If you need to filter it, you must filter the inner select as well:
``````SELECT b.SchedDate, CASE WHEN a.SchedEndDateTime IS NULL OR

a.SchedEndDateTime >= b.SchedStartDateTime THEN 0 ELSE DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) END AS AvailTime,

b.SchedEndDateTime

FROM  dbo.Schedules AS b LEFT OUTER JOIN

dbo.Schedules AS a ON a.SchedID =

(SELECT TOP (1) SchedID

FROM   dbo.Schedules AS c

WHERE (b.SchedEndDateTime > SchedEndDateTime)

and (c.SchedDate = CONVERT(DATETIME, '2010-09-11 00:00:00', 102))

ORDER BY SchedEndDateTime DESC)

WHERE (b.SchedDate = CONVERT(DATETIME, '2010-09-11 00:00:00', 102))
``````
0

Author Comment

ID: 33640745
Yes, I understand about the filtering and that is what I did, but the result set ends up

SchedDate                          AvailTime
9/12/2010 12:00:00 AM          135              9/12/2010 2:00:00 PM
9/12/2010 12:00:00 AM          30              9/12/2010 11:00:00 AM
9/12/2010 12:00:00 AM         1050               9/12/2010 10:00:00 AM
9/12/2010 12:00:00 AM          0                       9/11/2010 3:30:00 PM
0

LVL 41

Expert Comment

ID: 33640770
and what can you say about my last post? http:#a33640534 ?
0

Author Comment

ID: 33640807
the sql is...

Thanks

I really don't understand your procedure and it looks like you are using the primary key to track it.  Problem with that is, a schedule for 9:00 am can be made after the one that is already scheduled at 10:00 am.
Thus the id for 10 schedule would be 1, and the id for the 9 schedule would be 2.
Please correct me if I'm wrong
``````SELECT TOP (100) PERCENT b.SchedDate, CASE WHEN a.SchedEndDateTime IS NULL OR

a.SchedEndDateTime >= b.SchedStartDateTime THEN 0 ELSE DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) END AS AvailTime,

b.SchedEndDateTime

FROM  dbo.Schedules AS b LEFT OUTER JOIN

dbo.Schedules AS a ON a.SchedID =

(SELECT TOP (1) SchedID

FROM   dbo.Schedules AS c

WHERE (b.SchedEndDateTime > SchedEndDateTime) AND (SchedDate = CONVERT(DATETIME, '2010-09-12 00:00:00', 102))

ORDER BY SchedEndDateTime DESC)

WHERE (b.SchedDate = CONVERT(DATETIME, '2010-09-12 00:00:00', 102))

ORDER BY b.SchedEndDateTime DESC
``````
0

LVL 3

Expert Comment

ID: 33640851
Could you please send me the raw data of '9/12/2010'?
0

LVL 41

Expert Comment

ID: 33640907
maybe you can use the SchedStart column then, instead of the SchedID

``````;with CTE

as

(

select SchedID, SchedDate, SchedStart, SchedEnd, ROW_NUMBER() over (order by SchedStart) RowNum

from dbo.Schedules

)

select a.SchedID, case when isnull(DATEDIFF(minute, a.SchedEnd, b.SchedStart), 0) <= 0 then 0 else

DATEDIFF(minute, a.SchedEnd, b.SchedStart) end AS AvailTime

FROM  CTE AS a

LEFT JOIN CTE AS b ON a.RowNum = b.RowNum - 1
``````
0

Author Comment

ID: 33640933
How would you like the data prepared?
0

LVL 3

Expert Comment

ID: 33641044
If the data is as follows:
ID     SchedDate                          SchedStart                          SchedEnd
5      9/11/2010 12:00:00 AM        9/11/2010 9:00:00 AM        9/11/2010 10:00:00 AM
6      9/11/2010 12:00:00 AM        9/11/2010 10:30:00 AM        9/11/2010 11:00:00 AM
7      9/11/2010 12:00:00 AM        9/11/2010 1:15:00 PM        9/11/2010 2:00:00 PM
8      9/11/2010 12:00:00 AM        9/11/2010 3:00:00 PM        9/11/2010 3:30:00 PM
9     9/12/2010 12:00:00 AM        9/12/2010 9:00:00 AM        9/11/2010 10:00:00 AM
10      9/12/2010 12:00:00 AM        9/12/2010 10:30:00 AM   9/11/2010 11:00:00 AM
11     9/12/2010 12:00:00 AM        9/12/2010 1:15:00 PM        9/11/2010 2:00:00 PM
12      9/12/2010 12:00:00 AM        9/12/2010 3:00:00 PM       9/11/2010 3:30:00 PM

Then the problem is that SchedEnd values are '9/11/2010' for SchedDate = '9/12/2010'. You need to fix your data. with such data, DATEDIFF doesn't work well.
0

LVL 41

Expert Comment

ID: 33641108
Sheritlw, could you please comment on my post http:#a33640907 ? Please try the query and let me know if you got the correct resutl or not. If NOT, please post an example of what is wrong.
0

Author Comment

ID: 33641248
I did notice the schedend data and did fix it with the same results
The result set was the same at
9/12/2010 12:00:00 AM      135      9/12/2010 2:00:00 PM
9/12/2010 12:00:00 AM      30      9/12/2010 11:00:00 AM
9/12/2010 12:00:00 AM      1050      9/12/2010 10:00:00 AM
9/12/2010 12:00:00 AM      0      9/11/2010 3:30:00 PM

When I ran your query my result set is
5      0
9      43200
10      0
6      151200
7      0
11      64800
12      0
8      0

Thanks
0

Author Comment

ID: 33641331
Here is another result set

9/11/2010 12:00:00 AM           0               9/11/2010 10:00:00 AM
9/11/2010 12:00:00 AM          30       9/11/2010 11:00:00 AM
9/11/2010 12:00:00 AM          195      9/11/2010 2:00:00 PM
9/11/2010 12:00:00 AM          300      9/11/2010 3:30:00 PM

using the sql below.
``````SELECT TOP (100) PERCENT b.SchedDate, CASE WHEN a.SchedEndDateTime IS NULL OR

a.SchedEndDateTime >= b.SchedStartDateTime THEN 0 ELSE DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) END AS AvailTime,

b.SchedEndDateTime

FROM  dbo.Schedules AS b LEFT OUTER JOIN

dbo.Schedules AS a ON a.SchedID =

(SELECT TOP (1) SchedID

FROM   dbo.Schedules AS c

WHERE (b.SchedEndDateTime > SchedEndDateTime) AND (SchedDate = CONVERT(DATETIME, '2010-09-11 00:00:00', 102))

ORDER BY SchedEndDateTime)

WHERE (b.SchedDate = CONVERT(DATETIME, '2010-09-11 00:00:00', 102))
``````
0

LVL 3

Accepted Solution

mehdi_javan earned 500 total points
ID: 33641446
Well, I created the table and added the data. Then I ran the query and I got the correct result.

This is the data:

SchedID      SchedDate      SchedStartDateTime      SchedEndDateTime
5      2010-09-11 00:00:00.000      2010-09-11 09:00:00.000      2010-09-11 10:00:00.000
6      2010-09-11 00:00:00.000      2010-09-11 10:30:00.000      2010-09-11 11:00:00.000
7      2010-09-11 00:00:00.000      2010-09-11 13:15:00.000      2010-09-11 14:00:00.000
8      2010-09-11 00:00:00.000      2010-09-11 15:00:00.000      2010-09-11 15:30:00.000
9      2010-09-12 00:00:00.000      2010-09-12 09:00:00.000      2010-09-12 10:00:00.000
10      2010-09-12 00:00:00.000      2010-09-12 10:30:00.000      2010-09-12 11:00:00.000
11      2010-09-12 00:00:00.000      2010-09-12 13:15:00.000      2010-09-12 14:00:00.000
12      2010-09-12 00:00:00.000      2010-09-12 15:00:00.000      2010-09-12 15:30:00.000

And this is the result of query:

SchedDate      AvailTime      SchedEndDateTime
2010-09-12 00:00:00.000      0      2010-09-12 10:00:00.000
2010-09-12 00:00:00.000      30      2010-09-12 11:00:00.000
2010-09-12 00:00:00.000      135      2010-09-12 14:00:00.000
2010-09-12 00:00:00.000      60      2010-09-12 15:30:00.000

I think you must have made a mistake. Please use my data and my exact query.
``````SELECT b.SchedDate, CASE WHEN a.SchedEndDateTime IS NULL OR
a.SchedEndDateTime >= b.SchedStartDateTime THEN 0 ELSE DATEDIFF(minute, a.SchedEndDateTime, b.SchedStartDateTime) END AS AvailTime,
b.SchedEndDateTime
FROM  dbo.Schedules AS b LEFT OUTER JOIN
dbo.Schedules AS a ON a.SchedID =
(SELECT TOP (1) SchedID
FROM   dbo.Schedules AS c
WHERE (b.SchedEndDateTime > SchedEndDateTime)
and (c.SchedDate = CONVERT(DATETIME, '2010-09-12 00:00:00', 102))
ORDER BY SchedEndDateTime DESC)
WHERE (b.SchedDate = CONVERT(DATETIME, '2010-09-12 00:00:00', 102))
``````
0

Author Comment

ID: 33641503
Yes that worked!
Not sure what I did wrong, but THANK YOU!

Sheri
0

Author Closing Comment

ID: 33641509
Really helped me a lot.
Thank you
0

LVL 3

Expert Comment

ID: 33641541
You're welcome.
0

## Featured Post

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.