SQL Server Performance Forum – Threads Archive

Query Question

Here is what I am trying to do: Select CID, CName,
Profit =
(
If tblC.CampaignType = 1
Select SUM((Price1 – Price2) * Impressions) as Profit From tblR Where tblR.CID = tblC.CID
else
Select SUM((Price1 – Price2) * Clicks) as Profit From tblR Where tblR.CID = tblC.CID
)
From tblC Query analyzer doesn’t like that If statement. Is there another way I can perform this same logic?
This does work, but there is a different profit value depending on the CampaignType: Select CID, CName,
Profit =
(
Select SUM((Price1 – Price2) * Impressions) as Profit From tblR Where tblR.CID = tblC.CID
)
From tblC Any input will be appreciated.

Use CASE instead. All the details are in Books Online. Tom Pullen
DBA, Oxfam GB

I haven’t used the CASE before, but this is what I am lookin for…this works: Select CID, CName,
CASE tblC.CampaignType
WHEN 0 THEN ‘Impressions’
WHEN 1 THEN ‘Clicks’
END As Profit
From tblC
but I need to select from a different table..and I can not get that to work. Here is what I am trying to do:
Select CID, CName,
CASE tblC.CampaignType
WHEN 0 THEN Select SUM((Price1 – Price2) * Impressions) as Profit From tblR Where tblR.CID = tblC.CID
WHEN 1 THEN Select SUM((Price1 – Price2) * Clicks) as Profit From tblR Where tblR.CID = tblC.CID
END As Profit
From tblC

try:
Select
c.CID, c.CName,
sum((r.price1 – r.price2) * case (c.CampaignType)
when 1 then r.clicks
when 0 then r.Impressions
end
) as Profit
From
tblC c join
tblR r on r.CID = c.CID
group by
c.CID,
c.CName,
c.CampaignType
This is just one possible way to write it.

I figured it out…. it was: Select CID, CName,
CASE tblC.CampaignType
WHEN 0 THEN (Select SUM((Price1 – Price2) * Impressions) as Profit From tblR Where tblR.CID = tblC.CID)
WHEN 1 THEN (Select SUM((Price1 – Price2) * Clicks) as Profit From tblR Where tblR.CID = tblC.CID)
END As Profit
From tblC I was missing the ‘()’ around me Select Statement.

>sum((r.price1 – r.price2) * case (c.CampaignType) >when 1 then r.clicks >when 0 then r.Impressions >end Using CASE within expression… It is a good method. Thanks for this trick. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] /* SKChandra */

]]>