dwonisch dwonisch - 5 months ago 6
SQL Question

Finding start and end date for events in one table

I have a single table where events are stored with start and end dates in different rows. Now I want to query all corresponding pairs of Start and End dates to create a report.

CREATE TABLE EVENTS (
Id INT IDENTITY(1,1) PRIMARY KEY,
DateEntry datetime,
IsStart bit,
EventType varchar(12)
)

INSERT INTO EVENTS
VALUES
('2016-06-16', 1, 'EVENT A'),
('2016-06-16', 1, 'EVENT B'),
('2016-06-17', 0, 'EVENT A'),
('2016-06-18', 1, 'EVENT A'),
('2016-06-19', 0, 'EVENT A'),
('2016-06-20', 0, 'EVENT B'),
('2016-06-21', 1, 'EVENT C')


SELECT EventType, DateEntry as StartDate, EndDate = LEAD(DateEntry, 1) OVER ( PARTITION BY EventType ORDER BY DateEntry)
FROM Events
ORDER BY EventType, DateEntry


This query returns:

| EventType | StartDate | EndDate |
=======================================
| EVENT A | 2016-06-16 | 2016-06-17 |
| EVENT A | 2016-06-17 | 2016-06-18 |
| EVENT A | 2016-06-18 | 2016-06-19 |
| EVENT A | 2016-06-19 | NULL |
| EVENT B | 2016-06-16 | 2016-06-20 |
| EVENT B | 2016-06-20 | NULL |
| EVENT C | 2016-06-21 | NULL |


I am expecting this result:

| EventType | StartDate | EndDate |
=======================================
| EVENT A | 2016-06-16 | 2016-06-17 |
| EVENT A | 2016-06-18 | 2016-06-19 |
| EVENT B | 2016-06-16 | 2016-06-20 |
| EVENT C | 2016-06-21 | NULL |


I need to add a filter to only look for start dates and apply the LEAD function on it, but if I add a WHERE-clause it will completely ignore all EndDates.

With an inline query I am able to get the right result, but I'm not sure if this would be the best solution

SELECT * FROM (
SELECT EventType, IsStart, DateEntry as StartDate, EndDate = LAG(DateEntry, 1) OVER ( PARTITION BY EventType ORDER BY DateEntry DESC)
FROM Events) a
WHERE IsStart = 1

Answer
SELECT *
FROM
    #EVENTS s
    OUTER APPLY (
       SELECT TOP 1 DateEntry AS EndDateEntry
       FROM
          #EVENTS i
       WHERE
          i.EventType = s.EventType
          AND i.IsStart = 0
          AND i.DateEntry > s.DateEntry
       ORDER BY
          i.DateEntry ASC
    ) e
WHERE
    s.IsStart = 1
Comments