MB34 MB34 - 1 month ago 7
SQL Question

Out of Sequence numbers for today

I have a table that records transactions sent from our web service. I would like to obtain out of sequence numbers between one number and the last number in the table for that day.

This is a simplified structure:
Table name CARTREQUESTS

CR_ID
CR_DATE
CR_ORDER_ID
CR_CUSTOMERID


i.e.

CR_ID CR_DATE CR_ORDER_ID CR_CUSTOMERID
1540668 9/9/2016 11:59:52 AM 100148466 1087840
1540667 9/9/2016 11:58:49 AM 100148465 1191293
1540687 9/9/2016 12:23:07 PM 100148464 916470
1540647 9/9/2016 11:46:32 AM 100148463 918195
1540629 9/9/2016 11:45:08 AM 100148462 957676
1540628 9/9/2016 11:44:28 AM 100148461 1161585
1540627 9/9/2016 11:43:48 AM 100148460 956791
1540607 9/9/2016 11:40:47 AM 100148459 1161585
1540567 9/9/2016 11:36:07 AM 100148457 1127193
1540548 9/9/2016 11:30:42 AM 100148456 1204204
1540547 9/9/2016 11:29:11 AM 100148455 967946
1540527 9/9/2016 11:27:25 AM 100148454 1209116
1540507 9/9/2016 11:21:54 AM 100148453 1207376
1540488 9/9/2016 11:18:17 AM 100148452 1057770
1540467 9/9/2016 11:02:16 AM 100148450 1216001
1540448 9/9/2016 11:00:51 AM 100148449 1082302
1540447 9/9/2016 11:00:39 AM 100148448 1082298
1540427 9/9/2016 10:58:10 AM 100148447 951007
1540407 9/9/2016 10:56:10 AM 100148446 1209338
1540387 9/9/2016 10:51:08 AM 100148445 1205880
1540367 9/9/2016 10:39:12 AM 100148444 1061172
1540347 9/9/2016 10:34:37 AM 100148443 1215973


I need to know how I can enter a CR_ORDERID value of 100148443 and the query return these 2 order#s
100148458, 100148451

There are CR_ORDERID values greater than 100148466 at this time, I just want processed records for SYSDATE-1

Answer

Final working code:

select minid+(level-1) missingorders from (
    select max(cr_order_id) maxid, min(cr_order_id) minid
    from CARTREQUESTS
    WHERE cr_date >= sysdate - 1 
     and cr_order_id >= :orderid
)
connect by level <= maxid-minid+1
minus
select cr_order_id from CARTREQUESTS
WHERE cr_date >= sysdate - 1