How to update a Previous Row data in SQL SERVER

Question

for example Sales Table

SlNo Inventorycode Cost Date

1.    100001       1.8   01/01/2017
2.    100002       2.3   01/01/2017
3.    100002       3.5   02/01/2017
4.    100001       2.5   03/01/2017

Stock Table

SlNO InventoryCode  Cost    Date
1.    100001              01/01/2017
2.    100002              01/01/2017
3.    100001              01/01/2017
4.    100002              02/01/2017
5.    100001              01/01/2017
6.    100002              03/01/2017

from the above data, I want to update the COST of the Salestable to stocktable for the concern ItemCode and date.If no sales occurred on the concern date i want to update(StockTable) the cost of the previous date's cost.

Answer 1

I haven't tested it but this should work

UPDATE stocktable
SET Cost = (
        SELECT TOP 1 Cost
        FROM SalesTable st
        WHERE st.Inventorycode = StockTable.Inventorycode
            AND st.DATE <= StockTable.DATE
        ORDER BY DATE DESC
        )
FROM StockTable

It would be nice if you could share the schema and data for quick testing

Answer 2

update st set cost = sl.cost from stock_table st
inner join sales_table sl on sl.Inventory_Code=st.Inventory_Code
and sl.Date = st.Date

Answer 3

Are you trying to do this:

DECLARE @Sales TABLE
(
    [SLNo] TINYINT
   ,[Inventorycode] INT
   ,[Cost] DECIMAL(9,1)
   ,[Date] DATE
);


DECLARE @Stock TABLE
(
    [SLNo] TINYINT
   ,[Inventorycode] INT
   ,[Cost] DECIMAL(9,1)
   ,[Date] DATE
);

INSERT INTO @Sales ([SLNo], [Inventorycode], [Cost], [Date])
VALUES (1, 100001, 1.8, '01/01/2017')
      ,(2, 100002, 2.3, '01/01/2017')
      ,(3, 100002, 3.5, '02/01/2017')
      ,(4, 100001, 2.5, '03/01/2017');

INSERT INTO @Stock ([SLNo], [Inventorycode], [Date])
VALUES (1, 100001, '01/01/2017')
      ,(2, 100002, '01/01/2017')
      ,(3, 100001, '01/01/2017')
      ,(4, 100002, '02/01/2017')
      ,(5, 100001, '01/01/2017')
      ,(6, 100002, '03/01/2017');

UPDATE @Stock
SET [Cost] = DS.[Cost]
FROM @Stock A
OUTER APPLY
(
    SELECT TOP 1 B.[Cost]
    FROM @Sales B
    WHERE B.[Inventorycode] = A.[Inventorycode]
        AND B.[Date] <= A.[Date]
    ORDER BY B.[Date] DESC
) DS;

SELECT *
FROM @Stock;