Can I use complex CASE conditioning in SQL 2005 with the SUM funtion?

Hi All,

I'm trying to run the following sql query but I get an error msg. What am I doing wrong?


SELECT Owner.OwnerID, Owner.OwnerNumber,

sum
(case
when SoldInventory.RoomType='Superior' and SoldInventory.Week>=15 and SoldInventory.Week<=50 then 22
else
when SoldInventory.RoomType='Deluxe' and SoldInventory.Week>=15 and SoldInventory.Week<=50 then 26
else
when SoldInventory.RoomType='Royal' and SoldInventory.Week>=15 and SoldInventory.Week<=50 then 32
else
when SoldInventory.RoomType='Superior' then 35
else
when SoldInventory.RoomType='Deluxe' then 39
else  
when SoldInventory.RoomType='Royal' then 45
else 0
end) as VotesABC

FROM   Owner AS Owner INNER JOIN Contract AS Contract
ON Owner.OwnerID = Contract.OwnerID
INNER JOIN SoldInventory AS SoldInventory
ON Contract.ContractID = SoldInventory.ContractID

WHERE     (Contract.ContractStatus = 'Active')

group BY Owner.OwnerID, Owner.OwnerNumber

Thanks for your help.

Errol
Errol FarroAsked:
Who is Participating?
 
DcpKingCommented:
Telling us the error message would have been nice. However, you might try leaving out all but one of the "else" lines:
select	Owner.OwnerID, 
		Owner.OwnerNumber, 
		sum	(	case 
					when si.RoomType='Superior'	and si.Week>=15 and si.Week<=50 	then 22 
					when si.RoomType='Deluxe' 	and si.Week>=15 and si.Week<=50 	then 26
					when si.RoomType='Royal' 	and si.Week>=15 and si.Week<=50 	then 32
					when si.RoomType='Superior' 	then 35
					when si.RoomType='Deluxe' 	then 39
					when si.RoomType='Royal' 	then 45
					else 0 
				end) as 'VotesABC'
from   dbo.Owner
inner joinContract			on Owner.OwnerID = Contract.OwnerID 
inner join SoldInventory si		on Contract.ContractID = si.ContractID
where ( Contract.ContractStatus = 'Active'	)
group by Owner.OwnerID, Owner.OwnerNumber

Open in new window


hth

Mike
0
 
Errol FarroAuthor Commented:
Thanks a lot Mike. That took care of the errors. Next time I will add the actual errors. Best regards. Errol
0
 
DcpKingCommented:
Good to hear!

Mike

P.S. sorry about the missing space :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.