0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

CREATE TABLE #Data (    
    Prod VARCHAR(20)
    ,SDate DATE
    ,OStock INT
    ,CStock INT
    )

CREATE TABLE #BackData (
    Prod VARCHAR(20)
    ,SDate DATE
    ,CStock INT
    )


INSERT INTO #Data
SELECT 'p1', '2016-06-06', 10, 10
UNION ALL
SELECT 'p1', '2016-06-07', 10, 14
UNION ALL
SELECT 'p1', '2016-06-08', 14, 13
UNION ALL
SELECT 'p1', '2016-06-09', 13, 13
UNION ALL
SELECT 'p1', '2016-06-10', 13, 11

INSERT INTO #BackData
SELECT 'p1', '2016-06-06', 2
UNION ALL
SELECT 'p1', '2016-06-07', 4
UNION ALL
SELECT 'p1', '2016-06-09', -1
UNION ALL
SELECT 'p1', '2016-06-10', -2

;WITH ToUpdate AS (
   SELECT d1.OStock, d1.CStock,
          COALESCE(LAG(d2.CStock2) OVER (PARTITION BY d2.Prod 
                                         ORDER BY d2.SDate),
                   d1.OStock) AS OStock2,
          d2.CStock2  
   FROM #Data AS d1
   JOIN (
      SELECT d.Prod, d.SDate, d.OStock, d.CStock, 
             COALESCE(t.newCStock, 
                      LAG(t.newCStock) OVER (PARTITION BY d.Prod 
                                             ORDER BY d.SDate)) AS CStock2
      FROM #Data AS d
      LEFT JOIN (
         SELECT bd.Prod, bd.SDate, 
                drn.CStock + SUM(bd.CStock) OVER (PARTITION BY bd.Prod 
                                                  ORDER BY bd.SDate) AS newCStock
         FROM #BackData AS bd
         INNER JOIN (
            SELECT Prod, CStock, 
                   ROW_NUMBER() OVER (PARTITION BY Prod ORDER BY SDate) AS rn
            FROM #Data
         ) AS drn ON bd.Prod = drn.Prod AND drn.rn = 1
      ) AS t ON t.Prod = d.Prod AND t.SDate = d.SDate
   ) AS d2 ON d1.Prod = d2.Prod AND d1.SDate= d2.SDate
)
UPDATE ToUpdate
SET OStock = OStock2,
    CStock = CStock2
    
SELECT *
FROM #Data

fork query

edited dec 13 18

created jun 12 16 anonymous