Reporting Services NULL Check issues 1

[BeachSandGuy]

Hi, I am having a problem with a Reporting Services SQL statement.
1st. Have table like the following (omitted non-essentials):
Record# - Skill# - StartDate - EndDate
1 - 100 - 1/1/1980 - 12/31/2008
2 - 100 - 1/1/2009 - NULL
3 - 200 - 1/1/2009 - NULL

Essentially if the record is "active", end date is null.

In reporting services, I want to use a report parameter to limit the return of records to those with NULLs, the active records. So the parameter @ExcludeTermedRecords, boolean data type, I have default set to True.

the SQL is:
Select * from dbo.table
Where StartDate >=@StartDate AND
(@EndDate is null or EndDate <=@EndDate) AND
(If @ExcludeTermedRecords = True
Begin
Endate is Null
End)

I get an error message, "Incorrect syntax near the keyword 'if'. Incorrect syntax near 'EndDate'. (Microsoft SQL Server, Error: 156)
I've tried case statements too, I am stumped. Anyone have any ideas. Surely I'm not the first person to try to conditionally route with IF statements. I've searched high and low and couldn't find an answer. Thanks in advance!

Ian

 

[WoodyGuthrie]

An "If" construct is not going to work within your Select statement. You may be able to use a case expression with subqueries or you could create a stored procedure and use your if/then logic to build your Where clause dynamically.

But you may be better served looking into whether ISNULL or COALESCE would work for you in handling the null date filtering.

Also, be aware of exclusion problems that could arise from using optional filters on columns that are also nullable.

Woody

 

[RiverGuy]

Based on what I think you're trying to accomplish:

Select * from dbo.table
Where (StartDate >=@StartDate)
AND ((@ExcludeTermedRecords = 1 AND @EndDate IS NULL)
     OR (@ExcludeTermedRecords = 0 AND (EndDate <= @EndDate OR @EndDate IS NULL)))

 

[BeachSandGuy]

First, I want to thank both Woody and RiverGuy for taking the time to respond. This is such a great resource and incredible learning experience!

Last, RiverGuy, your solution worked very well with one minor modification. In the final or statement in the end, I needed to reverse the order so that the NULL check was first before checking the @EndDate field. It was excluding some records, although to be honest, I'm not exactly sure why (I suspect that some of my EndDate records have blanks perhaps, so the <= check was causing some havoc). So the statement that worked is follows:

Select * from dbo.table
Where (StartDate >=@StartDate)
AND ((@ExcludeTermedRecords = 1 AND @EndDate IS NULL)
OR (@ExcludeTermedRecords = 0 AND (@EndDate IS NULL OR EndDate <= @EndDate)))

Thanks again!!!!!

Ian