Good afternoon! Could anyone help me to solve the task? I have a table:
| Id | Date | Reason |
|---|---|---|
| 1 | 2020-01-01 10:00 | Departure |
| 1 | 2020-01-01 12:20 | Arrival |
| 1 | 2020-01-02 14:30 | Departure |
| 1 | 2020-01-02 19:20 | Arrival |
| 1 | 2020-01-03 15:40 | Departure |
| 1 | 2020-01-04 19:20 | Arrival |
| 2 | 2020-02-03 15:40 | Departure |
| 2 | 2020-02-04 19:20 | Arrival |
| 3 | 2020-03-05 15:40 | Departure |
| 3 | 2020-03-05 19:20 | Arrival |
| 3 | 2020-03-06 16:28 | Departure |
| 3 | 2020-03-06 21:00 | Arrival |
I need to estimate average duration of each ID. At first step I want to get table, for example for id = 1, as
| Id | Duraton (minutes) |
|---|---|
| 1 | 140 |
| 1 | 290 |
| 1 | 1660 |
How can I achive that by T-Sql query?
CodePudding user response：
Assuming the rows are perfectly interleaved, you can use lead():
select t.*,
datediff(minute, date, next_date) as diff_minutes
from (select t.*,
lead(date) over (partition by id order by date) as next_date
from t
) t
where reason = 'Departure';
If you want the results for only one id, you can filter in either the subquery or the outer query.