Generating sequence skipping by 2 Oracle-CodePudding

I have the below query.But I want to tweak the output to skip 2 consecutive numbers on every cycle. For example skip 1,2 in 2nd cycle . Skip 3,4 in 3rd cycle . Skip 5,6 in 4th cycle and so on.

Also, there will be 2 input variables (i) the number to be removed/added (ii) from which date. Once this is passed the sequence will have to be recalculate from that date on wards. I am using Oracle version 12.1

 SELECT TRUNC(sysdate, 'Y')   level - 1 AS "date", MOD(level - 1, 10)   1 AS col1
   FROM dual
CONNECT BY level <= ADD_MONTHS(TRUNC(sysdate, 'Y'), 12) - TRUNC(sysdate, 'Y')

    Date     Col1  Expected Output
    1/1/2022    1   1
    1/2/2022    2   2
    1/3/2022    3   3
    1/4/2022    4   4
    1/5/2022    5   5
    1/6/2022    6   6
    1/7/2022    7   7
    1/8/2022    8   8
    1/9/2022    9   9
    1/10/2022   10  10 
    1/11/2022   1   3
    1/12/2022   2   4
    1/13/2022   3   5
    1/14/2022   4   6
    1/15/2022   5   7
    1/16/2022   6   8
    1/17/2022   7   9
    1/18/2022   8   10
    1/19/2022   9   1
    1/20/2022   10  2
    1/21/2022   1   5
    1/22/2022   2   6
    1/23/2022   3   7
    1/24/2022   4   8
    1/25/2022   5   9
    1/26/2022   6   10
    1/27/2022   7   1
    1/28/2022   8   2
    1/29/2022   9   3
    1/30/2022   10  4
    1/31/2022   1   7
    2/1/2022    2   8
    2/2/2022    3   9
    2/3/2022    4   10

CodePudding user response：

You can use some more arithmetical functions such as FLOOR() and CEIL() along with precomputing unmatched values within a subquery, and then filter out by the incremented values upto the currently existing expression's value of

ADD_MONTHS(TRUNC(sysdate, 'Y'), 12) - TRUNC(sysdate, 'Y')

such as

WITH d AS
(
   SELECT SUM(CASE 
              WHEN MOD(FLOOR((level-.1)/10),6) = CEIL((MOD(level - 1, 10)   1)/2) 
              THEN 
                   0 
              ELSE 
                   1 
               END) 
              OVER (ORDER BY level) AS rn, 
          ADD_MONTHS(TRUNC(sysdate, 'Y'), 12) - TRUNC(sysdate, 'Y') AS day_diff,
          MOD(level - 1, 10)   1 AS col1, 
          MOD(FLOOR((level-.1)/10),6) AS col2, 
          CEIL((MOD(level - 1, 10)   1)/2) AS col3
     FROM dual
  CONNECT BY level <= (ADD_MONTHS(TRUNC(sysdate, 'Y'), 12) - TRUNC(sysdate, 'Y'))*2
)
SELECT TRUNC(sysdate, 'Y')   rn - 1 AS "date", col1
  FROM d
 WHERE col2 != col3 
   AND rn <= day_diff 
 ORDER BY rn

Demo

CodePudding user response：

Wrote below . Thanks for your answer as well. I just made some columns in excel and wrote some functions to get it

   WITH Y AS (SELECT TRUNC(SYSDATE,'Y')  LEVEL - 1 dt,
                      MOD(LEVEL-1,10) 1 AS grpid ,
                      LEVEL rnum FROM dual
                CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)-TRUNC(SYSDATE,'Y')  
               ),
         Z AS(SELECT dt,rnum,COUNT(CASE WHEN grpid = 1 THEN 1 END) OVER(ORDER BY dt) -1 as grp FROM y)
        
        SELECT z.*,
                REGEXP_REPLACE
                (CASE WHEN MOD(rnum,grp*10) = 1 THEN MOD(grp grp 1,10)
                 WHEN MOD(rnum,grp*10) = 2 THEN MOD(grp grp 2,10)
                 ELSE MOD(rnum-grp*(10-2),10)
             END,'^0$',10) output
         FROM Z;