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

Question

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 1

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