M3How M3How - 16 days ago 4
SQL Question

Conditional WHERE according to Paramater

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.

Answer

Add this to your WHERE clause :

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