Avatar of Eddie Shipman
Eddie Shipman
Flag for United States of America asked on

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 latest number in the table for that day.

This is a simplified structure: Table name CARTREQUESTS
CR_ID
CR_DATE
CR_ORDER_ID
CR_CUSTOMERID

Open in new window

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

Open in new window

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
Oracle Database

Avatar of undefined
Last Comment
Eddie Shipman

8/22/2022 - Mon
Pawan Kumar

May be this ..

SELECT 
	TOP 2
	CR_ID,
	CR_DATE,
	CR_ORDER_ID,
	CR_CUSTOMERID
FROM 
	CARTREQUESTS
WHERE 
	CR_DATE <= SYSDATE-1
AND		
	CR_ORDER_ID > 100148443 

Open in new window

Pawan Kumar

If you dont need the above, please let me know I didnt get it completely.
johnsone

This should do it:
SELECT cr_order_id - 1 
FROM   (SELECT cr_order_id, 
               Lag(cr_order_id) 
                 over ( 
                   ORDER BY cr_order_id) prev_cr_order_id 
        FROM   cartrequests 
        WHERE  cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400 
       ) 
WHERE  cr_order_id != prev_cr_order_id + 1; 

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

Pawan,

TOP isn't Oracle.

It also doesn't answer the question.  They are looking for gaps in the numbers.
Pawan Kumar

@johnsone & slightwv (䄆 Netminder) - Thanks for helping. Just moved from SQL Server. :) My bad!
Eddie Shipman

ASKER
@Pawan - no, you didn't get it. I don't just want the top 2, I want the ones that are missing from the sequence.

@johnsone - Where do I pass the latest id that I have, the beginning id?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Pawan Kumar

@All -- Thank you got it finally working on it.
Eddie Shipman

ASKER
@johnsone, that also didn't work because when I changed the dates to 10 days ago, it showed one record that was already there.
slightwv (䄆 Netminder)

Can you show the data for 10 days ago?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Pawan Kumar

Try this sir..

SELECT CASE WHEN a.GStart >= b.Gend THEN b.Gend ELSE a.GStart END MissingCR_ORDER_IDs       
FROM (
		SELECT MIN(CR_ORDER_ID) - 1 GStart, MAX(CR_ORDER_ID) + 1 Gend, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks FROM (SELECT
			 CR_ORDER_ID
			,CR_ORDER_ID - ROW_NUMBER() OVER (ORDER BY CR_ORDER_ID) rnk       
		FROM CARTREQUESTS
		WHERE  cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400
	
	)y
    Group By Rnk) a 
	INNER JOIN (
	
	SELECT MIN(CR_ORDER_ID) - 1 GStart, MAX(CR_ORDER_ID) + 1 Gend, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks FROM (SELECT
         CR_ORDER_ID
        ,CR_ORDER_ID - ROW_NUMBER() OVER (ORDER BY CR_ORDER_ID) rnk       
    FROM CARTREQUESTS
	WHERE  cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400
	
	)y
    Group By Rnk) b ON a.rnks-1 = b.rnks

--

Open in new window

Pawan Kumar

May be this one also..

SELECT v1 GapStart as MissingCR_ORDER_IDs FROM
(
    SELECT CR_ORDER_ID+1 v1 ,LEAD(CR_ORDER_ID) OVER(ORDER BY CR_ORDER_ID)-1 v2
    FROM CARTREQUESTS
	WHERE  cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400
)t
WHERE v2>=v1

--

Open in new window

ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Pawan Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

>>With full testing complete code

Tested using Oracle?  Hint:  There is still invalid Oracle syntax in there.

Now retest and don't include:
(1540467 ,'9/9/2016 11:02:16 AM',    100148450  , 1216001),

This creates a gap of two numbers missing.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Pawan Kumar

In the above case sir we can use something like below...

SELECT v1 StartingMissingCR_ORDER_IDs , v2 EndMissingCr_Order_No   FROM
(
    SELECT CR_ORDER_ID+1 v1 ,LEAD(CR_ORDER_ID) OVER(ORDER BY CR_ORDER_ID)-1 v2
    FROM test	
	--WHERE  cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400
)t
WHERE v2>=v1

Open in new window

johnsone

So, I'm confused.  Are we basing this on date, a starting id, or what?

Working on a more than 1 gap.
SOLUTION
johnsone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Eddie Shipman

ASKER
Netmnder, - don't want to post so much data.
still testing others
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
slightwv (䄆 Netminder)

You don't need to post it all.  Just add to the test case so that it simulates what you are seeing.

>>still testing others

Why?  I have already pointed out they have problems if you are missing more than 1 in a gap.
awking00

select (select min(cr_order_id) from cartrequests where cr_date > trunc(sysdate - 1)) + level - 1
from dual
connect by level <= (select max(cr_order_id) - min(cr_order_id) from cartrequests where cr_date > trunc(sysdate - 1))
minus
select cr_order_id from cartrequests where cr_date > trunc(sysdate - 1);
Pawan Kumar

Great awking00 -- I was expecting someone to use the numbers table. Cool :)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

Isn't that pretty much what mine does?
awking00

The connect by method works regardless of how many gaps. It in essence generates a "gapless" list then substracts the existing values to determining the missing ones.
awking00

Sorry, slightwv. I never noticed your post that used the connect by method.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Eddie Shipman

ASKER
Thanks guys.