SQL If statement

triggerthat · 5 Dec 2006 at 18:11

I want to basically find out if the count of something is more than 3, then apply a discount. I am creating a view.

Can anyone help?

oneilldo · 5 Dec 2006 at 18:42

DECLARE @cnt INT
SET @cnt = (SELECT COUNT(1) FROM TableName WHERE id = ??)
IF(@cnt>3)
BEGIN
.......
.....apply your discount
END

triggerthat · 5 Dec 2006 at 20:17

Remove.

oneilldo · 7 Dec 2006 at 18:20

sort of.

however you need to specify what table and what value to update if the clause is true. Copy the data and have a play with the copied data.
Are you trying to give repeat customers a discount?
If so you need a link between the price and that specific customer. i.e. you might have a table called orders that had customer_id, price,etcetc.

DECLARE @cnt INT
SET @cnt = (SELECT COUNT(1) FROM customer WHERE customer_ID = 20)
IF(@cnt>3)
BEGIN
UPDATE Order set Price = (Price*0.80) WHERE Customer_ID = 20
END

triggerthat · 7 Dec 2006 at 18:42

I don't want to change any items in the table just as yet.

Btw, in my middle statement, does that satify the 'inner join' clause?

Also, you said customer_ID = 20, I want to evaluate all customers.

oneilldo · 7 Dec 2006 at 19:11

Yeah that was just for one example. I am still unsure how the tables link together. You could try it as follows using the keyword having

Code:

select customer.CUSTOMER, customer.customer_contact, customer.CUSTOMER_ID, order_item.order_number, sum((belt_design.PRICE * order_specification.order_quantity)) as Total_Price_Of_Orders, COUNT(1)
INTO TmpTableName
from customer, order_item, belt_design, order_specification
where customer.CUSTOMER_ID = order_item.CUSTOMER_ID 
AND ORDER_ITEM.ORDER_NUMBER = order_specification.ORDER_NUMBER
and order_specification.DESIGN_NUMBER = belt_design.DESIGN_NUMBER
group by customer.CUSTOMER, customer.customer_contact, customer.CUSTOMER_ID, order_item.order_number
HAVING COUNT(1)>3
order by customer.customer;


UPDATE TableNameWithPriceFieldIn
FROM TmpTableName tmp
SET TableNameWithPriceFieldIn.Price = (TableNameWithPriceFieldIn.Price*0.80)
WHERE TableNameWithPriceFieldIn.customer_id = tmp.customer_id
AND TableNameWithPriceFieldIn.order_number = tmp.order_number

triggerthat · 8 Dec 2006 at 01:00

There won't be a field that has a final value. The price is a composition of quantity and price. Both are in different tables. I wanted to satisfy if there is more than 2 or 3 orders, then show their final price with a discount.