依班別統計的上車地點
來完成此商業案例的第二個目標。針對每個 NYC Borough 中的上車地點與班別，AvgFarePerKM、RideCount 與 TotalRideMin 分別是多少？
本練習屬於課程
在 SQL Server 中撰寫函式與預存程序
練習說明
- 建立名稱為
cuspPickupZoneShiftStats的預存程序，接受@Borough nvarchar(30)作為輸入參數，並只篩選Borough值相符的紀錄。 - 計算
'Shift'：將PickupDate的hour傳入dbo.GetShiftNumber()函式。使用DATEPART函式只擷取PickupDate的hour部分。 - 依
PickupDate的星期幾、班別與 Zone 進行分組。 - 依
PickupDate的星期幾（星期一優先）、班別，以及TotalRideMin進行排序。
動手互動練習
試著完成這個範例程式碼，體驗一下這個練習。
-- 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
___.___(___(___, ___)) 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
___.___(___(___, ___)),
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
___.___(___(___, ___)),
SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60 DESC
END;