Case Statement Help!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case Statement Help!!

Author

Topic

burkhark
Starting Member

1 Post

Posted - 2011-02-08 : 17:19:23

I am trying to separate orders into categories based on their size. However, when the results get returned to me it says everything is $300 or more, which I know is not true. Help please!!!

Thanks

select SUM(od.OrderTally) as NumberofOrders,
case
when isf.retailamount <= 100 then '$100 or less'
when isf.retailamount between 100 and 125.01 then '$100 - $125'
when isf.retailamount between 125 and 150.01 then '$125 - $150'
when isf.retailamount between 150 and 175.01 then '$150 - $175'
when isf.retailamount between 175 and 200.01 then '$175 - $200'
when isf.retailamount between 200 and 225.01 then '$200 - $225'
when isf.retailamount between 225 and 250.01 then '$225 - $250'
when isf.retailamount between 250 and 275.01 then '$250 - $275'
when isf.retailamount between 275 and 300.01 then '$275 - $300'
when isf.retailamount >= 300.01 then '$300 or More'
else 'Unknown'
end as SalesAmount
from ItemSalesFact as isf
inner join OrderDim as od
on od.OrderKey = isf.OrderKey
where od.OrderDate between '09-14-2010' and '10-16-2010'
group by isf.RetailAmount
order by SalesAmount;

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 17:51:14

What is the datatype of isf.retailamount?

What does this return?

SELECT MIN(retailamount),Max(retailamount)
FROM ItemSalesFact

You will have some overlap in those case statements by the way..250.01 > 250 which would cause some records to appear in both results.(if it were working properly)


case isf.retailamount
			when  <= 100 then '$100 or less'
			when between 100.01 and 125 then '$100 - $125'
			when between 125.01 and 150 then '$125 - $150'
			when between 150.01 and 175 then '$150 - $175'
			when between 175.01 and 200 then '$175 - $200'
			when between 200.01 and 225 then '$200 - $225'
			when between 225.01 and 250 then '$225 - $250'
			when between 250.01 and 275 then '$250 - $275'
			when between 275.01 and 300 then '$275 - $300'
			when >= 300.01 then '$300 or More'
else 'Unknown'
end as SalesAmount

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources