SQL Server I can't able to write a sql query for one requirements

qvtsj1bj 于 2023-04-10 发布在其他

关注(0)|答案(1)|浏览(132)

In a sql server table contains train_id,station_name and Reaching_time. so I want to add one more column while using select query. that column is duration. so first station reaching time and second station reaching time there so simply we want to minus then we will got the duration. but I can't able to achive it. so I attached my query but I got null value in duration column. Please refer below screenshot.

Query:

select
    t1.train_id, t1.Station_Name, t1.Reaching_Timing, DATEDIFF(MINUTE,t1.Reaching_Timing,t2.Reaching_Timing) 
from train_schudle t1
left join train_schudle t2
    on t1.train_id=t2.train_id
group by t1.train_id, t1.Station_Name, t1.Reaching_Timing,t2.train_id, t2.Station_Name, t2.Reaching_Timing;

I'm getting only null value in duration column so I want to correct duration time in that duration column.

train_id	Station_Name	Reaching_Timing
1	sanfraneco	10:30:00.0000000
2	Newyork	12:30:00.0000000
3	chicago	01:45:00.0000000

sql-server

来源：https://stackoverflow.com/questions/75888973/i-cant-able-to-write-a-sql-query-for-one-requirements

1条答案

按热度按时间

kcugc4gi1#

You want the LAG() windowing function.

SELECT train_id, Station_Name, Reaching_Time, 
   DateDiff(minute, LAG(Reaching_time) OVER (PARTITION BY train_id ORDER BY reaching_time), Reaching_Time) Duration
FROM train_schedule

This assumes the Reaching_time is a reasonable data type, and not a string/varchar column.

But note: I would expect null values based on the sample data, since these are different trains. If you want to see results, use the same train_id in a few of the sample data rows, so you can follow the same train from one stop to the next. You can see that effect here:
https://dbfiddle.uk/tTqKnXqS

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server I can't able to write a sql query for one requirements

1条答案

相关问题

热门标签

最新问答