SQL Query

[esanray]

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.

[rajatrastogi]

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