Topic
  • 1 reply
  • Latest Post - ‏2013-04-04T02:43:58Z by PWConner
Junius
Junius
17 Posts

Pinned topic How do I find missing/skipped number in a sequence of number

‏2013-04-03T22:12:52Z |
I've created a list of transaction numbers for all of our 150 POS registers for a two week period of time (called tranlist) for the purpose of finding any missing/skipped transaction numbers. For example, for store #1, register #1 the numbers in the file might go from 13567 to 14100, followed by register #2 which might have numbers 23114 to 23566, and so on.

I've tried a recursive select (CTEs) which doesn't work (probably because it's my first real world example). :-\
With
-- select first row as the "seed" row
FirstTran (Str,Reg,Tran#,TranDt, Nxtran) as
(select Hstr#, Hreg#, Htran#, Htrandt, Htran#+1 as NextTran
from session/tranlist as A
where rrn(A) = 1
UNION ALL
select Hstr#, Hreg#, Htran#, Htrandt, Htran#+1 as NextTran
from session/tranlist as TL, FirstTran as FT
where FT.Nxtran dec(TL.htran#) )

select Str, Reg, Tran#, TranDt, NxTran
from FirstTran ;;

Since each register at each store has it's own set of numbers, I would expect my result list to have the truly missing numbers and show the break in numbers when the sequence for one register ends and the next register begins. Instead, what I get is a list of all transactions for the first register in the table. :-(

Am I heading down the wrong path with the recursive CTEs? Is there an easier way? If this is a good way to solve the problem, what am I doing wrong??

I know I could do this fairly easily in RPG, but I'd like to find an SQL solution, if it is reasonable.
Updated on 2013-04-04T02:43:58Z at 2013-04-04T02:43:58Z by PWConner
  • PWConner
    PWConner
    44 Posts

    Re: How do I find missing/skipped number in a sequence of number

    ‏2013-04-04T02:43:58Z  
    Here is how I found missing numbers:

    create table tranList
    (
    hStr# integer,
    hReg# integer,
    hTran# integer
    );

    insert into tranList
    values
    ( 1, 1, 13567 ),
    ( 1, 1, 13568 ),
    ( 1, 1, 13569 ),
    ( 1, 1, 13571 ),
    ( 2, 1, 13114 ),
    ( 2, 1, 13116 ),
    ( 2, 1, 13117 );

    select *
    from tranList;

    With consecTran (str, reg, tran, maxTran ) as
    (
    Select hStr#, hReg#, min(hTran#), max(hTran#)
    from tranList
    group by hStr#, hReg#
    union all
    select str, reg, tran+1 tran, maxTran
    from consecTran
    where tran+1 <= maxTran
    )
    select *
    from consecTran
    exception join tranList
    on str = hStr# and reg = hReg# and tran = hTran#
    order by str, reg, tran;

    Results:
    1 1 13570 13571 null null null
    2 1 13115 13117 null null null

    If you want to see the whole list use a left join instead of an exception join.
    Results:
    1 1 13567 13571 1 1 13567
    1 1 13568 13571 1 1 13568
    1 1 13569 13571 1 1 13569
    1 1 13570 13571 null null null
    1 1 13571 13571 1 1 13571
    2 1 13114 13117 2 1 13114
    2 1 13115 13117 null null null
    2 1 13116 13117 2 1 13116
    2 1 13117 13117 2 1 13117

    You can clean up the code to get only the columns you want.