finding discontiguous numbers
754172Apr 9 2010 — edited Apr 19 2010 Greetings,
I need to write a plsql scripts that will find discontiguous numbers. For example, say I have a table..
id int
store varchar
tracking_num int
Each store uses a different sequence for their tracking number. ii need to find and list the contiguous segments for each store, and the total number of items shipped. For example..
STORE MIN MAX
----------------------------------------------------------
STORE X 100 105
STORE X 107 110
STORE X 112 115
---------------------------------------------------------
STORE X TOTAL 14
STORE Y 901 903
STORE Y 905 907
STORE Y 908 910
---------------------------------------------------------
STORE X TOTAL 9
OK, I figure I need a cursor to be able to do this. I figure I'll loop through each store's records, increment a counter for the total. For the discontinuous I figure I'll add a new column which I'll use to group the contiguous segments together, then I can just a max and min tracking number for each store's contiguous groups.
The problem I'm having is wrapping my head around how to build a dataset with a cursor populating the new contiguous column IN MEMORY, in an line view or something, without having to create a physical table.
Anyone have any suggestions, or see any flaws with this logic?