• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

T-SQL Calculate minutes between rows

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
Sheritlw
Asked:
Sheritlw
  • 11
  • 9
  • 8
  • +2
1 Solution
 
ralmadaCommented:
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
 
Patrick MatthewsCommented:
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
 
dwkorCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SheritlwAuthor Commented:
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
 
ralmadaCommented:
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
 
mehdi_javanCommented:
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
 
SheritlwAuthor Commented:
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
 
mehdi_javanCommented:
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
 
SheritlwAuthor Commented:
Correct, but if null, I will return 0.

Don't need to consider primaryid.

Thanks
0
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
mehdi_javanCommented:
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
 
ralmadaCommented:
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
 
SheritlwAuthor Commented:
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
 
ralmadaCommented:
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
 
mehdi_javanCommented:
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
 
SheritlwAuthor Commented:
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
 
ralmadaCommented:
and what can you say about my last post? http:#a33640534 ?
0
 
SheritlwAuthor Commented:
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
 
mehdi_javanCommented:
Could you please send me the raw data of '9/12/2010'?
0
 
ralmadaCommented:
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
 
SheritlwAuthor Commented:
How would you like the data prepared?
0
 
mehdi_javanCommented:
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
 
ralmadaCommented:
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
 
SheritlwAuthor Commented:
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
 
SheritlwAuthor Commented:
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
 
mehdi_javanCommented:
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
 
SheritlwAuthor Commented:
Yes that worked!
Not sure what I did wrong, but THANK YOU!

Sheri
0
 
SheritlwAuthor Commented:
Really helped me a lot.
Thank you
0
 
mehdi_javanCommented:
You're welcome.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 11
  • 9
  • 8
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now