• The first and the second query should give you the same results.

    I just did some consolidation. All the "magic" is in the "on" criteria.

    Pretty sure it can be done without the CTE and possibly without subquerys but I can't wrap my head around that at the moment since I've forced myself to always think in CTEs. haha

    As for adding an update statement, try this:

    ;

    WITH previousweek

    AS ( SELECT StoreProdID

    , sp.ListID

    , ProdID

    , Qty

    , PrevQty

    , GroupID

    , StoreID

    FROM #StoreProducts sp

    JOIN #StoreList sl

    ON sl.ListID = sp.ListID

    ) ,

    thisweek

    AS ( SELECT StoreProdID

    , sp.ListID

    , ProdID

    , Qty

    , PrevQty

    , GroupID

    , StoreID

    FROM #StoreProducts sp

    JOIN #StoreList sl

    ON sl.ListID = sp.ListID)

    UPDATE tw SET tw.prevqty=pw.qty

    FROM previousweek pw

    JOIN thisweek tw

    ON pw.ProdID = tw.ProdID

    AND pw.StoreID = tw.StoreID

    AND tw.GroupID - 1 = pw.GroupID