I have a table containing records relating to public transportation. I need to know what time of day the peak vehicles were out and how many vehicles.
The date range would be a fiscal year, from 7/1/yyyy to 6/30/yyyy.
My table is called fixedrouterecords and here is a sample of the relevant columns.
I have tried using some of the examples for peak users, but could not get there.
service_date bus leave_yard return_to_yard
2016-10-24 104 05:15:00 06:30:00
2016-10-24 204 04:10:00 06:30:00
EDIT - Removed the need for the UDF and added an ad-hoc tally
Declare @YourTable table (service_date date,bus int,leave_yard Time,return_to_yard time) Insert Into @YourTable values ('2016-10-24',104,'05:15:00','06:30:00'), ('2016-10-24',204,'04:10:00','06:30:00') ;with cte0(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)) , cteT(T) As (Select Top 1440 cast(DateAdd(MI,Row_Number() over (Order By (Select NULL))-1,'1900-01-01') as time) From cte0 N1, cte0 N2, cte0 N3, cte0 N4) , cteBase as ( Select service_date ,bus ,OutTime = T From @YourTable A -- << Replace with Your Table Name Join cteT B on T between leave_yard and return_to_yard Where Year(service_date)=2016 -- << Or any other filter you like ) Select MinOut ,MaxOut ,Busses = count(Distinct bus) From ( Select Top 1 Hits ,MinOut=min(OutTime) ,MaxOut=max(OutTime) From (Select OutTime,Hits=count(*) from cteBase group by OutTime) A Group By Hits Order By Hits Desc ) A Join cteBase B on (OutTime between MinOut and MaxOut) Group By MinOut,MaxOut
MinOut MaxOut Busses 05:15:00 06:30:00 2