April 10, 2009 at 3:11 am
I have 3 different pairs of @DateFrom and @DateTo parameters (Logged Date, Paid Date, Outcome Date) and depending on which pair is selected, the others can be NULL , I need to return records between these two dates. So if the logged date parameters are selected then I have tried this:
WHERE (LOGGED_DATE = CASE WHEN NOT (@LoggedDateFrom) IS NULL AND NOT (@LoggedDateTo) IS NULL
THEN 'BETWEEN @LoggedDateFrom AND @LoggedDateTo' ELSE LOGGED_DATE END)
but get the ' ' round the BETWEEN bit and it doesn't run.
Anyone know how to check if the date is not null then return data BETWEEN @DateFrom AND @Dateto
Thanks.
April 10, 2009 at 3:34 am
What do you want to do if more than one pair of date range parameters are not null?
@LoggedDateFrom NOT NULL
@LoggedDateTo NOT NULL
@PaidDateFrom NOT NULL
@PaidDateTo NOT NULL
@OutcomeDateFrom NULL
@OutcomeDateTo NULL
What do you want to do if one parameter from a pair of date range parameters is null, but not the other?
@LoggedDateFrom NOT NULL
@LoggedDateTo NULL
@PaidDateFrom NULL
@PaidDateTo NULL
@OutcomeDateFrom NULL
@OutcomeDateTo NULL
or a combination like the following:
@LoggedDateFrom NOT NULL
@LoggedDateTo NOT NULL
@PaidDateFrom NOT NULL
@PaidDateTo NULL
@OutcomeDateFrom NULL
@OutcomeDateTo NULL
What do you want to do if all date range parameters are null?
April 10, 2009 at 3:49 am
It would be ok to select more than one pair so between logged dates and between paid dates may be selected.
both the from and and the to date of any pair must be not null otherwise it is ignored.
Thanks.
April 10, 2009 at 3:52 am
sorry, forgot your last question. If all are null then all records will be returned. I have a default fromDate and toDate in the logged date parameter but it could be changed to Null by the user so all records would be returned.
Thanks.
April 10, 2009 at 4:16 am
Case statement is a function that can have as a result only one value. You are trying the use the case statement as a control flow statement. Here is the way to use it:
WHERE LoogedDate >= CASE WHEN @LoggedDateFrom IS NULL OR @LoggedDateTo IS NULL THEN LoggedDate ELSE @LoggedDateFrom END
AND LoogedDate <= CASE WHEN @LoggedDateTo IS NULL THEN OR @LoggedDateFrom IS NULL LoggedDate ELSE @LoogedDateTo END
Notice that this kind of query produces a none optimal query plan. If you have a small table, then it doesn’t matter but if you have a big table, you’ll have to considare a different approach.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 10, 2009 at 4:24 am
Thanks for your reply Adi.
That makes more sense! The table is going to be huge so I will have to rethink it, starting with passing the parameters to a function to do the filtering.
Cheers.
April 10, 2009 at 5:15 am
You could try this WHERE clause, but Adi's comment about potentially non-optimal query plans is stil relevant here, so you should test it for acceptable performance against a large data set.
WHERE (@LoggedDateFrom IS NULL OR @LoggedDateTo IS NULL OR LOGGED_DATE BETWEEN @LoggedDateFrom AND @LoggedDateTo)
AND (@PaidDateFrom IS NULL OR @PaidDateTo IS NULL OR PAID_DATE BETWEEN @PaidDateFrom AND @PaidDateTo)
AND (@OutcomeDateFrom IS NULL OR @OutcomeDateTo IS NULL OR OUTCOME_DATE BETWEEN @OutcomeDateFrom AND @OutcomeDateTo)
Other options would be to use dynamic SQL to construct a WHERE clause specific to which pairs of date range parameters are supplied, or to include multiple alternative queries in a stored procedure, and determine which one to use based on the parameters supplied with IF statements. The TSQL below shows how you might go about using dynamic SQL.
DECLARE @LoggedDateFrom datetime
DECLARE @LoggedDateTo datetime
DECLARE @PaidDateFrom datetime
DECLARE @PaidDateTo datetime
DECLARE @OutcomeDateFrom datetime
DECLARE @OutcomeDateTo datetime
SELECT @LoggedDateFrom = '20090101', @LoggedDateTo = '20090131'
SELECT @PaidDateFrom = '20090115', @PaidDateTo = '20090215'
SELECT @OutcomeDateFrom = '20090201', @OutcomeDateTo = NULL
DECLARE @sql nvarchar(4000)
DECLARE @params nvarchar(1000)
DECLARE @crlf nvarchar(2)
SELECT @sql = N'SELECT * FROM dbo.MyTable WHERE (1 = 1)'
SELECT @params = N'@LoggedDtFrom datetime, @LoggedDtTo datetime, @PaidDtFrom datetime, @PaidDtTo datetime, @OutcomeDtFrom datetime, @OutcomeDtTo datetime'
SELECT @crlf = CHAR(13) + CHAR(10)
IF (@LoggedDateFrom IS NOT NULL AND @LoggedDateTo IS NOT NULL) BEGIN
SELECT @sql = @sql + @crlf + N' AND (LOGGED_DATE BETWEEN @LoggedDtFrom AND @LoggedDtTo)'
END
IF (@PaidDateFrom IS NOT NULL AND @PaidDateTo IS NOT NULL) BEGIN
SELECT @sql = @sql + @crlf + N' AND (PAID_DATE BETWEEN @PaidDtFrom AND @PaidDtTo)'
END
IF (@OutcomeDateFrom IS NOT NULL AND @OutcomeDateTo IS NOT NULL) BEGIN
SELECT @sql = @sql + @crlf + N' AND (OUTCOME_DATE BETWEEN @OutcomeDtFrom AND @OutcomeDtTo)'
END
--PRINT @sql
EXEC sp_executesql @sql, @params,
@LoggedDtFrom = @LoggedDateFrom,
@LoggedDtTo = @LoggedDateTo,
@PaidDtFrom = @PaidDateFrom,
@PaidDtTo = @PaidDateTo,
@OutcomeDtFrom = @OutcomeDateFrom,
@OutcomeDtTo = @OutcomeDateTo
April 10, 2009 at 5:32 am
Hi
My solution would be quiet alike Andrew's first. I just would negate the AND-OR to avoid to get all values if all parameters would be NULL:
WHERE (@LoggedDateFrom IS NOT NULL AND @LoggedDateTo IS NOT NULL AND LOGGED_DATE BETWEEN @LoggedDateFrom AND @LoggedDateTo)
OR (@PaidDateFrom IS NOT NULL AND @PaidDateTo IS NOT NULL AND PAID_DATE BETWEEN @PaidDateFrom AND @PaidDateTo)
OR (@OutcomeDateFrom IS NOT NULL AND @OutcomeDateTo IS NOT NULL AND OUTCOME_DATE BETWEEN @OutcomeDateFrom AND @OutcomeDateTo)
In addition don't forget the RECOMPILE option if you use the statement within a procedure to avoid the wrong execution plan.
Greets
Flo
April 10, 2009 at 5:37 am
Andrew, your WHERE clause worked a treat!
I will have to look into your T SQL suggestion as there will potential be 16,000 records a month being added to the table so performance will be a big issue.
Cheers.
April 10, 2009 at 5:41 am
Flo this also worked great and in fact I did say I would want all records returned if no parameters were selected but in reality this would be probably crash the server! so thank you.
April 10, 2009 at 5:46 am
maxine (4/10/2009)
Flo this also worked great and in fact I did say I would want all records returned if no parameters were selected but in reality this would be probably crash the server! so thank you.
Dang! I didn't notice this!
Sorry also to Andrew!
Greets
Flo
April 10, 2009 at 5:54 am
Florian Reischl (4/10/2009)
maxine (4/10/2009)
Flo this also worked great and in fact I did say I would want all records returned if no parameters were selected but in reality this would be probably crash the server! so thank you.Dang! I didn't notice this!
Sorry also to Andrew!
Greets
Flo
No need to be sorry Flo.
Your warning about retrieving all rows is a good one and Maxine clearly recognizes this.
--Andrew
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply