Multi statement TVF

Create a multi statement table value function that returns the trip count and average ride duration for each day for the month & year parameter values passed.

Cet exercice fait partie du cours

Writing Functions and Stored Procedures in SQL Server

Afficher le cours

Instructions

Create a function CountTripAvgDuration() that returns a table variable named @DailyTripStats.
Declare input parameters @Month and @Year.
Insert the query results into the @DailyTripStats table variable.
Use CAST to select and group by StartDate as a date data type.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

-- Create the function
CREATE FUNCTION ___ (___ CHAR(2), ___ CHAR(4))
-- Specify return variable
RETURNS ___ TABLE(
	TripDate	date,
	TripCount	int,
	AvgDuration	numeric)
AS
BEGIN
-- Insert query results into @DailyTripStats
___ ___
SELECT
    -- Cast StartDate as a date
	___(___ AS ___),
    COUNT(ID),
    AVG(Duration)
FROM CapitalBikeShare
WHERE
	DATEPART(month, StartDate) = @Month AND
    DATEPART(year, StartDate) = @Year
-- Group by StartDate as a date
GROUP BY ___(___ AS ___)
-- Return
___
END

Modifier et exécuter le code