September 9, 2014 at 7:07 am
I've set up a report with two parameters, @StartDate and @EndDate. Report users expect to enter the following values:
StartDate: 9/20/2013 EndDate: 9/20/2013 -- Show all records dated 9/20/2013
StartDate: 9/20/2013 EndDate: [empty] -- Show all records dated 9/20/2013
StartDate: 9/20/2013 EndDate: 9/23/2013 -- Show all records dated between 9/20/2013 and 9/23/2013, including all records dated 9/23/2013
StartDate: [empty] EndDate: [empty] -- Don't filter any records based on date (show all records for all dates)
I believe I need to handle all these cases in the "WHERE" statement for the query, but I'm not sure how to go about doing this. Hopefully someone can point me in the right direction to get started with this.
September 9, 2014 at 7:14 am
sgroesz (9/9/2014)
I've set up a report with two parameters, @StartDate and @EndDate. Report users expect to enter the following values:StartDate: 9/20/2013 EndDate: 9/20/2013 -- Show all records dated 9/20/2013
StartDate: 9/20/2013 EndDate: [empty] -- Show all records dated 9/20/2013
StartDate: 9/20/2013 EndDate: 9/23/2013 -- Show all records dated between 9/20/2013 and 9/23/2013, including all records dated 9/23/2013
StartDate: [empty] EndDate: [empty] -- Don't filter any records based on date (show all records for all dates)
I believe I need to handle all these cases in the "WHERE" statement for the query, but I'm not sure how to go about doing this. Hopefully someone can point me in the right direction to get started with this.
Quick thought, this article Catch-all queries[/url] is a good place to start.
😎
September 9, 2014 at 12:08 pm
You could do this with a stored procedure, but if it were me, I would do the grunt work in the front end and just pass the completed query to the report to execute. You 4 have options that you need to cover:
0 0
0 1
1 0
1 1
So in pseudo code (which can be adapted to any language or T-SQL) it would be
if startDate = 0 and endDate = 0
startDate = '1900-01-01'
endDate = '2100-12-31'
else if startDate = 0 and endDate > 0
startDate = '1900-01-01'
else if startDate > 0 and endDate = 0
endDate = '2100-12-31'
end
Then you build your query:
WHERE someDate >= startDate and someDate < DATEADD(dd, 1, endDate)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 9, 2014 at 1:14 pm
Eirikur Eiriksson (9/9/2014)
sgroesz (9/9/2014)
I've set up a report with two parameters, @StartDate and @EndDate. Report users expect to enter the following values:StartDate: 9/20/2013 EndDate: 9/20/2013 -- Show all records dated 9/20/2013
StartDate: 9/20/2013 EndDate: [empty] -- Show all records dated 9/20/2013
StartDate: 9/20/2013 EndDate: 9/23/2013 -- Show all records dated between 9/20/2013 and 9/23/2013, including all records dated 9/23/2013
StartDate: [empty] EndDate: [empty] -- Don't filter any records based on date (show all records for all dates)
I believe I need to handle all these cases in the "WHERE" statement for the query, but I'm not sure how to go about doing this. Hopefully someone can point me in the right direction to get started with this.
Quick thought, this article Catch-all queries[/url] is a good place to start.
😎
Yeap. This is how I would do it too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2014 at 9:31 am
I will also recommend Gail's article.
The logic for your query will look like this (note that you did not detail the expected behavior for if only @enddate has a value).
DECLARE @startdate date = '9/8/2014',
@enddate date = '9/9/2014';
WITH
random_dates AS
(
SELECT dt = cast(dt as date)
FROM (values (getdate()-2),(getdate()-1), (getdate())) t(dt)
)
SELECT *
FROM random_dates
WHERE dt between @startdate and isnull(@enddate,@startdate)
OR @startdate IS NULL;
Gail's article will give you a couple options for improving it.
-- Itzik Ben-Gan 2001
September 10, 2014 at 10:29 am
Alan.B (9/10/2014)
I will also recommend Gail's article.The logic for your query will look like this (note that you did not detail the expected behavior for if only @enddate has a value).
DECLARE @startdate date = '9/8/2014',@enddate date = '9/9/2014';
WITH
random_dates AS
(
SELECT dt = cast(dt as date)
FROM (values (getdate()-2),(getdate()-1), (getdate())) t(dt)
)
SELECT *
FROM random_dates
WHERE dt between @startdate and isnull(@enddate,@startdate)
OR @startdate IS NULL;
Gail's article will give you a couple options for improving it.
Thanks Alan. I selected yours as the answer because you provided the logic for this particular use case, though if I could, I would give equal credit to Eirikur for sharing the blog post initially.
The truth is, I found the "Dynamic Search Conditions in T-SQL" by Erland Sommarskog, which was referenced in Gail's article, to be the most helpful. I learned a lot from Erland's article, which helped with some other questions I hadn't yet posted about.
Thank you for the responses - I can tell this community will be helpful to me :).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply