I have a time series with a table like this
CarId
EventDateTime
Event
SessionFlag
| CarId | EventDateTime | Event | SessionFlag | ExpectedKey |
|---|---|---|---|---|
| 1 | 2022-01-01 7:00 | Start | 1 | 1-20220101-7 |
| 1 | 2022-01-01 7:05 | Drive | 1 | 1-20220101-7 |
| 1 | 2022-01-01 8:00 | Park | 1 | 1-20220101-7 |
| 1 | 2022-01-01 10:00 | Drive | 1 | 1-20220101-7 |
| 1 | 2022-01-01 18:05 | End | 0 | 1-20220101-7 |
| 1 | 2022-01-01 23:00 | Start | 1 | 1-20220101-23 |
| 1 | 2022-01-01 23:05 | Drive | 1 | 1-20220101-23 |
| 1 | 2022-01-02 2:00 | Park | 1 | 1-20220101-23 |
| 1 | 2022-01-02 3:00 | Drive | 1 | 1-20220101-23 |
| 1 | 2022-01-02 15:00 | End | 0 | 1-20220101-23 |
| 1 | 2022-01-02 16:00 | Start | 1 | 1-20220102-16 |
Other CarIds do exist. What I am attempting to do is create the last column, ExpectedKey.
The problem I face though is midnight, as the same session can exist over two days. The record above with ExpectedKey 1-20220101-23 is the prime example of what I'm trying to achieve.
I've played with using:
CASE
WHEN SessionFlag<> 0
AND
SessionFlag= LAG(SessionFlag) OVER (PARTITION BY Carid ORDER BY EventDateTime)
THEN FIRST_VALUE(CarId+'-'+Convert(CHAR(8),EventDateTime,112)+'-'+CAST(DATEPART(HOUR,EventDateTime)AS
VARCHAR))OVER (PARTITION BY CarId ORDER BY EventDateTime)
ELSE CarId+'-'+Convert(CHAR(8),EventDateTime,112)+'-'+CAST(DATEPART(HOUR,EventDateTime)AS VARCHAR) END AS SessionId
But can't seem to make it partition correctly overnight.
Can anyone off advice?
This is a classic gaps-and-islands problem. There are a number of solutions.
The simplest (if not that efficient) is partitioning over a windowed conditional count
WITH Groups AS (
SELECT *,
GroupId = COUNT(CASE WHEN t.Event = 'Start' THEN 1 END)
OVER (PARTITION BY t.CarId ORDER BY t.EventDateTime)
FROM YourTable t
)
SELECT *,
NewKey = CONCAT_WS('-',
t.CarId,
CONVERT(varchar(8), EventDateTime, 112),
FIRST_VALUE(DATEPART(hour, t.EventDateTime))
OVER (PARTITION BY t.CarId, t.GroupId ORDER BY t.EventDateTime
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
FROM Groups t;