Filtering a view which contains aggregates on date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Filtering a view which contains aggregates on date

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2011-11-02 : 08:25:44

Hi,

If I have a view

CREATE VIEW myView
AS

SELECT COUNT(*)
,CreatedBy
FROM myTable
GROUP BY CreatedBy

GO

Idealy I want to be able to filter my view on created date but if I add created date to the above I have to add it to the GROUP BY clause which gives me a count for created by on created date.

How do I add a filter on date to my view is it possible? Would I have to use a stored proc? e.g.

SELECT COUNT(*)
,CreatedBy
FROM myTable
WHERE created BETWEEN @StartDate AND @EndDate
GROUP BY CreatedBy

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 09:59:36

Probably best not to use a view for this - it doesn't give you much.
Stored procedure would give flexibility and more security than the view - the view might just give a bit of security but that would be better as just
select CreatedBy from myTable
and filter/group by in the client.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-02 : 12:28:52

An inline Table-Vaued Function functions like a "view with parameters."

CREATE FUNCTION dbo.MyFunction
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS TABLE
AS
RETURN
(
    SELECT COUNT(*)
    ,CreatedBy
    FROM myTable
    WHERE created BETWEEN @StartDate AND @EndDate
    GROUP BY CreatedBy
);

EDIT: Added sample function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources