Forum Stats

  • 3,782,078 Users
  • 2,254,591 Discussions
  • 7,879,908 Comments

Discussions

Sequence number in oracle SQL SQL

Vemula Muni
Vemula Muni Member Posts: 57
edited Jul 31, 2019 11:07AM in SQL & PL/SQL

Hi I have my data as below.

<span class="pln">Year    Period  Start_Date  End_Date <br/></span><span class="lit">2019</span><span class="pln">    </span><span class="lit">6</span><span class="pln">        </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jun-19    </span><span class="lit">30</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">2019</span><span class="pln">    </span><span class="lit">7</span><span class="pln">        </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jul-19    </span><span class="lit">31</span><span class="pun">-</span><span class="pln">Jul-19</span>

Period will be the month number

start date and end date will be the month start and end dates.

I want to fetch the data as below based on above.


7        1           05-Jul-19
7        2           12-Jul-19
7        3           19-Jul-19
7        4           26-Jul-19
<span class="lit">7</span><span class="pln">        </span><span class="lit">5</span><span class="pln">           </span><span class="lit">26</span><span class="pun">-</span><span class="pln">Aug-19<code>( when ever month last day is not equal to Friday i need to display next month first Friday in current month).
<span class="pln">Period  Sequence_no Period_end_dt <br/></span><span class="lit">6</span><span class="pln">        </span><span class="lit">1</span><span class="pln">           </span><span class="lit">07</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln">        </span><span class="lit">2</span><span class="pln">           </span><span class="lit">14</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln">        </span><span class="lit">3</span><span class="pln">           </span><span class="lit">21</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln">        </span><span class="lit">4</span><span class="pln">           </span><span class="lit">28</span><span class="pun">-</span><span class="pln">Jun-19 <br/><code><span class="lit"></span><span class="pln">6        5</span><span class="pln">           </span><span class="lit">05</span><span class="pun">-</span><span class="pln">Jul-19 ( when ever month last day is not equal to Friday i need to display next month first Friday in current month).</span>

in above out put data Period_end_dt values will be the Fridays in between start and end date in my source data. .

John ThortonMustafa_KALAYCI

