June 1, 2010 at 7:05 am
I'm working on a SSRS project where the user will be able to select data based on 'Package', 'Date' and 'nulldates' parameters. The 'Package' parameter allows the user to select a particular package or the option to '**SELECT ALL**'. The 'Date' selects records after a chosen date. The 'nulldates' either includes or omits records with null dates.
For the sake of simplicity I've replicated the scenario purely in TSQL using test data:
IF OBJECT_ID('dbo.Table_1') IS NOT NULL
DROP TABLE dbo.Table_1
-------------
CREATE TABLE dbo.Table_1(
id INT IDENTITY(1,1) PRIMARY KEY,
EGType nchar(10) NULL,
Dates smalldatetime NULL
)
-------------
INSERT INTO dbo.Table_1 ( EGType, Dates)
SELECT 'A ','May 1 2010 12:00AM' UNION ALL
SELECT 'A ','May 13 2010 12:00AM' UNION ALL
SELECT 'A ',NULL UNION ALL
SELECT 'A ',NULL UNION ALL
SELECT 'A ','May 11 2010 12:00AM' UNION ALL
SELECT 'B ',NULL UNION ALL
SELECT 'B ','' UNION ALL
SELECT 'C ','May 7 2010 12:00AM' UNION ALL
SELECT 'A ','May 22 2010 12:00AM' UNION ALL
SELECT 'B ','May 1 2010 12:00AM' UNION ALL
SELECT 'C ',NULL UNION ALL
SELECT 'A ',NULL UNION ALL
SELECT 'A ','May 14 2010 12:00AM' UNION ALL
SELECT 'B ',NULL UNION ALL
SELECT 'B ','May 3 2010 12:00AM' UNION ALL
SELECT 'C ',NULL UNION ALL
SELECT 'A ','May 28 2010 12:00AM' UNION ALL
SELECT 'C ',NULL UNION ALL
SELECT 'C ',NULL UNION ALL
SELECT 'B ','May 4 2010 12:00AM'
What I require (and I'm struggling to think of the most effective way to go about....nor do I want to go down the route of dynamic tsql) is to allow the user to select records based on the three paramaters. Examples include:
-Select all Packages after a certain date and include null dates
-Select all Packages, all dates and omit nulls
-Select a particular package after a certain date and omit nulls.
-Etc., etc.
Here's the partially completed code (commented out bits refer to example user select preferences):
DECLARE @Package char (50)
DECLARE @Date smalldatetime
DECLARE @nulldates bit
-------------------
SET @Package = '**SELECT ALL**'
--SET @Package = 'A'
-------------------
--SET @Date = '2010-05-13 00:00:00'
SET @Date = ''
-------------------
SET @Nulldates = 1
-- SET @Nulldates = 0
-------------------
-------------------
IF @Package = '**SELECT ALL**'
BEGIN
SELECT id, EGType, Dates
FROM dbo.Table_1
WHERE Dates >= @Date ---AND if @Nulldates = 1 then include null dates
END
ELSE
BEGIN
SELECT id, EGType, Dates
FROM dbo.Table_1
WHERE EGTYPE = @Package AND
Dates >= @Date ---AND if @Nulldates = 1 then include null dates
END
I'm sure there's a simple solution to this!
Many thanks,
Dom Horton
June 1, 2010 at 7:33 am
you can use this:
DECLARE @Package char (50)
DECLARE @Date smalldatetime
DECLARE @nulldates bit
-------------------
SET @Package = '**SELECT ALL**'
--SET @Package = 'A'
-------------------
SET @Date = '2010-05-01 00:00:00'
--SET @Date = null
-------------------
SET @Nulldates = 1
-- SET @Nulldates = 0
-----------------
SET @Package = NULLIF(@Package, '**SELECT ALL**')
SELECT id, EGType, Dates
FROM dbo.Table_1
WHERE (@Package IS NULL OR EGTYPE = @Package)
AND (@Date IS NULL OR Dates >= @Date)
AND (@Nulldates = 1 OR Dates IS NOT NULL)
June 1, 2010 at 8:09 am
Brilliant,
that's simplyfied matters no end. I was going about it the wrong way.
many thanks,
Dom
June 1, 2010 at 8:41 am
If I'm guessing right, you're writing a stored proc.
To avoid parameter sniffing (which happen in this kind of stored proc) copy values from stored proc input parameters into local variables and then use them in the query.
June 1, 2010 at 8:50 am
elutin (6/1/2010)
SET @Package = NULLIF(@Package, '**SELECT ALL**')
NULLIF? That's the first time I've heard of that function. I'm glad I read this thread.
June 1, 2010 at 9:09 am
Hello again,
I think I jumped the gun in responding. Unfortunately your code doesn't do exactly what I'm after.
The problem arises if I want all records after a certain date AND include all null dates too.
I don't believe the WHERE clause can handle that...
AND (@Date IS NULL OR Dates >= @Date)
AND (@Nulldates = 1 OR Dates IS NOT NULL)
AND ('2010-05-01 00:00:00' IS NULL or Dates >= '2010-05-01 00:00:00' )
AND (1 = 1 or Dates IS NOT NULL)
June 1, 2010 at 9:36 am
Sorry, you need small modification in it:
SELECT id, EGType, Dates
FROM dbo.Table_1
WHERE (@Package IS NULL OR EGTYPE = @Package)
AND (@Date IS NULL OR ISNULL(Dates, @Date) >= @Date)
AND (@Nulldates = 1 OR Dates IS NOT NULL)
June 1, 2010 at 9:39 am
Brandie Tarvin (6/1/2010)
elutin (6/1/2010)
SET @Package = NULLIF(@Package, '**SELECT ALL**')
NULLIF? That's the first time I've heard of that function. I'm glad I read this thread.
It's not really a function as such. It is just a wrapper around
CASE WHEN construct.
You can see in compiled SQL that NULLIF(Value1, Value2) is replaced with:
CASE WHEN Value1 = Value2 THEN NULL ELSE Value1 END
Using NULLIF saves some space 😀
June 1, 2010 at 11:13 am
Elutin,
Many thanks for the modification. For a simpleton like me would you briefly explain how the logic works? Is temporarily replacing the null dates with the parameter value, checking that it is equal or greater to itself, which it is and then including that record in the output?
If I'm guessing right, you're writing a stored proc.
To avoid parameter sniffing (which happen in this kind of stored proc) copy values from stored proc input parameters into local variables and then use them in the query.
I'm working on a Reporting Services project and passing parameter values to a Select statement. If feasible, I may look at converting to SPs in the future.
It's not really a function as such. It is just a wrapper around
CASE WHEN construct.
You can see in compiled SQL that NULLIF(Value1, Value2) is replaced with:
CASE WHEN Value1 = Value2 THEN NULL ELSE Value1 END
Using NULLIF saves some space
Thanks for the explanation!
Once again thanks for your help with this
June 2, 2010 at 11:08 am
Dom,
Ref: AND (@Date IS NULL OR ISNULL(Dates, @Date) >= @Date)
Yes, we need to ignore NULLs in the Dates column in this filter, otherwise these records will not be included into output regardless of @Nulldates parameter. Basically, by replacing NULL date with @Dates will ensure that the records with null Dates values will pass this filter and their inclusion into the final output will be desided by "AND (@Nulldates = 1 OR Dates IS NOT NULL)" filter.
June 7, 2010 at 6:34 am
Thanks Eugene
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply