• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

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
0
Eddie Shipman
Asked:
Eddie Shipman
  • 9
  • 6
  • 4
  • +2
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
If you dont need the above, please let me know I didnt get it completely.
0
 
johnsoneSenior Oracle DBACommented:
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

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
Pawan,

TOP isn't Oracle.

It also doesn't answer the question.  They are looking for gaps in the numbers.
0
 
Pawan KumarDatabase ExpertCommented:
@johnsone & slightwv (䄆 Netminder) - Thanks for helping. Just moved from SQL Server. :) My bad!
0
 
Eddie ShipmanAll-around developerAuthor Commented:
@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?
0
 
Pawan KumarDatabase ExpertCommented:
@All -- Thank you got it finally working on it.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
@johnsone, that also didn't work because when I changed the dates to 10 days ago, it showed one record that was already there.
0
 
slightwv (䄆 Netminder) Commented:
Can you show the data for 10 days ago?
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
slightwv (䄆 Netminder) Commented:
I think the above examples will have problems if there is a gap of more than 1.

Here is my attempt:
select minid+(level-1) from (
select max(cr_order_id) maxid, min(cr_order_id) minid
from CARTREQUESTS
WHERE cr_date >= to_date('9/9/2016','MM/DD/YYYY') and cr_date < to_date('9/9/2016','MM/DD/YYYY')+1
)
connect by level <= maxid-minid+1
minus
select cr_order_id from CARTREQUESTS
/

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
With full testing complete code [Where clause of dates is commented.. Please uncomment]

CREATE TABLE test
(
	 CR_ID BIGINT  
	,CR_DATE DATETIME                
	,CR_ORDER_ID BIGINT
	,CR_CUSTOMERID BIGINT
)
GO

INSERT INTO test VALUES
(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



/*
** SOLUTION 1
*/



SELECT v1 MissingCR_ORDER_IDs  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



/*
** SOLUTION 2
*/


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 test
		--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 test
	--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


Output
-----------------------

MissingCR_ORDER_IDs

100148451
100148458


Enjoy
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
johnsoneSenior Oracle DBACommented:
So, I'm confused.  Are we basing this on date, a starting id, or what?

Working on a more than 1 gap.
0
 
johnsoneSenior Oracle DBACommented:
So this will give you the starting and ending number for each gap.  Is that enough?

Not sure what we are looking at for universe, so you have to fill in the where clause in the middle where the ??? is.
SELECT prev_cr_order_id + 1 start_gap, 
       cr_order_id - 1      end_gap 
FROM   (SELECT cr_order_id, 
               Lag(cr_order_id) 
                 over ( 
                   ORDER BY cr_order_id) prev_cr_order_id 
        FROM   cartrequests 
        WHERE  ???) 
WHERE  cr_order_id != prev_cr_order_id + 1; 

Open in new window

If this doesn't give you correct results, please provide sample data where it doesn't.  Given the sample data you provided, this works.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Netmnder, - don't want to post so much data.
still testing others
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
awking00Commented:
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);
0
 
Pawan KumarDatabase ExpertCommented:
Great awking00 -- I was expecting someone to use the numbers table. Cool :)
0
 
slightwv (䄆 Netminder) Commented:
Isn't that pretty much what mine does?
0
 
awking00Commented:
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.
0
 
awking00Commented:
Sorry, slightwv. I never noticed your post that used the connect by method.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Thanks guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now