0

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.
SELECT 
    PrevPrimaryID = a.PrimaryID
    ,a.WidgetID
    ,a.AnotherValue
    ,a.DataID
    ,PrevSortOrder = a.SortOrder
    ,NewPrimaryID = b.PrimaryID
    ,NewDataID = b.DataID
    ,NewStartDate = b.StartDate
    ,NewSortOrder = b.SortOrder
INTO #NewOrder2
FROM #YesterdaysData2 a
LEFT JOIN #TodaysData2 b ON a.WidgetID = b.WidgetID
                         AND a.AnotherValue = b.AnotherValue
WHERE 
   a.Primaryid <> a.sortorder
   AND b.PrimaryID = b.SortOrder

SELECT * FROM #NewOrder2 

-- later update based on #NewOrder2...
UPDATE CustomerData
    SET SortOrder = (
        SELECT PrevSortOrder
        FROM #NewOrder2
        WHERE CustomerData.PrimaryID = #NewOrder2.NewPrimaryID
        )
    WHERE PrimaryID IN (
        SELECT NewPrimaryID
        FROM #NewOrder2
        )

UPDATE - Is it possible its just a blunder and the WHERE clause should be 

WHERE a.Primaryid <> a.sortorder
AND b.PrimaryID <> b.SortOrder
JoJo
  • 4,643
  • 9
  • 42
  • 65
  • 1
    How is `#TodaysData2` getting populated? Does it have data in it? – Andrew Jul 23 '14 at 15:51
  • @Andrew #TodaysData2 is being populated with a customData SELECT from non-temp tables and I have verified contains over 10,000 records. – JoJo Jul 23 '14 at 16:54
  • 1
    is sortorder = null when a new record is created? why not check to see if the field is set and only sort it when it's null? – Kevin Cook Jul 23 '14 at 19:46
  • @KevinCook .. looking at the SPROC that Adds Data which runs 2X a day and the SPROC that Resets the SortOrder on Data.. it appears that a NULL Is in fact inserted. But why run the SPROCS at diff intervals? – JoJo Jul 23 '14 at 20:04
  • 1
    that would probably be a business requirement of some sort. maybe they wanted to refresh the data every 12 hours? that said, I would just check to see if sortorder is null (new record) and only update those and leave the rest alone until sortorder <> prevsortorder and then that meant the customer moved it around. – Kevin Cook Jul 23 '14 at 20:19

0 Answers0