Session Ready
Exercise

Pickup locations by shift

It's time to solve the second objective of the business case. What are the AvgFarePerKM, RideCount and TotalRideMin for each pickup location and shift within a NYC Borough?

Instructions
100 XP
  • Create a stored procedure named cuspPickupZoneShiftStats that accepts @Borough nvarchar(30) as an input parameter and limits records with the matching Borough value.
  • Calculate the 'Shift' by passing the hour of the PickupDate to the dbo.GetShiftNumber() function. Use the DATEPART function to select only the hour portion of the PickupDate.
  • Group by PickupDate weekday, shift, and Zone.
  • Sort by PickupDate weekday (with Monday first), shift, and TotalRideMin.