How to find missing records in foxpro table?

Question

I have a table like below.

cAcctNumber	nSerialNo	cBatchNo
12345	120	456
12345	122	456
12345	123	456
12345	125	456
12345	100	500
12345	105	500
12345	106	500
12345	108	500

Now I want my output like this,

nSerialNo
121
124
101
102
103
104
107

I did something like this,

Select cAcctNumber, cBatchNo, nSerialNo , ;
   Min(nSerialNo) as minCN,;
   Max(nSerialNo) as maxCN,; 
   Count(*) as BatchCount; 
   From Cheque_no Group By cAcctNumber, cBatchNo, nSerialNo ;
   Into Cursor Batches  READWRITE 


Select Batches 
Go Top
nMin = minCN
Go Bottom
nMax = maxCN
nMaxRecno = Recno()


Create Cursor MissingChequeno  (nSerialNo I)

For I = nMin+1 To nMax-1
   Try
      Insert Into Batches Values (I)
      
      Insert Into MissingChequeno  Values (I)
   Catch 
   Endtry
ENDFOR 

Need to get my missing records according to every cBatchNo . It means Batch 456 missing ** nSerialNo 121,nSerialNo 124** and Batch 456 missing nSerialNo 101,nSerialNo 102,nSerialNo 103,nSerialNo 104,nSerialNo 107 . How can I get my output? Thank you

Answer 1

There are N ways to do that. One of them is:

Create Cursor Cheque_no ;
    (cAcctNumber i,nSerialNo i,cBatchNo i)
Insert Into Cheque_no Values(12345,120,456)
Insert Into Cheque_no Values(12345,122,456)
Insert Into Cheque_no Values(12345,123,456)
Insert Into Cheque_no Values(12345,125,456)
Insert Into Cheque_no Values(12345,100,500)
Insert Into Cheque_no Values(12345,105,500)
Insert Into Cheque_no Values(12345,106,500)
Insert Into Cheque_no Values(12345,108,500)




Local Array laNums[1]
Select Min(nSerialNo), Max(nSerialNo) From Cheque_no Into Array laNums
Local ix
Create Cursor allNums(num i)
For ix = laNums[1] To laNums[2]
    Insert Into allNums Values (m.ix)
Endfor

Select N.num As nSerialNo ;
    From allNums N ;
    where Not Exists (Select * From myTable T Where T.nSerialNo = N.num) ;
    into Cursor MissingChequeno nofilter

Browse