Mean imputation
Create a stored procedure that will apply mean imputation to the YellowTripData records with an incorrect TripDistance of zero. The average trip distance variable should have a precision of 18 and 4 decimal places.
This exercise is part of the course
Writing Functions and Stored Procedures in SQL Server
Exercise instructions
- Create a stored procedure named
cuspImputeTripDistanceMean - Create a numeric variable:
@AvgTripDistance. - Compute the average
TripDistancefor all records whereTripDistanceis greater than0. - Update the records in
YellowTripDatawhereTripDistanceis0and set to@AvgTripDistance.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create the stored procedure
___ ___ dbo.___
AS
BEGIN
-- Specify @AvgTripDistance variable
___ ___ AS numeric (18,4)
-- Calculate the average trip distance
___ ___ = ___(___)
FROM YellowTripData
-- Only include trip distances greater than 0
___ ___ ___ ___
-- Update the records where trip distance is 0
___ ___
___ ___ = ___
WHERE ___ = _
END;