yep, that data structure is really confusing

Can you give an example of the expected output?

Or would the insert go into the #storeproducts table?

try this and let me know if it helps at all, if so you would just have to add your insert statments

;

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)

SELECT tw.ListID

, tw.ProdID

, tw.Qty

, pw.qty AS PrevQty

FROM previousweek pw

JOIN thisweek tw

ON pw.ProdID = tw.ProdID

AND pw.StoreID = tw.StoreID

AND tw.GroupID - 1 = pw.GroupID