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