>I have a delivery table with the following columns
>
>location c(10)
>driver c(2)
>pickupdate datetime
>pickuptime c(8) 99:99:99
>
>I want to sequence pickups for a given driver, location and day of the week by pickup time.
>One wrinkle is that there are lots of pickups for any day and there can be some odd times. I want to use the most common time for a day.
>That is, I want to wind up a table with one row for each driver, location and day of the week sequenced as above
>Another wrinkle is that I might want to do this in MySql as well as in SQL Server.
>
>Any ideas most welcome.
In SQL Server the first idea that comes to mind:
;with cte as (select *, count(*) over (partition by driver, location, datepart(weekday, pickupdate), pickuptime) as cntTimesPerDay from DeliveryTable),
cte2 as (select *, row_number() over (partition by driver, location, datepart(weekday, pickupdate) order by cntTimesPerDay DESC) as Rn from cte)
select * from cte2 where Rn = 1
If it's not broken, fix it until it is.
My Blog