Bobby Kuhn Bobby Kuhn - 1 month ago 8
SQL Question

How do i find the peak number of vehicles and peak times

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


of course there are thousands of lines for a fiscal year

Answer

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

Returns

MinOut      MaxOut      Busses
05:15:00    06:30:00    2
Comments