Conditional Where statement

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  February 7, 2011 at 10:29 am

  #388795

  I have a situation where I am getting values from a couple of views and feeding them into an SSRS report. In my query, we have one case where if it is a certain product, that product has to be split (this condition does not affect other products).

  I have the following:

  declare @product as char(10)

  declare @thisDate as datetime

  set @product='SHDS'

  set @thisDate='2/6/11'

  select d.product

  ,s.[group]

  ,round(sum(d.wt1),0) as lbs1

  ,round(sum(d.wt2),0) as lbs2

  ,round(sum(d.wt3),0) as lbs3

  ,round((sum(d.wt1) + sum(d.wt2) + sum(d.wt3)),0) as DailyLBS

  ,round(s.SchedWt,0) as Scheduled

  ,round(((sum(d.wt1) + sum(d.wt2) + sum(d.wt3))-(s.SchedWt)),0) as LBSVariance

  from vw_daily_lbs d

  inner join vw_adhoc_lbs_ProductDay s on d.[date]=s.[date] and d.product=s.product

  where d.[date]=@thisDate

  and

  CASE @Product

  WHEN 'SHDS' THEN (d.resource='FAM201' or d.resource='FAM202')

  WHEN 'SHDN' THEN (d.resource='FAM203' or d.resource='FAM204')

  ELSE 1

  END

  group by d.product, s.[group], s.SchedWt

  @product is an internal variable and when I call the procedure, I will pass the value to it. My WHERE statement would be conditional only if @product='SHDN' or @product='SHDS', otherwise I do not need to append to the original WHERE statement (for the date).

  The set statements are being used just for testing purposes.

  Thanks for any information and help with this. I think I am on the right track, but just need some help here.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  February 7, 2011 at 10:52 am

  #1283309

  You're probably going to be better off in this case by just doing something like :

  IF @product = 'SHDS'

  Select ...

  WHERE d.resource IN ('FAM201','FAM202')

  ELSE IF @Product = 'SHDN'

  SELECT ...

  WHERE d.resource IN ('FAM203','FAM204')

  ELSE

  SELECT ...

  While you could write it in one statement, I think you're going to risk bad execution plans depending on data distribution.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup.

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  February 7, 2011 at 11:01 am

  #1283317

  Thanks for this information. The IF - THEN - ELSE statements seemed to have done the trick.

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  February 7, 2011 at 3:19 pm

  #1283462

  The problem with your original CASE statement is that you were trying to return a Boolean value and T-SQL won't let you return a Boolean value. You can restructure your CASE statement to return non-Boolean values:

  CASE WHEN @Product = 'SHDS' AND (d.resource='FAM201' or d.resource='FAM202') THEN 1

  WHEN @Product = 'SHDS' THEN 0

  WHEN @Product = 'SHDN' AND (d.resource='FAM203' or d.resource='FAM204') THEN 1

  WHEN @Product = 'SHDN' THEN 0

  ELSE 1

  END

  Either approach will work in this case, but there are other cases where this approach may be more efficient.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA