Database contain the following data
Schedule time Actual time. Train No Passenger
01:10 04:00 9876 50
02:00 03:00 9876 90
04:00 07:00 7654 70
05: 00 11:00 3423 90
I need the report
Hour wise No of passenger delay
Delay 2+hours 120
Delay 5+hours 90
can you please tell me the SQL Query to retrive this type orf report.
You can use stored proc for getting such reports
Assuming your data is stored in table "Timetable" and actual & scheduled time as time data type use the following code to create a stored proc
CREATE PROC GetDelayTime As
DECLARE @Delay5 int, @Delay2 int
SET @Delay5 = (select SUM(Passenger)FROM TimeTable
WHERE convert(time, dateadd(minute, datediff(minute,scheduledTime , actualTime), 0))>='05:00:00')
SET @Delay2=( select SUM(Passenger) FROM TimeTable
WHERE convert(time, dateadd(minute, datediff(minute,scheduledTime , actualTime), 0))>='02:00:00'
AND
convert(time, dateadd(minute, datediff(minute,scheduledTime , actualTime), 0))<'05:00:00')
PRINT 'Delay 2+ Hours ' + Convert(varchar(10),@Delay2)
PRINT 'Delay 5+ Hours ' + Convert(varchar(10),@Delay5)
Hope this helps :D
Rajat Rastogi