February 7, 2011 at 10:29 am
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.
February 7, 2011 at 10:52 am
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.
February 7, 2011 at 11:01 am
Thanks for this information. The IF - THEN - ELSE statements seemed to have done the trick.
February 7, 2011 at 3:19 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply