asp.net Dynamic Data filtering view

Question

I use asp.net dynamic data to show the contents of a few tables that I have in a sql server database and it's pretty cool. Apart from that, I'm trying to do one other thing.

say there is a table

CustomerComplaints
    - some fields
    - CustomerName
    - ComplaintType
    - ComplaintDate
    - other fields

I want to show a view with Top Complaining Customer

so I created a view

 select CustomerName, count(*) ComplaintCount from CustomerComplaints group by CustomerName

but I also want to be able to pass in an optional AfterDate and BeforeDate to filter on ComplaintDate to do my Top Compalining Customer view.

What's a good way to go about to do this without creating a page very specific for this problem?

Thanks

Josh Darnell · Accepted Answer · 2013-03-11 17:40:53Z

1

You could pass parameters to your query and use this:

SELECT CustomerName, count(*) ComplaintCount 
FROM CustomerComplaints 
WHERE ComplaintDate BETWEEN @AfterDate AND @BeforeDate
GROUP BY CustomerName

And then set default values for those parameters as whatever the min and max are for that column's data type (if it's datetime2, the values would be 0001-01-01 and 9999-12-31). This way, if you don't pass parameters explicitly, the default values won't filter your results at all.

answered Mar 11, 2013 at 17:40

Josh Darnell

11.5k9 gold badges40 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

asp.net Dynamic Data filtering view

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related