NYC Borough statistics SP

It's time to apply what that you have learned in this course and write a stored procedure to solve the first objective of the Taxi Ride business case. Calculate AvgFarePerKM, RideCount and TotalRideMin for each NYC borough and weekday. After discussion with stakeholders, you should omit records where the TripDistance is zero.

Este ejercicio forma parte del curso

Writing Functions and Stored Procedures in SQL Server

Ver curso

Instrucciones del ejercicio

Select and group by pickup weekday and Borough.
Calculate AvgFarePerKM with dbo.ConvertDollar() and dbo.ConvertMiletoKM() utilizing .88 exchange rate to the Euro.
Display AvgFarePerKM as German currency, RideCount and TotalRideMin as German numbers.
Omit records where TripDistance is 0.

Ejercicio interactivo práctico

Prueba este ejercicio y completa el código de muestra.

CREATE OR ALTER PROCEDURE dbo.cuspBoroughRideStats
AS
BEGIN
SELECT
    -- Calculate the pickup weekday
	___(weekday, PickupDate) AS 'Weekday',
    -- Select the Borough
	Zone.Borough AS 'PickupBorough',
    -- Display AvgFarePerKM as German currency
	___(AVG(dbo.___(TotalAmount, .88)/dbo.___(TripDistance)), '_', 'de-de') AS '___',
    -- Display RideCount in the German format
	___(COUNT(ID), '_', 'de-de') AS '___',
    -- Display TotalRideMin in the German format
	___(SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60, '_', 'de-de') AS '___'
FROM YellowTripData
INNER JOIN TaxiZoneLookup AS Zone 
ON PULocationID = Zone.LocationID
-- Only include records where TripDistance is greater than 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;

Editar y ejecutar código