Solved

T-SQL Calculate minutes between rows

Posted on 2010-09-09
30
417 Views
Last Modified: 2012-06-27
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))

Open in new window

0
Comment
Question by:Sheritlw
  • 11
  • 9
  • 8
  • +2
30 Comments
 
LVL 41

Expert Comment

by:ralmada
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

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
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

by:dwkor
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

by:Sheritlw
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

by:ralmada
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

by:mehdi_javan
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)

Open in new window

0
 

Author Comment

by:Sheritlw
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

by:mehdi_javan
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

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

Don't need to consider primaryid.

Thanks
0
 
LVL 41

Expert Comment

by:ralmada
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

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
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

Open in new window

0
 
LVL 3

Expert Comment

by:mehdi_javan
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)

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
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

Open in new window

0
 

Author Comment

by:Sheritlw
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)

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
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

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 3

Expert Comment

by:mehdi_javan
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))

Open in new window

0
 

Author Comment

by:Sheritlw
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

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

Author Comment

by:Sheritlw
ID: 33640807
the sql is...

Thanks

Hi ralmada:

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

Open in new window

0
 
LVL 3

Expert Comment

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

Expert Comment

by:ralmada
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

Open in new window

0
 

Author Comment

by:Sheritlw
ID: 33640933
How would you like the data prepared?
0
 
LVL 3

Expert Comment

by:mehdi_javan
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

by:ralmada
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

by:Sheritlw
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

ralmada:

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

by:Sheritlw
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))

Open in new window

0
 
LVL 3

Accepted Solution

by:
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))

Open in new window

0
 

Author Comment

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

Sheri
0
 

Author Closing Comment

by:Sheritlw
ID: 33641509
Really helped me a lot.
Thank you
0
 
LVL 3

Expert Comment

by:mehdi_javan
ID: 33641541
You're welcome.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now