MrBawsEnough MrBawsEnough - 8 days ago 7
SQL Question

Stored procedure giving "discount" based on X number of months from todays date

So my problem is that I have to make a procedure that will multiply all prices in my product table with 0.8 if the product haven't sold for X number of months.

At the moment I can't seem to get any further than this:

GO
CREATE PROC newprice(@numberofmonth int)
AS
BEGIN
DECLARE @today datetime
SET @today = GetDate()
SELECT product.productid, product.name
FROM orders JOIN orderitem on orderitem.orderid = orders.orderid
JOIN product on product.productid = orderitem.productid
WHERE orders.orderdate > (SELECT DATEADD(month, -@numberofmonth, @today))

UPDATE product set price = price * 0.8 where
END


Hope everything is transparent enough to read and understand without any further description. I'm working with SQL Server.

Answer

I'm not understand your problem completly.i just assume your need look this

    GO CREATE PROC newprice(@numberofmonth int) 
AS 
BEGIN 
DECLARE @today datetime 
SET @today = GetDate() 
UPDATE product set price = price * 0.8 FROM orders JOIN orderitem on orderitem.orderid = orders.orderid JOIN product on product.productid = orderitem.productid WHERE orders.orderdate >
(SELECT DATEADD(month, -@numberofmonth, @today))   
END