Case study UDFs

Writing Functions and Stored Procedures in SQL Server

Meghan Kwartler

IT Consultant

Taxi ride business problem

EU private equity firm seeking investment opportunity in US Transportation.
What is the average fare per distance, ride count & total ride time for each NYC borough on each day of the week?

Taxi ride business problem

EU private equity firm seeking investment opportunity in US Transportation.
What is the average fare per distance, ride count & total ride time for each NYC borough on each day of the week?
Which pickup locations within the borough should be scheduled for each of the driver shifts?

Conversion UDFs

CREATE FUNCTION dbo.ConvertMileToMeter (@miles numeric)
RETURNS numeric
AS
BEGIN
RETURN (SELECT @miles * 1609.34)
END

CREATE FUNCTION dbo.ConvertCurrency (@Currency numeric, @ExchangeRate numeric)
RETURNS numeric
AS
BEGIN
RETURN (SELECT @ExchangeRate * @Currency)
END

SELECT TripDistance as 'MileDistance', 
dbo.ConvertMileToMeter (TripDistance) as 'MeterDistance', 
FareAmount as 'FareUSD', 
dbo.ConvertCurrency (FareAmount, '.78') as 'FareGBP' 
FROM dbo.YellowTripData

+--------------+---------------+---------+---------+
| MileDistance | MeterDistance | FareUSD | FareGBP |
|--------------+---------------+---------+---------+
|  1.10        | 1609          |  6      | 6       |
|  0.02        | 0             | 52      | 52      |
|  0.50        | 1609          |  4      | 4       |
|  7.75        | 12875         | 22      | 22      |
|  0.80        | 1609          |  6      | 6       |
|  0.90        | 1609          |  7      | 7       |
|  1.76        | 3219          |  7      | 7       |
|  8.47        | 12875         | 24      | 24      |
|  2.40        | 3219          | 10.50   | 11      |
| 12.60        | 20921         | 60      | 60      |
|  0.90        | 1609          |  7      | 7       |
+--------------+---------------+---------+---------+

Iterate

ALTER FUNCTION dbo.ConvertMileToMeter (
  @miles numeric (18, 2)
) RETURNS numeric (18, 2) as BEGIN RETURN (
  SELECT @miles * 1609.34
) END;

ALTER FUNCTION dbo.ConvertCurrency (
  @Currency numeric (18, 2), 
  @ExchangeRate numeric(18, 2)
) RETURNS numeric (18, 2) AS BEGIN RETURN (
  SELECT @ExchangeRate * @Currency
) END;

What about Shifts?

CREATE FUNCTION dbo.GetShift (@Hour int)
RETURNS int
AS
BEGIN
RETURN (CASE 
    WHEN @Hour >= 0 AND @Hour < 9 THEN 1
    WHEN @Hour >= 9 AND @Hour < 18 THEN 2
    WHEN @Hour >= 18 AND @Hour < 24 THEN 3
END)
END;

Test Shifts

SELECT 
  DATENAME(hour, PickupDate) AS 'Hour', 
  dbo.GetShift (
    DATENAME(hour, PickupDate)
  ) AS 'Shift' 
FROM YellowTripData 
GROUP BY DATENAME(hour, PickupDate) 
ORDER BY 
  dbo.GetShift (
    DATENAME(hour, PickupDate)
  )

+------+-------+
| Hour | Shift |
|------+-------|
|  3   | 1     |
|  6   | 1     |
|  4   | 1     |
|  2   | 1     |
|  0   | 1     |
|  8   | 1     |
|  5   | 1     |
|  1   | 1     |
|  7   | 1     |
|  9   | 2     |
| 11   | 2     |
| 15   | 2     |
| 14   | 2     |
| ...  | ...   |
+------+-------|

Your turn!

Writing Functions and Stored Procedures in SQL Server