CODEWITHSUNDEEP
c#asp.netsqlasp.net-dynamic-data
Professor Chaos
Professor Chaos

Reputation: 9070

asp.net Dynamic Data filtering view

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

Upvotes: 1

Views: 694

Answers (1)

Josh Darnell
Josh Darnell

Reputation: 11433

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.

Upvotes: 1

Related Questions