Best Answer

  • mathguy
    mathguy Member Posts: 10,223 Blue Diamond
    edited Jul 30, 2019 10:38AM Accepted Answer
    Muneendra wrote:mathguy can you please alter your query to fetch data as like below. ( when month end date is not equal to Friday, i want to display the next month first Friday in the current month with period as current month)Please refer the below changes. YEAR_ PERIOD START_DATE END_DATE SEQUENCE_NO PERIOD_END_DT---------- ---------- ----------- ----------- ----------- ------------- 2019 6 01-Jun-2019 30-Jun-2019 1 07-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 2 14-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 3 21-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 4 28-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 5 05-Jul-2019 2019 9 01-Sep-2019 30-Sep-2019 1 06-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 2 13-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 3 20-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 4 27-Sep-2019  2019 9 01-Oct-2019 31-Oct-2019 5 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 1 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 2 11-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 3 18-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 4 25-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 5 01-Nov-2019

    To get that output, you need just a very minor change to the code I proposed (highlighted in red below). You may use what I posted (including the WITH clause), just make this minor change in the code and re-run, you will get the modified output you requested.

    select  year_, period, start_date, add_months(start_date, 1) - 1 as end_date, level as sequence_no,        trunc(start_date + 2, 'iw') - 3 + 7 * level as period_end_dtfrom    (          select year_, period, to_date(to_char(year_ * 100 + period), 'yyyymm') as start_date          from   test_data        )connect by  level < ( add_months(start_date, 1) - (trunc(start_date + 2, 'iw') - 3) + 6) / 7          -- here (add 6)
            and prior year_ = year_ and prior period = period        and prior sys_guid() is not nullorder   by period_end_dt;
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 29, 2019 1:11PM
    Muneendra wrote:Hi I have my data as below.<span class="pln">Year Period Start_Date End_Date <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jun-19 </span><span class="lit">30</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jul-19 </span><span class="lit">31</span><span class="pun">-</span><span class="pln">Jul-19</span>

    Period will be the month number

    start date and end date will be the month start and end dates.

    I want to fetch the data as below based on above.

    <span class="pln">Period Sequence_no Period_end_dt <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">07</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">14</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">21</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">28</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">05</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">12</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">19</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">26</span><span class="pun">-</span><span class="pln">Jul-19</span>

    in above out put data Period_end_dt values will be the Fridays in between start and end date in my source data. .

    Please provide details as stated in #5 - #9 inclusive in URL below

    How do I ask a question on the forums?

  • EdStevens
    EdStevens Member Posts: 28,608 Gold Crown
    edited Jul 29, 2019 1:23PM
    Muneendra wrote:Hi I have my data as below.<span class="pln">Year Period Start_Date End_Date <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jun-19 </span><span class="lit">30</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">2019</span><span class="pln"> </span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pun">-</span><span class="pln">Jul-19 </span><span class="lit">31</span><span class="pun">-</span><span class="pln">Jul-19</span>

    Period will be the month number

    start date and end date will be the month start and end dates.

    I want to fetch the data as below based on above.

    <span class="pln">Period Sequence_no Period_end_dt <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">07</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">14</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">21</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">6</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">28</span><span class="pun">-</span><span class="pln">Jun-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">05</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="lit">12</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">19</span><span class="pun">-</span><span class="pln">Jul-19 <br/></span><span class="lit">7</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> </span><span class="lit">26</span><span class="pun">-</span><span class="pln">Jul-19</span>

    in above out put data Period_end_dt values will be the Fridays in between start and end date in my source data. .

    In addition to John's request,

    1) It appears that 'period' could/should be derived from the month of either start_date or end_date.  If so, then the 'period' column should not even exist.

    What is the data type of all of these columns? (We'll see that when you respond to John's request.)

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Jul 29, 2019 1:57PM

    Here's how you should provide your "data"

    with raw_datum as    (     select extract(year from to_date('01-Jun-19', 'dd-Mon-yy')) as my_year           ,extract(month from to_date('01-Jun-19', 'dd-Mon-yy')) as my_period           ,to_date('01-Jun-19', 'dd-Mon-yy') as start_date           ,to_date('30-Jun-19', 'dd-Mon-yy') as end_date           ,extract(day from to_date('30-Jun-19', 'dd-Mon-yy')) as num_days_per_month       from dual      union all     select extract(year from to_date('01-Jul-19', 'dd-Mon-yy')) as my_year           ,extract(month from to_date('01-Jul-19', 'dd-Mon-yy')) as my_period           ,to_date('01-Jul-19', 'dd-Mon-yy') as start_date           ,to_date('31-Jul-19', 'dd-Mon-yy') as end_date           ,extract(day from to_date('31-Jul-19', 'dd-Mon-yy')) as num_days_per_month       from dual    )select rd.my_year      ,rd.my_period      ,rd.start_date      ,rd.end_date      ,rd.num_days_per_month  from raw_datum rd;

    Output

    Time Start: 7/29/2019 1:27:52 PM   MY_YEAR  MY_PERIOD START_DATE END_DATE  NUM_DAYS_PER_MONTH---------- ---------- ---------- --------- ------------------      2019          6 01-JUN-19  30-JUN-19                 30      2019          7 01-JUL-19  31-JUL-19                 312 rows selected.Time End: 7/29/2019 1:27:53 PMElapsed Time for Script Execution: 638 msecs
  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jul 29, 2019 1:57PM

    with t (sd, ed) as

    (

    select date '2019-06-01', date '2019-07-31' from dual

    )

    select

    dt

    , extract(year from  dt) yr

    , extract(month from  dt) mnth

    , row_number() over (partition by extract(month from  dt) order by dt) seq

    from

    (

       select sd + level -1 dt,  to_char(sd + level - 1, 'DY', 'nls_language=ENGLISH') dy from t

       connect by sd + level - 1 <= ed

    )

    where dy = 'FRI'

    ;

    DT                YR       MNTH        SEQ
    --------- ---------- ---------- ----------
    07-JUN-19       2019          6          1
    14-JUN-19       2019          6          2
    21-JUN-19       2019          6          3
    28-JUN-19       2019          6          4
    05-JUL-19       2019          7          1
    12-JUL-19       2019          7          2
    19-JUL-19       2019          7          3
    26-JUL-19       2019          7          4

    8 rows selected.

    Mustafa_KALAYCI
  • Paulzip
    Paulzip Member Posts: 8,540 Blue Diamond
    edited Jul 29, 2019 2:04PM

    You shouldn't store period as it's derived from the date range, and in doing so, you create the possibility where period could mismatch the date. You could create a virtual column to expose it.

    John ThortonMustafa_KALAYCI
  • mathguy
    mathguy Member Posts: 10,223 Blue Diamond
    edited Jul 30, 2019 1:41AM

    Here is a better (closed form) way to do this. Based on simple computations, rather than an enumeration of all consecutive days and filtering for day-of-the-week = Friday.

    Inputs: a set rows showing "year" and "period" (month), assuming no duplicates. Output: for each input row, several output rows, repeating the "year", "period", "start date", "end date", and all the Fridays in the period, with the sequence number in a separate column. Ordered by those Fridays (which in particular will also, automatically, order by year first, and by period second).

    Note that YEAR is an Oracle keyword, which shouldn't be used as a column name. I changed it to YEAR_  (notice the trailing underscore). Test data provided in WITH clause.

    with  test_data as (    select 2019 as year_,  6 as period from dual union all    select 2019         ,  9           from dual union all    select 2019         , 10           from dual  )select  year_, period, start_date, add_months(start_date, 1) - 1 as end_date, level as sequence_no,        trunc(start_date + 2, 'iw') - 3 + 7 * level as period_end_dtfrom    (          select year_, period, to_date(to_char(year_ * 100 + period), 'yyyymm') as start_date          from   test_data        )connect by  level < ( add_months(start_date, 1) - (trunc(start_date + 2, 'iw') - 3) ) / 7        and prior year_ = year_ and prior period = period        and prior sys_guid() is not nullorder   by period_end_dt;     YEAR_      PERIOD  START_DATE   END_DATE     SEQUENCE_NO  PERIOD_END_DT----------  ----------  -----------  -----------  -----------  -------------      2019           6  01-Jun-2019  30-Jun-2019            1  07-Jun-2019      2019           6  01-Jun-2019  30-Jun-2019            2  14-Jun-2019      2019           6  01-Jun-2019  30-Jun-2019            3  21-Jun-2019      2019           6  01-Jun-2019  30-Jun-2019            4  28-Jun-2019      2019           9  01-Sep-2019  30-Sep-2019            1  06-Sep-2019      2019           9  01-Sep-2019  30-Sep-2019            2  13-Sep-2019      2019           9  01-Sep-2019  30-Sep-2019            3  20-Sep-2019      2019           9  01-Sep-2019  30-Sep-2019            4  27-Sep-2019      2019          10  01-Oct-2019  31-Oct-2019            1  04-Oct-2019      2019          10  01-Oct-2019  31-Oct-2019            2  11-Oct-2019      2019          10  01-Oct-2019  31-Oct-2019            3  18-Oct-2019      2019          10  01-Oct-2019  31-Oct-2019            4  25-Oct-2019
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Jul 30, 2019 7:07AM

    @Paulzip  Can you please alter your query to fetch the dates like below.

    DT                YR       MNTH        SEQ

    --------- ---------- ---------- ----------

    07-JUN-19       2019          6          1

    14-JUN-19       2019          6          2

    21-JUN-19       2019          6          3

    28-JUN-19       2019          6          4 When month end date is grater than last Friday in month, i need to add one more row with next month first Friday with current month number as shown below.

    05-JUL-19       2019          6          5

    05-JUL-19       2019          7          1

    12-JUL-19       2019          7          2

    19-JUL-19       2019          7          3

    26-JUL-19       2019          7          4

    02-AUG-19       2019          7          5   

  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Jul 30, 2019 7:19AM

    @mathguy 

    can you please alter your query to fetch data as like below. ( when month end date is not equal to Friday, i want to display the next month first Friday in the current month with period as current month)

    Please refer the below changes.

         YEAR_      PERIOD  START_DATE   END_DATE     SEQUENCE_NO  PERIOD_END_DT

    ----------  ----------  -----------  -----------  -----------  -------------

          2019           6  01-Jun-2019  30-Jun-2019            1  07-Jun-2019

          2019           6  01-Jun-2019  30-Jun-2019            2  14-Jun-2019

          2019           6  01-Jun-2019  30-Jun-2019            3  21-Jun-2019

          2019           6  01-Jun-2019  30-Jun-2019            4  28-Jun-2019

          2019           6  01-Jun-2019  30-Jun-2019            5  05-Jul-2019

          2019           9  01-Sep-2019  30-Sep-2019            1  06-Sep-2019

          2019           9  01-Sep-2019  30-Sep-2019            2  13-Sep-2019

          2019           9  01-Sep-2019  30-Sep-2019            3  20-Sep-2019

          2019           9  01-Sep-2019  30-Sep-2019            4  27-Sep-2019

          2019           9  01-Oct-2019  31-Oct-2019            5  04-Oct-2019

          2019          10  01-Oct-2019  31-Oct-2019            1  04-Oct-2019

          2019          10  01-Oct-2019  31-Oct-2019            2  11-Oct-2019

          2019          10  01-Oct-2019  31-Oct-2019            3  18-Oct-2019

          2019          10  01-Oct-2019  31-Oct-2019            4  25-Oct-2019

          2019          10  01-Oct-2019  31-Oct-2019            5  01-Nov-2019

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jul 30, 2019 8:11AM

    with t (sd, ed) as

    (

      select date '2019-05-01', date '2019-07-31' from dual

    )

    select

      dt

    , dy 

    , case when dt = mnth_end then decode(dy, 'FRI', dt, lag(dt) over (order by dt)+7) else dt end dt_1

    , extract(year from  dt) yr

    , extract(month from  dt) mnth

    , row_number() over (partition by extract(month from  dt) order by dt) seq

    from

    (

       select

         sd + level -1 dt

       , to_char(sd + level - 1, 'DY', 'nls_language=ENGLISH') dy

       , last_day(sd + level -1) mnth_end

       from t

       connect by sd + level - 1 <= ed

    )

    where dy = 'FRI' or (dt = mnth_end)

    ;

    DT          DY  DT_1                YR       MNTH        SEQ
    ----------- --- ----------- ---------- ---------- ----------
    2019-MAY-03 FRI 2019-MAY-03       2019          5          1
    2019-MAY-10 FRI 2019-MAY-10       2019          5          2
    2019-MAY-17 FRI 2019-MAY-17       2019          5          3
    2019-MAY-24 FRI 2019-MAY-24       2019          5          4
    2019-MAY-31 FRI 2019-MAY-31       2019          5          5
    2019-JUN-07 FRI 2019-JUN-07       2019          6          1
    2019-JUN-14 FRI 2019-JUN-14       2019          6          2
    2019-JUN-21 FRI 2019-JUN-21       2019          6          3
    2019-JUN-28 FRI 2019-JUN-28       2019          6          4
    2019-JUN-30 SUN 2019-JUL-05       2019          6          5
    2019-JUL-05 FRI 2019-JUL-05       2019          7          1
    2019-JUL-12 FRI 2019-JUL-12       2019          7          2
    2019-JUL-19 FRI 2019-JUL-19       2019          7          3
    2019-JUL-26 FRI 2019-JUL-26       2019          7          4
    2019-JUL-31 WED 2019-AUG-02       2019          7          5

    15 rows selected

  • mathguy
    mathguy Member Posts: 10,223 Blue Diamond
    edited Jul 30, 2019 10:38AM Accepted Answer
    Muneendra wrote:mathguy can you please alter your query to fetch data as like below. ( when month end date is not equal to Friday, i want to display the next month first Friday in the current month with period as current month)Please refer the below changes. YEAR_ PERIOD START_DATE END_DATE SEQUENCE_NO PERIOD_END_DT---------- ---------- ----------- ----------- ----------- ------------- 2019 6 01-Jun-2019 30-Jun-2019 1 07-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 2 14-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 3 21-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 4 28-Jun-2019 2019 6 01-Jun-2019 30-Jun-2019 5 05-Jul-2019 2019 9 01-Sep-2019 30-Sep-2019 1 06-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 2 13-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 3 20-Sep-2019 2019 9 01-Sep-2019 30-Sep-2019 4 27-Sep-2019  2019 9 01-Oct-2019 31-Oct-2019 5 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 1 04-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 2 11-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 3 18-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 4 25-Oct-2019 2019 10 01-Oct-2019 31-Oct-2019 5 01-Nov-2019

    To get that output, you need just a very minor change to the code I proposed (highlighted in red below). You may use what I posted (including the WITH clause), just make this minor change in the code and re-run, you will get the modified output you requested.

    select  year_, period, start_date, add_months(start_date, 1) - 1 as end_date, level as sequence_no,        trunc(start_date + 2, 'iw') - 3 + 7 * level as period_end_dtfrom    (          select year_, period, to_date(to_char(year_ * 100 + period), 'yyyymm') as start_date          from   test_data        )connect by  level < ( add_months(start_date, 1) - (trunc(start_date + 2, 'iw') - 3) + 6) / 7          -- here (add 6)
            and prior year_ = year_ and prior period = period        and prior sys_guid() is not nullorder   by period_end_dt;