CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-servicesssrs-2008
Jared Christensen
Jared Christensen

Reputation: 31

Group based on time difference between two date values

I've searched around, but haven't been able to find anyone else with this same question.

I'm working with SQL Server (2008 R2).

Let's say I have the following three rows of data coming back from my query. What I need to do is group the first two rows into one (in either SQL Server or SSRS) based on the difference in minutes between the Start Time and the End Time (the Duration). How much time elapses between one row's End Time and the next row's Start Time is of no concern; I'm only looking at Duration.

Current result set:

+---------+------------+------------+----------+
| Vehicle | Start Time | End Time   | Duration |
+---------+------------+------------+----------+
| 12      | 1:56:30 AM | 2:07:47 AM | 11       |
+---------+------------+------------+----------+
| 12      | 2:07:57 AM | 6:46:08 AM | 279      |
+---------+------------+------------+----------+
| 19      | 2:55:02 PM | 3:45:59 PM | 53       |
+---------+------------+------------+----------+

Desired result set:

+---------+------------+------------+----------+
| Vehicle | Start Time | End Time   | Duration |
+---------+------------+------------+----------+
| 12      | 1:56:30 AM | 6:46:08 AM | 290      |
+---------+------------+------------+----------+
| 19      | 2:55:02 PM | 3:45:59 PM | 53       |
+---------+------------+------------+----------+

I feel like it has to be a matter of grouping, but I'm not sure how to group based on whether or not the start and end times are less than 15 minutes apart.

How can this be accomplished?

Upvotes: 0

Views: 1431

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

Unless I misunderstood your question, try this

Select Vehicle
      ,StartTime = min(StartTime)
      .EndTime   = max(EndTime)
      ,Duration  = sum(Duration)
From YourTable
Group By Vehicle

Upvotes: 1

Related Questions