Out of Sequence numbers for today

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

Select allOpen in new window

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; 

Select allOpen in new window

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

--

Select allOpen in new window

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

--

Select allOpen in new window

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

Select allOpen in new window