February 7, 2006 at 7:13 am
Hi there,
please help if you can, Ive got stuck on a task. Imagine the following Orders table:
OrderID....Date...Description.......Price
1.............01/02...Product A........£15.99
2.............02/02...Product B........£23.99
3.............03/02...Product C.........£5.45
I need the price total of orders from the above, however. I need to through in a specials company discounts calculation.
For example: any order above £10 gets a 10% discount. Anything over £20 gets a 15% discount.
If I do SELECT SUM(Price) as Total, its not going to allow me to through in my criteria.
Is there a way around this? I think what I need is an IF statement in the middle of that SUM statement but unsure how to do it.
Or
What if I did a query to filter orders out Over £20, and then another to filter orders over £10, which would have their own calculations for discounts, and then add the results together.
I think the later is going suffer with performance issues. And is maybea very long way around it surely.
Any response gratefully appreciated.
Andy
February 7, 2006 at 7:30 am
First thought would be to use a CASE statement in your SELECT. Something Like this:
SELECT ORDER_ID,
CASE WHEN SUM(ORDER_PRICE)<10 THEN SUM(ORDER_PRICE)
WHEN SUM(ORDER_PRICE)>10 AND SUM(ORDER_PRICE)<20 THEN (SUM(ORDER_PRICE)-(ORDER_PRICE * .10))
WHEN SUM(ORDER_PRICE)>0 THEN (SUM(ORDER_PRICE)-(ORDER_PRICE * .20))
END
FROM ORDERS
GROUP BY ORDER_ID,ORDER_PRICE
February 7, 2006 at 7:35 am
How about the following:
select sum(case when price >= 20 then price*1.15 when price >= 10 then price*1.1 else price end) from Orders
February 7, 2006 at 7:36 am
This is the standard boundary value problem in SQL Server. Make sure you understand what is meant by "open" and "closed' interval or you run the risk of dubling some reductions.
declare @order table
(
Order_id int identity(1,1) primary key,
[Date] datetime,
[Description] varchar(100),
[Price] numeric(10,2)
)
declare @discount table
(
closed_interval numeric(10,2),
open_interval numeric(10,2),
discount_rate numeric(10,2)--must be a ratio
)
insert @order([Date],[Description],[Price])
select '01/02/2006','Product A',15.99
union all
select '02/02/2006','Product B',23.99
union all
select '03/02/2006','Product C',5.45
--insert your discount table here..probably want a "real" table for this
insert @discount
select 0.00,10.00,0.00
union all
select 10.00,20.00,.10
union all
select 20.00,999999,.20
select o.Order_id,o.[Date],o.[Description],o.[Price],convert(numeric(10,2),(1-d.discount_rate)*o.[Price]) Discounted_Price
from @order o
join @discount d
on d.closed_interval <= o.[Price]
and d.open_interval > o.[Price]
HTH
Mathew J Kulangara
sqladventures.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply