Unanswered: Differential Data Merge is complex? How to get results?
I am looking at an old stored procedure that's job is to preserve the New sort order based on yesterday's and today's data.
Sort orders are not being preserved any longer and I have narrowed it down to the WHERE clause eliminating all rows. The main goal is to preserve the SortOrder so if some custom data was in position 4 yesterday, any NEW custom data that takes its place should ALSO have position 4.
If I eliminate
--AND b.PrimaryID = b.SortOrder
then I get thousands of rows. I suspect something is wrong but it I am not understanding. How can I make this simpler so it is REALLY easy to understand?
IMPORTANT: the SortOrder actually equals the PrimaryID if the data is no longer sorted. Otherwise it is incremental 1 2 3 4 5 6 7 .. and so on. I guess this was the original architects way of doing it.
-- Merge data and get missing rows that have not changed.
PrevPrimaryID = a.PrimaryID
,PrevSortOrder = a.SortOrder
,NewPrimaryID = b.PrimaryID
,NewDataID = b.DataID
,NewStartDate = b.StartDate
,NewSortOrder = b.SortOrder
FROM #YesterdaysData2 a
LEFT JOIN #TodaysData2 b ON a.WidgetID = b.WidgetID
AND a.AnotherValue = b.AnotherValue
a.Primaryid <> a.sortorder
AND b.PrimaryID = b.SortOrder