text: -- 1. Create stored procedure that will compute the borough stats
  CREATE OR ALTER PROCEDURE dbo.cuspBoroughRideStats
  AS
  BEGIN
  SELECT
      -- Calculate the pickup weekday
  	DATENAME(weekday, PickupDate) AS 'Weekday',
      -- Select the Borough
  	Zone.Borough AS 'PickupBorough',
      -- Display AvgFarePerKM as German currency
  	FORMAT(AVG(dbo.ConvertDollar(TotalAmount, .88)/dbo.ConvertMiletoKm(TripDistance)), 'c', 'de-de') AS 'AvgFarePerKM',
      -- Display RideCount in the German format
  	FORMAT(COUNT(ID), 'n', 'de-de') AS 'RideCount',
      -- Display TotalRideMin in the German format
  	FORMAT(SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60, 'n', 'de-de') AS 'TotalRideMin'
  FROM YellowTripData
  INNER JOIN TaxiZoneLookup AS Zone 
  ON PULocationID = Zone.LocationID
  -- Only include records where TripDistance is greater than 0
  WHERE TripDistance > 0
  -- Group by pickup weekday and Borough
  GROUP BY DATENAME(WEEKDAY, PickupDate), Zone.Borough
  ORDER BY CASE WHEN DATENAME(WEEKDAY, PickupDate) = 'Monday' THEN 1
  	     	  WHEN DATENAME(WEEKDAY, PickupDate) = 'Tuesday' THEN 2
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Wednesday' THEN 3
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Thursday' THEN 4
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Friday' THEN 5
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Saturday' THEN 6
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Sunday' THEN 7 END,  
  		 SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60
  DESC
  END;
  
  -- Run the Stored Procedure and store the results in a TABLE
  -- Create SPResults
  DECLARE @SPResults TABLE(
    	-- Create Weekday
  	Weekday nvarchar(30),
      -- Create Borough
  	Borough nvarchar(30),
      -- Create AvgFarePerKM
  	AvgFarePerKM nvarchar(30),
      -- Create RideCount
  	RideCount nvarchar(30),
      -- Create TotalRideMin
  	TotalRideMin nvarchar(30))
  
  -- Insert the results into @SPResults
  INSERT INTO @SPResults
  -- Execute the SP
  EXEC dbo.cuspBoroughRideStats
  
  -- Select all the records from @SPresults 
  SELECT * 
  FROM @SPResults;
  
  
  -- 2. Create SP that will compute variables for each pickup location and shift in NYC Borough
  -- Create the stored procedure
  CREATE PROCEDURE dbo.cuspPickupZoneShiftStats
  	-- Specify @Borough parameter
  	@Borough nvarchar(30)
  AS
  BEGIN
  SELECT
  	DATENAME(WEEKDAY, PickupDate) as 'Weekday',
      -- Calculate the shift number
  	dbo.GetShiftNumber(DATEPART(hour, PickupDate)) as 'Shift',
  	Zone.Zone as 'Zone',
  	FORMAT(AVG(dbo.ConvertDollar(TotalAmount, .77)/dbo.ConvertMiletoKM(TripDistance)), 'c', 'de-de') AS 'AvgFarePerKM',
  	FORMAT(COUNT (ID),'n', 'de-de') as 'RideCount',
  	FORMAT(SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60, 'n', 'de-de') as 'TotalRideMin'
  FROM YellowTripData
  INNER JOIN TaxiZoneLookup as Zone on PULocationID = Zone.LocationID 
  WHERE
  	dbo.ConvertMiletoKM(TripDistance) > 0 AND
  	Zone.Borough = @Borough
  GROUP BY
  	DATENAME(WEEKDAY, PickupDate),
      -- Group by shift
  	dbo.GetShiftNumber(DATEPART(hour, PickupDate)),  
  	Zone.Zone
  ORDER BY CASE WHEN DATENAME(WEEKDAY, PickupDate) = 'Monday' THEN 1
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Tuesday' THEN 2
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Wednesday' THEN 3
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Thursday' THEN 4
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Friday' THEN 5
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Saturday' THEN 6
                WHEN DATENAME(WEEKDAY, PickupDate) = 'Sunday' THEN 7 END,
           -- Order by shift
           dbo.GetShiftNumber(DATEPART(hour, PickupDate)),
           SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60 DESC
  END;
  
  -- Execute the SP and review the results
  -- Create @Borough
  DECLARE @Borough AS nvarchar(30) = 'Manhattan'
  -- Execute the SP
  EXEC dbo.cuspPickupZoneShiftStats
      -- Pass @Borough
  	@Borough = @Borough;
  