Create sql Key based on datetime that is persistent overnight-CodePudding

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?

CodePudding user response：

using APPLY to get the Start event datetime and form the key with concat_ws

select *
from   time_series t
       cross apply
       (
          select top 1 
                 ExpectedKey = concat_ws('-', 
                                         CarId, 
                                         convert(varchar(10), EventDateTime, 112), 
                                         datepart(hour, EventDateTime))
          from   time_series x
          where  x.Event = 'Start'
          and    x.EventDateTime <= t.EventDateTime
          order by x.EventDateTime desc
       ) k

CodePudding user response：

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;

db<>fiddle