1

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.

3

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;
0
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.