conditional value - 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

SQL Server 2005 Forums

Transact-SQL (2005)

conditional value

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-08 : 00:34:27

Hi

I have a table that contains 4 columns..


ID (PK)  Price (Decimal)  Qty (int)  IsRange (Bit)

I need to do a conditional calculation based on wheather the IsRange is true or false. So if the IsRange is False I want to calculate like this..

Price * Qty AS SubTotal, but if IsRange is True I need it to be Price AS SubTotal, How would a query like that look like ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-08 : 00:58:33

[code]
select
case
when IsRange = 'true' then Price
else Price * Qty
end as SubTotal
from table
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-10 : 02:20:42

Great, Thanks!

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-27 : 07:54:53

Hi

I need to make a modification to my exsisting query with the code above, but I dont get it right. My query look like this...


SELECT     TOP (100) PERCENT dbo.tbl_Customer.CustName, SUM(dbo.tbl_Order.Quantity * dbo.tbl_Order.ProductPrice) AS TotalPrice, DATENAME(month, 
                      dbo.tbl_Order.DateOrdered) AS MonthName, DATENAME(year, dbo.tbl_Order.DateOrdered) AS Year
FROM         dbo.tbl_Customer INNER JOIN
                      dbo.tbl_Login ON dbo.tbl_Customer.CustID = dbo.tbl_Login.CustID INNER JOIN
                      dbo.tbl_Order ON dbo.tbl_Login.UID = dbo.tbl_Order.UserUniqueID
WHERE     (dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2011-01-01', 102) AND CONVERT(DATETIME, '2011-03-31', 102))

GROUP BY dbo.tbl_Customer.CustName, DATENAME(mm, dbo.tbl_Order.DateOrdered), DATENAME(year, dbo.tbl_Order.DateOrdered), MONTH(dbo.tbl_Order.DateOrdered)
ORDER BY Year, MONTH(dbo.tbl_Order.DateOrdered)

How do I change so that I can use the SUM in this..


select
case
  when dbo.tbl_Products.UsePriceRange = 'true' then dbo.tbl_Order.PriceSubTotal
  else dbo.tbl_Order.ProductPrice * dbo.tbl_Order.Quantity
end as SubTotal
from table

instead of this..


SUM(dbo.tbl_Order.Quantity * dbo.tbl_Order.ProductPrice) AS TotalPrice

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-27 : 08:06:15

You can actually just sum that up
select
SUM
(
case
when dbo.tbl_Products.UsePriceRange = 'true' then dbo.tbl_Order.PriceSubTotal
else dbo.tbl_Order.ProductPrice * dbo.tbl_Order.Quantity
end
)as SubTotal

Jim
from table

Everyday I learn something that somebody else already knew

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-27 : 08:31:08

Thanks Jim, that worked just perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources