Logical weekdays with Hot Deck

Calculate Total Fare Amount per Total Distance for each day of week. If the TripDistance is zero use the Hot Deck imputation function you created earlier in the chapter.

Bu egzersiz

Writing Functions and Stored Procedures in SQL Server

kursunun bir parçasıdır

Kursu Görüntüle

Egzersiz talimatları

Use DATENAME() and PickupDate to select the day of week.
Use AVG() to calculate TotalAmount per TripDistance, and a CASE statement to select TripDistance if it's more than 0. If not, use dbo.GetTripDistanceHotDeck().
Order by the PickupDate day of week, with 'Monday' appearing first.

Uygulamalı interaktif egzersiz

Bu örnek kodu tamamlayarak bu egzersizi bitirin.

SELECT
    -- Select the pickup day of week
	DATENAME(weekday, PickupDate) as DayofWeek,
    -- Calculate TotalAmount per TripDistance
	CAST(AVG(TotalAmount/
            -- Select TripDistance if it's more than 0
			CASE WHEN TripDistance > 0 THEN TripDistance
                 -- Use GetTripDistanceHotDeck()
     			 ELSE dbo.GetTripDistanceHotDeck() END) as decimal(10,2)) as 'AvgFare'
FROM YellowTripData
GROUP BY DATENAME(weekday, PickupDate)
-- Order by the PickupDate day of week
ORDER BY
     ___ ___ ___(weekday, PickupDate) = '___' THEN 1
         ___ ___(weekday, PickupDate) = '___' THEN 2
         ___ ___(weekday, PickupDate) = '___' THEN 3
         ___ ___(weekday, PickupDate) = '___' THEN 4
         ___ ___(weekday, PickupDate) = '___' THEN 5
         ___ ___(weekday, PickupDate) = '___' THEN 6
         ___ ___(weekday, PickupDate) = '___' THEN 7
END ASC;

Kodu Düzenle ve Çalıştır