Create view with group by and extra fields

Question

I had a hard time putting the question title together but essentially I want to create a view like this:

ALTER VIEW vPlaceStatistics
AS
SELECT COUNT (TypeID) as Count, TypeID
FROM PlaceStatistics
GROUP BY TypeID

...which works great, but there is a Date field (mydatefield) on the table that I want to be able to use as a filter in my view.

If include mydatefield in my view then it creates record for each date whereas I want it to create one for each TypeID.

Essentially, I want my view to have access to the date field without it being part of the group by, if that is the right way to do it.

Answer 1

If you want to filter rows before the aggregation, I would use an inline table-valued function instead of a view:

CREATE FUNCTION dbo.PlaceStatisticsForDate
    (@DateFilter AS date)
RETURNS TABLE
AS
RETURN
    SELECT COUNT(PS.TypeID) AS TypeCount, PS.TypeID
    FROM dbo.PlaceStatistics AS PS
    WHERE PS.mydatefield = @DateFilter
    GROUP BY PS.TypeID;

Usage:

SELECT PSD.TypeCount, PSD.TypeID
FROM dbo.PlaceStatisticsForDate ('20160506') AS PSD;

Answer 2

If all possible filter types (=@d, between @d1 and @d2, day of week in (@dw1,.. @dw7), ...) can be defined at design time then create TVF[s] the way Paul White described. Otherwise you need dynamic sql. Views wouldn't help anyway.