Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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
0
Errol Farro
Asked:
Errol Farro
  • 2
1 Solution
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now