CODEWITHSUNDEEP
sql-server
Gustavo
Gustavo

Reputation: 11

How can I consolidate Event-Based Rows in a SQL Query?

I have a simple SQL Table with the following Fields:

The data inside represents a continuous timeline broken in different types of events. Every [Event End] will match an [Event Start] field (like a chain).

My challenge here, is that some of the events are the same, see below for example:

Event Start        Event End          Event Description
1/1/2018 07:00:00  1/1/2018 07:30:00  Breakfast
1/1/2018 07:30:00  1/1/2018 07:40:00  Drive To Work
1/1/2018 07:40:00  1/1/2018 07:55:00  Drive To Work
1/1/2018 07:55:00  1/1/2018 12:00:00  Working

But I would like a SQL Query that can display a view as follows:

Event Start        Event End          Event Description
1/1/2018 07:00:00  1/1/2018 07:30:00  Breakfast
1/1/2018 07:30:00  1/1/2018 07:55:00  Drive To Work
1/1/2018 07:55:00  1/1/2018 12:00:00  Working

In my table I can have more than 2 events that need to be merged into a single row, in case you are wondering if we can only do a simple JOIN and fix the issue, probably recursive methods need to be applied but I do not know how. do you know how can the SQL query can be build?

Upvotes: 1

Views: 314

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

You could do this:

select
      "Event Description"
    , min("Event Start") as "Event Start"
    , max("Event End") as "Event End"
from that_table
group by
      "Event Description"

BUT relying on just a description is likely to be a substantial issue, for example misspellings or just accidental multiple spaces may cause differences your didn't expect - or repeated descriptions may see tasks "joined" that should not be joined.

Upvotes: 1

Related Questions