October 21, 2009 at 12:44 pm
I am a new bee to SQL Server. Please help me.
I have a table like
OrderNo OrderDate Cost PreviousCost NewCOst
1 10/20/2009 100 10 0
2 10/19/2009 200 0 200
1 10/20/2009 10 0 10
If the same orderno exists in previous month, then update the precost column with previous month cost other wise update the newcost column
Thanks.
007
October 21, 2009 at 1:09 pm
I think requirement is bit incomplete,
you have a orderdate column, What if there are multiple orders in the previous month and what if there are multiple records in the current month. Do you want to update the cumulative cost of the orders in the previous month? Please provide create table script, some more test data in insert format and bit more clarity on requirement. Thanks.
---------------------------------------------------------------------------------
October 21, 2009 at 1:23 pm
Ignore the previous question. Here is what I need.
If the orderno exists in immediate previous month, then update the previousCost column with cost other wise update the newcost column with cost.
Thanks.
Srini
October 21, 2009 at 2:25 pm
Will this do??
DROP TABLE ORDERS
CREATE TABLE ORDERS(OrderNo int, OrderDate datetime, Cost int, PreviousCost int, NewCost int)
Delete from Orders
INSERT INTO ORDERS VALUES(1, '2009-09-19', 100,0,0)
INSERT INTO ORDERS VALUES(2, '2009-10-19', 200,0,0)
INSERT INTO ORDERS VALUES(1, '2009-10-19', 10,0,0)
Select * from ORDERS
UPDATE O1
SET O1.PreviousCost = O1.COST
-- SELECT *
FROM ORDERS O1
JOIN ORDERS O2
ON O2.ORDERNO = O1.OrderNo
AND DatePart(m,O1.OrderDate) = DatePart(m,O2.OrderDate) +1
UPDATE O
SET NewCost = COST -- Select *
from ORDERS O
Where PreviousCost = 0
Select * from ORDERS
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply