Clever Geek Handbook

SQL - Create a conditional Where clause for this simple query

I'm trying to create a conditional where clause for my query below, but I see so many alternatives all the time, I'm not sure what to use in this case.

I need something like this: (although of course this code is wrong)

where casCaseType='m'
  and casCurrentWorkflowID=990
  and cmsDateCreated between @FromDate and @ToDate

 CASE @WFStatus
      WHEN @WFStatus=1 then eveworkflowID<100
      WHEN @WFStatus=2 then eveworkflowID<200
      WHEN @WFStatus=3 then eveworkflowID<300
      WHEN @WFStatus=4 then eveworkflowID<400
 ELSE 0
 END

So when I select the WFStatus parameter as 1, it automatically uses that section of the where clause, outputting only those results with eveworkflowID less than 100.

Any help would be greatly appreciated!

thank

+3


source to share


4 answers 


WHERE casCaseType='m'
  AND casCurrentWorkflowID=990
  AND cmsDateCreated between @FromDate and @ToDate

  AND eveworkflowID < 
      CASE @WFStatus
        WHEN 1 THEN 100
        WHEN 2 THEN 200
        WHEN 3 THEN 300
        WHEN 4 THEN 400
        ELSE 0
      END


+5


source


I'm not sure, but if I understand correctly: ... AND eveworkflowID < @WFStatus * 100



+2


source 


where casCaseType='m'
    and casCurrentWorkflowID=990
    and cmsDateCreated between @FromDate and @ToDate
    and (@WFStatus BETWEEN 1 AND 4 AND eveworkflow < @WFStatus * 100)

EDIT
Didn't mean the case @WFStatus

is not between 1 and 4, which is covered by the default case.

+1


source 


where casCaseType='m'
and casCurrentWorkflowID=990
and cmsDateCreated between @FromDate and @ToDate

  CASE eveworkflowID<
  WHEN @WFStatus=1 then 100
  WHEN @WFStatus=2 then 200
  WHEN @WFStatus=3 then 300
  WHEN @WFStatus=4 then 400
 ELSE 0
END
0


source






More articles:


All Articles