CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Excited_to_learn
Excited_to_learn

Reputation: 359

SQL query to generate report on the basis of parameter selection

I have a requirement in which on the basis of the options selected by the user, a report gets generated.

The options are as follows:

Mandatory fields:

Org:

Start Date:

End Date:

Select one option (Radio Button):

Event date

Finalized Date

On the basis of the option selected my report should generate accordingly So far the code is:

`DECLARE @Org           VARCHAR(50)         
 DECLARE @UserName      VARCHAR(50)          
 DECLARE @StartDate     DATETIME            
 DECLARE @EndDate       DATETIME    
 DECLARE @FinalizedDate DateTime 
 DECLARE @EventDate     DateTime 

SET @org = ‘zzz’
SET @StartDate = '2014-08-29'
SET @EndDate = '2014-09-30'

Select A.*, EventDate, FinalizedDate, Org
From ABC A
WHERE   Org = @Org AND (@EventDate  BETWEEN @StartDate  AND @EndDate AND @EventDate IS NOT NULL) 
    or (@FinalizedDate   BETWEEN @StartDate  AND @EndDate AND @FinalizedDate  IS NOT NULL)

Please note that this is just the sample code the actual code is much more complicated.

I need to create a stored proc to use it in SSRS report. When I run this code, it doesn't return any data.

Upvotes: 0

Views: 86

Answers (2)

ChrisLively
ChrisLively

Reputation: 88044

EDIT

My prior answer is wrong because it was based on a misreading of your Select one section.

First off, the following is wrong: 

 DECLARE @FinalizedDate DateTime 
 DECLARE @EventDate     DateTime

Your code shouldn't pass those as separate datetimes. It should pass the selection as a single value indicating whether they want to test the Finalized or Event date. So, something like this:

DECLARE @DateOpt varchar(20)

Where DateOp will contain "EventDate" or "FinalizedDate"

This means your where clause is going to look like this:

WHERE  (Org = @Org)
AND (

( (@DateOpt ='EVENTDATE') AND (EventDate BETWEEN @StartDate  AND @EndDate) )
OR
( (@DateOpt ='FinalizedDate') AND (FinalizedDate BETWEEN @StartDate  AND @EndDate) )

)

Upvotes: 1

sqluser
sqluser

Reputation: 5672

You can use ISNULL function to include or exclude your optional parametres in your WHERE clause.

SELECT A.*, EventDate, FinalizedDate, Org
FROM ABC A
WHERE Org = @Org
AND (@EventDate BETWEEN @StartDate AND @EndDate) 
OR (@FinalizedDate BETWEEN @StartDate AND @EndDate)
AND EventDate = ISNULL(@EventDate, EventDate)
AND FinalizedDate = ISNULL(@FinalizedDate , FinalizedDate)

For better performance as it's better to not use functions in WHERE clause, there are some other alternatives like CASE or UNION to achieve this.

Upvotes: 0

Related Questions