Help Please: Advice and best practice for narrowing results by date with nulls

Question

Post reply

Help Please: Advice and best practice for narrowing results by date with nulls

dbridgland

SSC-Addicted

Points: 445
More actions
September 29, 2008 at 3:49 am

#194258

Hi,
I'm trying to introduce an option in my query to narrow the results by date. I'm using parameters to handle the date values.
I'd like the query to return the results based on the following rules:
1. If the parameter value is not null, return only those rows that are within the date range
OR
2. If the parameter value is null, return all rows.
I've tried a few options (LEFT OUTER JOIN with clauses, Case statement) but each have fallen short of the mark. I think this example best illustrates what I'm trying to achieve.
DECLARE @REG_START_DATE DATETIME
DECLARE @REG_END_DATE DATETIME
SET @REG_START_DATE = '2000-01-01'
SET @REG_END_DATE = '2007-12-31'
SELECT CASE.CASE_ID,
CASE.TITLE,
CASE_APP.APP_NO,
CASE_APP.APP_DATE,
CASE_REG.REG_NO,
CASE_REG.REG_DATE,
FROM CASE
LEFT OUTER JOIN CASE_APP
ON CASE.CASE_ID = CASE_APP.CASE_ID
LEFT OUTER JOIN CASE_REG
ON CASE.CASE_ID = CASE_REG.CASE_ID
WHERE ((CASE.REG_DATE BETWEEN @REG_START_DATE AND @REG_END_DATE )
OR
(CASE.REG_DATE IS NULL ) AND
Thanks in advance for any assistance.
Regards
Dan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

abhijeetv SSCommitted Points: 1945 More actions · Answer 1

have you already tried:

WHERE REG_DATE BETWEEN isnull(@REG_START_DATE,REG_DATE) AND isnull(@REG_END_DATE,REG_DATE)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

WHERE REG_DATE BETWEEN ISNULL(@REG_START_DATE,REG_DATE)

AND ISNULL(@REG_END_DATE,REG_DATE)

"Keep Trying"

dbridgland SSC-Addicted Points: 445 More actions · Answer 3

abhijeetv (9/29/2008)
have you already tried:
WHERE REG_DATE BETWEEN isnull(@REG_START_DATE,REG_DATE) AND isnull(@REG_END_DATE,REG_DATE)

Hi abhijeetv

I've just tried your suggestion, but it failes on the second option,

2. If the parameter value is null, return all rows.

Your suggestion exclues the rows where the values for REG_DATE are null.

Thanks for you help though.

Regards

Dan

abhijeetv SSCommitted Points: 1945 More actions · Answer 4

You can add a separate CASE for checking if the parameter is NULL and use the BETWEEN clause only when it is not null.so the WHERE condition wont be there in case the parameter value is NULL which would return all rows.

dbridgland SSC-Addicted Points: 445 More actions · Answer 5

abhijeetv (9/29/2008)
You can add a separate CASE for checking if the parameter is NULL and use the BETWEEN clause only when it is not null.so the WHERE condition wont be there in case the parameter value is NULL which would return all rows.

I've tried the following but that still wont work.

DECLARE @REG_START_DATE DATETIME

DECLARE @REG_END_DATE DATETIME

SET @REG_START_DATE = '2000-01-01'

SET @REG_END_DATE = '2007-12-31'

SELECT CASE.CASE_ID,

CASE.TITLE,

CASE_APP.APP_NO,

CASE_APP.APP_DATE,

CASE_REG.REG_NO,

CASE_REG.REG_DATE,

FROM CASE

LEFT OUTER JOIN CASE_APP

ON CASE.CASE_ID = CASE_APP.CASE_ID

LEFT OUTER JOIN CASE_REG

ON CASE.CASE_ID = CASE_REG.CASE_ID

WHERE CASE @REG_START_DATE WHEN NULL

THEN 1

ELSE (CASE_REG.REG_DATE <= @REG_START_DATE )

END

CASE @REG_END_DATE WHEN NULL

THEN 2

ELSE (CASE_REG.REG_DATE => @REG_END_DATE )

END

Is this what you meant?

Thanks again for your help

Regards

Dan