CODEWITHSUNDEEP
sqlsql-serverreporting-servicesparametersreportbuilder
Grumpy Guard
Grumpy Guard

Reputation: 59

Conditional WHERE according to Parameter

I created a report but I've stopped on a hurdle that may be very simple for You but I can't think about any method that would let me solve my problem.

I have created a report based on SQL query (with multiple CTE) that should be parametrized by like that:

where
deliverydate between @FromDate and @ToDate
and
carrierid = @Carrier

And it's working so far. But I want to create another parameter that'll have 3 values. And depending on those values I want to get all the records that will meet the condition. Sorta like that:

  1. when parameter value = 1 then I want to get every record
  2. when parameter value = 2 then I want every record where column1 <> column2
  3. when parameter value = 3 then I want to get every record where column1 = column2

Column1 and column2 are columns from the report that I created. The final select looks like that:

SELECT  SALESID ,
        CARRIERID ,
        dlvmodeid ,
        SUM(totalweight) AS totalweight ,
        SUM(totalcharges) AS totalcharges ,
        TOTALCHARGESCURRENCY ,
        SUM(HowManyPackagesForThisSO) AS HowManyPackagesForThisSO ,
        SUM(HowManyPackagesForThisSO_st) AS HowManyPackagesForThisSO_st ,
        SUM(InHowManyDays) AS InHowManyDays ,
        SUM(cspjnumber) AS HowManyPackingSlip
FROM    countingcte
WHERE   deliverydate BETWEEN @FromDate AND @ToDate
        AND carrierid = @Carrier
GROUP BY SALESID ,
        CARRIERID ,
        dlvmodeid ,
        TOTALCHARGESCURRENCY

Does anyone know a solution for my problem?

The exact columns that I have in mind are HowManyPackagesForThisSO and HowManyPackagesForThisSO_st.

Upvotes: 1

Views: 72

Answers (1)

sagi
sagi

Reputation: 40481

Add this to your WHERE clause :

AND ((@Param = 1) OR
     (@Param = 2 AND col1 <> col2) OR
     (@Param = 3 AND col1 = col2))

Upvotes: 3

Related Questions