How to calculate Arrival and Departure Date Times from a list of Date Times values stored in an SQL SERVER Table with optimized way

Question

I have a list of Date Times values stored in a SQL SERVER Table.

I have to calculate the Arrival Date time and Departure Date Time list:

Each arrival and departure occur in the same Day date
Departure date is the first record of a day, Arrival Date is the next record in the same day if exists, then the next record is an Arrival date

Here is an example of Data

set dateformat dmy
declare @mytable as table (MyEvent datetime)

insert into @mytable values
('01/01/2022 08:15'),
('01/01/2022 10:20'),
('01/01/2022 18:37'),
('02/01/2022 09:15'),
('02/01/2022 20:05'),
('02/01/2022 23:28'),
('02/01/2022 06:32'),
('04/01/2022 10:15'),
('05/01/2022 11:39');

The out put should be as follows

I have done the following script which makes the calculation , but I'm asking if there is another more performant way before I implement the solution in a Table with millions of records

    with cte1 as 
    (select CAST(MyEvent as date) Mydate,MyEvent from @mytable),
    cte2 as
    (select MyDate,MyEvent,ROW_NUMBER() over(partition by MyDate order by Mydate) R# from cte1),
    cte3 as
    (select cte21.MyDate,cte21.Myevent Departure,
cte22.Myevent Arrival from cte2 cte21 
left outer join cte2 cte22 on cte21.Mydate=cte22.Mydate and cte21.R#+1=cte22.R#
    where cte21.R# % 2 =1
    )
    select * from cte3

you say 'Arrival date is the first record of a day, Departure Date is the next record in the same day if exists' - but in your expected output, you show departure at 8:15 and then arrival at 10:20 !?? — Anand Sowmithiran, Jan 24 '22 at 17:08
Yes you're right, I corrected "Departure date is the first record of a day, Arrival Date is the next record in the same day" — Kemal AL GAZZAH, Jan 24 '22 at 17:10
Does this answer your question? [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) — Charlieface, Jan 24 '22 at 18:34
You can use `LAG` for this, and `ROW_NUMBER` will not be necessary. Also, you should do `partition by MyDate order by MyEvent` otherwise the ordering is arbitrary. — Charlieface, Jan 24 '22 at 18:35
@Charlieface - how can you use LAG for this? You need to identify which are departures and which are arrivals. Using LAG on the row with datetime '2022-01-01 10:20:00.000' would not be correct since that one should be an arrival. — Jeff, Jan 24 '22 at 21:43
@Jeff `LAG` will get you every row's previous value. You're quite that alone will not help, you still need `ROW_NUMBER` to tell you which row is the starting row etc, however a self-join is not needed — Charlieface, Jan 24 '22 at 21:46

score 0 · Answer 1 · answered Jan 25 '22 at 08:07

Thanks to the comments above, I changed the script using Lead Function and without self joining which will improve the performance.

The new script returns the same result as the previous one but with of course a best execution performance

---First script with self joining
with cte1 as 
    (select CAST(MyEvent as date) Mydate,MyEvent from @mytable),
    cte2 as
    (select MyDate,MyEvent,ROW_NUMBER() over(partition by MyDate order by Mydate) R# from cte1),
    cte3 as
    (select cte21.MyDate,cte21.Myevent Departure,
cte22.Myevent Arrival from cte2 cte21 
left outer join cte2 cte22 on cte21.Mydate=cte22.Mydate and cte21.R#+1=cte22.R#
    where cte21.R# % 2 =1)    select * from cte3;

--New script using Lead function without self joining
with cte1 as (select cast(MyEvent as date) MyDate,Myevent from @Mytable),
cte2 as (select row_number() over(partition by Mydate order by Myevent) R#,MyDate ,Myevent Departure,LEAD(Myevent) over (PARTITION by MyDate order by Myevent) Arrival from cte1)
select * from cte2 where R# % 2=1;

How to calculate Arrival and Departure Date Times from a list of Date Times values stored in an SQL SERVER Table with optimized way

1 Answers1