Solved

Out of Sequence numbers for today

Posted on 2016-10-03
25
47 Views
Last Modified: 2016-10-03
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
Comment
Question by:EddieShipman
  • 9
  • 6
  • 4
  • +2
25 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826575
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826576
If you dont need the above, please let me know I didnt get it completely.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41826585
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41826587
Pawan,

TOP isn't Oracle.

It also doesn't answer the question.  They are looking for gaps in the numbers.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826590
@johnsone & slightwv (䄆 Netminder) - Thanks for helping. Just moved from SQL Server. :) My bad!
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 41826591
@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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826599
@All -- Thank you got it finally working on it.
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 41826603
@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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41826612
Can you show the data for 10 days ago?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826618
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826630
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 350 total points
ID: 41826648
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 75 total points
ID: 41826662
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41826668
>>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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826690
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
 
LVL 34

Expert Comment

by:johnsone
ID: 41826691
So, I'm confused.  Are we basing this on date, a starting id, or what?

Working on a more than 1 gap.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 75 total points
ID: 41826714
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 41826727
Netmnder, - don't want to post so much data.
still testing others
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41826731
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
 
LVL 31

Expert Comment

by:awking00
ID: 41826747
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41826751
Great awking00 -- I was expecting someone to use the numbers table. Cool :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41826753
Isn't that pretty much what mine does?
0
 
LVL 31

Expert Comment

by:awking00
ID: 41826754
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
 
LVL 31

Expert Comment

by:awking00
ID: 41826758
Sorry, slightwv. I never noticed your post that used the connect by method.
0
 
LVL 26

Author Closing Comment

by:EddieShipman
ID: 41826836
Thanks guys.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now