Casestudy stored procedures

Functies en opgeslagen procedures schrijven in SQL Server

Meghan Kwartler

IT Consultant

Taxirit businessprobleem

EU-participatiemaatschappij zoekt investering in US Transportation.
Wat is het gemiddeld tarief per afstand, het ritaantal en de totale reistijd per NYC-stadsdeel per weekdag?
Welke opstaplocaties in het stadsdeel plan je voor de diensten van chauffeurs?

Evolutie van een SP

--Query detailniveau met UDF's
SELECT 
  DATENAME (weekday, PickupDate) as 'Weekday', 
  PickupDate, 
  DropOffDate, 
  TotalAmount, 
  TripDistance, 
  dbo.ConvertDollar(TotalAmount,.88)/ dbo.ConvertMileToKm(TripDistance) as 'EuroFarePerKM', 
  DATEDIFF(SECOND, PickupDate, DropOffDate)/ 60 as 'TotalRideMin' 
FROM YellowTripData 
WHERE TripDistance > 0

SELECT DATENAME(weekday, PickupDate) as 'Weekday', 
     Zone.Borough as 'PickupBorough',
     AVG(dbo.ConvertDollar(TotalAmount, .77)/
     dbo.ConvertMiletoKM(TripDistance)))
     AS 'AvgFarePerKM',
     COUNT (ID) as 'RideCount',
     SUM(DATEDIFF(SECOND, PickupDate, DropOffDate)/60) as 'TotalRideMin'
FROM YellowTripData
INNER JOIN TaxiZoneLookup AS Zone 
ON PULocationID = Zone.LocationID 
WHERE dbo.ConvertMiletoKM(TripDistance) > 0 
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,  
AVG(dbo.ConvertDollar(TotalAmount, .77)/dbo.ConvertMiletoKM(TripDistance)) DESC;

„Laatste” stap

CREATE OR ALTER PROCEDURE dbo.cuspPickupZoneShiftStats
@Borough nvarchar(30)
AS
BEGIN
.....
END

DROP PROCEDURE IF EXISTS dbo.cuspPickupZoneShiftStats
GO
CREATE PROCEDURE dbo.cuspPickupZoneShiftStats
@Borough nvarchar(30)
AS
BEGIN
....
END

Jouw beurt!

Functies en opgeslagen procedures schrijven in SQL Server