CODEWITHSUNDEEP
sqldategroup-by
hbulens
hbulens

Reputation: 1969

SQL Grouping consecutive rows without date gaps

I have the following problem: in the following query I have a list of meetings with start and end time stamps. I need to know which meetings were consecutives without any gaps and I need to group those results.

This is the raw data set:

DATE                LOCATION   START END
2015-04-09 00:00:00 6          1100 1200
2015-04-09 00:00:00 6          1000 1100
2015-04-09 00:00:00 6          1200 1300
2015-04-09 00:00:00 6          1300 1400
2015-04-09 00:00:00 6          1500 1600
2015-04-09 00:00:00 6          1600 1700

In this data set, the following records are considered to be consecutive without time gaps:

DATE                LOCATION   START END
-- CONSECUTIVE MEETINGS GROUP 1  
2015-04-09 00:00:00 6          1000 1100
2015-04-09 00:00:00 6          1100 1200
2015-04-09 00:00:00 6          1200 1300
2015-04-09 00:00:00 6          1300 1400

-- CONSECUTIVE MEETINGS GROUP 2
2015-04-09 00:00:00 6          1500 1600
2015-04-09 00:00:00 6          1600 1700

This is what I want to achieve:

DATE                LOCATION   COUNT     
2015-04-09 00:00:00 6          2

At the moment I'm not able to join more than two consecutive meetings. I can join meetings from 11:00 - 12:00 to 12:00 - 13:00 but I cannot go further than that in my SQL statements.

Can anybody help me out?

Upvotes: 1

Views: 921

Answers (1)

Cristian Lupascu
Cristian Lupascu

Reputation: 40576

Here's a query showing the meetings that do not have a preceding meeting: 

select *
from Meetings m_after
left join Meetings m_before
  on m_before.end = m_after.start
  and m_before.date = m_after.date
  and m_before.location = m_after.location
where m_before.location is null;

These are essentially the start meetings of the groups you want to count.

Therefore, let's count them, grouping by date and location:

select 
  m_after.date,
  m_after.location,
  count(*) as Count
from Meetings m_after
left join Meetings m_before
  on m_before.end = m_after.start
  and m_before.date = m_after.date
  and m_before.location = m_after.location
where m_before.location is null
group by m_after.date, m_after.location;

Here's an SQLFiddle: http://www.sqlfiddle.com/#!9/79676/8. It is done in MySQL, but it should work on any platform, as this is just standard SQL.

Upvotes: 1

Related Questions