Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Montu.

Asked: May 09, 2016 - 5:55 am UTC

Last updated: May 12, 2016 - 9:54 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello i am trying to generate result based on conditions.

CREATE TABLE REPEAT_EVENTS
(
ID NUMBER,
START_DT DATE,
END_DT DATE,
CNT NUMBER,
YEAR VARCHAR2(10 BYTE),
MONTH VARCHAR2(10 BYTE),
DAY VARCHAR2(10 BYTE),
WEEK VARCHAR2(10 BYTE),
WEEKDAY VARCHAR2(20 BYTE),
WEEKDAY_NO VARCHAR2(10 BYTE)
)


Insert into REPEAT_EVENTS
(ID,
CMP_ID, START_DT, END_DT, CNT, YEAR,
MONTH, DAY, WEEK, WEEKDAY, WEEKDAY_NO)
Values
(1,TO_DATE('05/04/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10,
'*', '2', NULL, NULL, '3',
'1');
COMMIT;

Basically i want to repeat event on every 3rd day of every other month so it will give me 10 records.If i change weekday/week/month then it should display me other result based on events.If you can guide me i would really appreciate it.

thnak you

and Chris said...

It sounds like you want something to start/show results based on different calendar options.

DBMS_scheduler has powerful options so you can specify daily/weekly/monthly schedules. You can use this to generate lists of dates in SQL.

Kim Berg Hansen shows you how to do this at:

http://www.kibeha.dk/2014/12/date-row-generator-with-dbmsscheduler.html

But I've no idea how 10 records come into this. 10 records of what?

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Repeat events

Montu, May 09, 2016 - 8:57 am UTC

Thank you basically u want to show data from start date to end date....now if client select 1 St day of 3 rd week of 4 th month then it should display all repeated events which falls in Start dare and end date..
Count is loop which can be used in connect by <= 10.

Chris Saxon
May 09, 2016 - 9:24 am UTC

I still don't understand. You want to show the 3rd day of every other month between:

TO_DATE('05/04/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and
TO_DATE('05/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

?

Then why are you expecting 10 rows? There's 12 months between those dates. So showing every other month you'll only get 6 rows...

Here's an example using Kim's generator:

create type t_date_table as table of date
/

create or replace function date_generator(
   p_repeat_interval in varchar2
 , p_start_date      in date
 , p_end_date        in date
)
   return t_date_table pipelined
is
   l_date_after   date;
   l_next_date    date;
begin
   l_date_after := p_start_date - 1;
   loop
       dbms_scheduler.evaluate_calendar_string(
          calendar_string   => p_repeat_interval
        , start_date        => p_start_date
        , return_date_after => l_date_after
        , next_run_date     => l_next_date
       );
       exit when l_next_date > p_end_date;
       pipe row (l_next_date);
       l_date_after := l_next_date;
   end loop;
end date_generator;
/

select dg.column_value the_date
  from table( date_generator(
            'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=3;'
          , DATE '2016-04-05'
          , DATE '2017-04-15'
       )) dg
/

THE_DATE           
--------------------
03-JUN-2016 00:00:00
03-AUG-2016 00:00:00
03-OCT-2016 00:00:00
03-DEC-2016 00:00:00
03-FEB-2017 00:00:00
03-APR-2017 00:00:00


If you want to know more about setting these repeat intervals, see:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN10040

Repeat events

Montu, May 09, 2016 - 9:53 am UTC

Reason for using Cnt 10 is I want to show only 10 recodrs... I saw scheduler but it's fix I want user to decide which data he wants to show based on parameters.....
It's same as Google events in calendar where by repeating events occurs from given time period.
Thank you.


Chris Saxon
May 09, 2016 - 1:53 pm UTC

You can pass the repeat interval as a variable:

SQL> var repeat varchar2(100) ;
SQL> exec :repeat := 'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=3;';

PL/SQL procedure successfully completed.

SQL> select dg.column_value the_date
  2    from table( date_generator(
  3              :repeat
  4            , DATE '2016-04-05'
  5            , DATE '2017-04-15'
  6         )) dg
  7  /

THE_DATE
---------
03-JUN-16
03-AUG-16
03-OCT-16
03-DEC-16
03-FEB-17
03-APR-17

6 rows selected.

SQL> exec :repeat := 'FREQ=WEEKLY; INTERVAL=3; BYDAY=FRI;';

PL/SQL procedure successfully completed.

SQL> select dg.column_value the_date
  2    from table( date_generator(
  3              :repeat
  4            , DATE '2016-04-05'
  5            , DATE '2017-04-15'
  6         )) dg;

THE_DATE
---------
08-APR-16
29-APR-16
20-MAY-16
10-JUN-16
01-JUL-16
22-JUL-16
12-AUG-16
02-SEP-16
23-SEP-16
14-OCT-16
04-NOV-16
25-NOV-16
16-DEC-16
06-JAN-17
27-JAN-17
17-FEB-17
10-MAR-17
31-MAR-17

18 rows selected.


So you just need to write some code which maps the user's input to the appropriate scheduler calendar syntax.

Repeat Events

Rajeshwaran, Jeyabal, May 09, 2016 - 2:37 pm UTC

Chris,

You want to show the 3rd day of every other month between:

TO_DATE('05/04/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and
TO_DATE('05/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS')



select dg.column_value the_date
  from table( date_generator(
            'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=3;'
          , DATE '2016-04-05'
          , DATE '2017-04-15'
       )) dg
/

THE_DATE           
--------------------
03-JUN-2016 00:00:00
03-AUG-2016 00:00:00
03-OCT-2016 00:00:00
03-DEC-2016 00:00:00
03-FEB-2017 00:00:00
03-APR-2017 00:00:00


starting with 5/4 and the need to show the 3rd day of every other month then it should be 03-JUL-2016 rather than 03-JUN-2016.

Here is somewhat i could get close , would you like to take it forward and incorporate other possible cases ?

rajesh@ORA11G> select new_dt
  2  from (
  3  select *
  4  from repeat_events
  5  model
  6    partition by ( rowid rid )
  7    dimension by ( 1 x )
  8    measures( id, start_dt, end_dt, year, month, day, week, weekday , weekday_no,
  9                        case when year is not null then 'Y'
 10                        when month is not null then 'M'
 11                        when day is not null then 'D'
 12                        when week is not null then 'W' end as uom ,
 13                            cast(null as date) as new_dt,
 14                            cast(null as date) as next_dt)
 15    rules iterate(10000) until( end_dt[1] < next_dt[iteration_number+1] )
 16    (
 17        new_dt[ iteration_number+1] = case when uom[1] ='Y' then add_months( start_dt[1] , 12 * iteration_number)
 18                                          when uom[1] ='M' then
 19                                            trunc( add_months( start_dt[1] , month[1] * iteration_number) ,'mm') + weekday[1]-1
 20                                            end ,
 21        next_dt[iteration_number+1] = case when uom[1] ='Y' then add_months( start_dt[1] , 12 * (iteration_number+1) )
 22                                           when uom[1] ='M' then
 23                                            add_months( start_dt[1] ,  month[1] * (iteration_number+1) )
 24                                            end )
 25         )
 26  where start_dt is null
 27  /

NEW_DT
-----------
03-JUL-2016
03-SEP-2016
03-NOV-2016
03-JAN-2017
03-MAR-2017
03-MAY-2017

6 rows selected.

rajesh@ORA11G>



Chris Saxon
May 09, 2016 - 3:40 pm UTC

"starting with 5/4 and the need to show the 3rd day of every other month then it should be 03-JUL-2016 rather than 03-JUN-2016. "

Why so?

I read that as:

"On or after the date provided, find the first 3rd of the month. Then return every other month after this."

3 Jun is the first occurrence after 4 May. Why would you skip this?

Repeat events

Montu, May 10, 2016 - 8:26 pm UTC

CREATE TABLE REPEAT_EVENTS
(
event_id NUMBER,
event_date DATE,
rep_start_dt DATE,
rep_end_dt DATE,
rep_cnt NUMBER,
rep_year VARCHAR2(10 BYTE),
rep_month VARCHAR2(10 BYTE),
rep_day VARCHAR2(10 BYTE),
rep_week VARCHAR2(10 BYTE),
rep_weekday_no VARCHAR2(20 BYTE)
)
Insert into REPEAT_EVENTS
(event_id,
event_date,
rep_start_dt,
rep_end_dt,
rep_cnt,
rep_year,
rep_month,
rep_day,
rep_week,
rep_weekday_no)

Values
(1,
TO_DATE('05/01/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('05/04/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('05/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
10,
'*',
'2',
NULL,
'3',
'1');

Insert into REPEAT_EVENTS
(event_id,
event_date,
rep_start_dt,
rep_end_dt,
rep_cnt,
rep_year,
rep_month,
rep_day,
rep_week,
rep_weekday_no)
Values
(2,
TO_DATE('05/02/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('05/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('05/03/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
15,
'*',
'3',
NULL,
'5',
'2');

Insert into REPEAT_EVENTS
(event_id,
event_date,
rep_start_dt,
rep_end_dt,
rep_cnt,
rep_year,
rep_month,
rep_day,
rep_week,
rep_weekday_no)
Values
(2,
TO_DATE('05/03/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('06/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('06/03/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
20,
'*',
'2',
NULL,
'4',
'3');

select event_id, cmp_id, event_date
from (
select
rep_start_dt, rep_end_dt, rep_cnt, rep_year, rep_month, rep_day, rep_week, rep_weekday_no, event_id, cmp_id,
event_date,
dense_rank() over (order by dt_yr) as yr_seq,
dense_rank() over (order by dt_mth) as mth_seq,
mod(dense_rank() over (order by dt_mth) , rep_month) as mth_mod,
dense_rank() over (order by dt_yr, dt_wk) as wk_seq,
mod(dense_rank() over (order by dt_yr, dt_wk) , rep_week) as wk_mod,
row_number() over (order by event_date) as day_seq,
day_wk ,
day_name
from
(
select
rep_start_dt , rep_end_dt, rep_cnt, rep_year, rep_month, rep_day, rep_week, rep_weekday_no, event_id, cmp_id,
rep_start_dt + level - 1 event_date,
to_number(to_char( (rep_start_dt + level - 1), 'YYYY')) dt_yr,
to_number(to_char( (rep_start_dt + level - 1), 'YYYYMM')) dt_mth,
to_number(to_char( (rep_start_dt + level - 1), 'WW')) dt_wk,
to_number(to_char( (rep_start_dt + level - 1), 'D')) day_wk ,
to_char( (rep_start_dt + level - 1), 'DAY') day_name
from FRP_EVENTS_REPEAT
connect by level <= rep_end_dt - rep_start_dt
) days
) x
where 1=1
and day_wk = rep_weekday_no
and wk_mod = 0
and mth_mod = 0
and rownum <= rep_cnt

I have created above query to get what result I want but when ever there are more records in table the above query runts continues and won't show me any result...if you can check what I am missing then it will really good for me.
Chris Saxon
May 11, 2016 - 8:36 am UTC

The connect by level trick only works on single row queries. Generate the rows first with a query such as:

select *
from   dual
connect by level <= (select max(rep_end_dt - rep_start_dt) from repeat_events)


Then cross join the result of this with your main query, filtering out extra rows as needed.

Repeat events

Montu, May 11, 2016 - 8:41 am UTC

Yes I tried that way to but result remains same...no out put displays :(
Chris Saxon
May 11, 2016 - 8:42 am UTC

Tried what precisely?

Repeat events

Montu, May 11, 2016 - 9:02 am UTC

Select * from (select * form repeat_events Connect by <= (select max (rep_end_dt -rep_start_dt) from repeat_events ) where 1=1
Chris Saxon
May 11, 2016 - 10:30 am UTC

You're still connecting by on the repeats table. The connect by needs to be on a query that returns one row, e.g. select * from dual.

To help make this clear it's good to use the with clause. For example:

with rws as (
  select rownum r from dual
  connect by level <= ( 
    select max ( rep_end_dt -rep_start_dt ) from repeat_events
  )
) 
select *
from   repeat_events
join   rws
on     r <= ( rep_end_dt - rep_start_dt );

Repeat events

Montu, May 11, 2016 - 12:43 pm UTC

Like this way !!!
with days as (
select rep_start_dt , rep_end_dt, rep_cnt, rep_year, rep_month, rep_day
, rep_week, rep_weekday_no, event_id
, rep_start_dt + level - 1 event_date
, to_number(to_char( (rep_start_dt + level - 1), 'YYYY')) dt_yr
, to_number(to_char( (rep_start_dt + level - 1), 'YYYYMM')) dt_mth
, to_number(to_char( (rep_start_dt + level - 1), 'WW')) dt_wk
, to_number(to_char( (rep_start_dt + level - 1), 'D')) day_wk
, to_char( (rep_start_dt + level - 1), 'DAY') day_name
from repeat_events_test
connect by level <= (
select max ( rep_end_dt -rep_start_dt ) from repeat_events_test
)
),seq as
(
select rep_start_dt, rep_end_dt, rep_cnt, rep_year, rep_month, rep_day
, rep_week, rep_weekday_no, event_id, event_date
, dense_rank() over (order by dt_yr) as yr_seq
, dense_rank() over (order by dt_mth) as mth_seq
, mod(dense_rank() over (order by dt_mth) , rep_month) as mth_mod
, dense_rank() over (order by dt_yr, dt_wk) as wk_seq
, mod(dense_rank() over (order by dt_yr, dt_wk) , rep_week) as wk_mod
, row_number() over (order by event_date) as day_seq
, day_wk
, day_name
from days
)
select *
from repeat_events_test
join days
on seq <= ( rep_end_dt - rep_start_dt );


It gives me error column ambiguously define at last line.....can you please try and check in developer I would really appreciate thank youbsir
Chris Saxon
May 11, 2016 - 2:55 pm UTC

You're still placing the connect by on your repeat_events table! Stop doing that!

Look at the query I provided. See how the connect by goes after dual? Do that!

Then join the results of this to repeat_events.

repeat events

montu, May 11, 2016 - 3:26 pm UTC

This is quite embarrassing... in your solution you use (select * from dual 'then' connect by)
------------------------------
i did the same instead of Dual i am calling my table which have level define in it which will give me desire output
-------------------------------
if i use connect by anywhere else then it wont allow.
---------------------------------
For testing purpose i have tried this also
-----------------------------------------
with rws as (
select rownum r from dual
connect by level <= (
select max ( rep_end_dt -rep_start_dt ) from repeat_events_test
)
)
select *
from repeat_events_test
join rws
on r <= ( rep_end_dt - rep_start_dt ) and rep_month = 2 and event_id = 1 and rep_cnt = 10;
------------------
Which will give 365 records which i dont want i want only values which is set in rep_cnt.
As you can see in above on condition i have used rep_cnt =10
it should only display me 10 records not 365 :)
Chris Saxon
May 11, 2016 - 4:50 pm UTC

You need to build the logic to only return the dates you're interested in :)

I'm still not sure what your issue is with Kim's solution I originally posted. It does what you want with lots of power and flexibility. All you need to do is map your inputs to the calendaring syntax.

For example:

select * from (
select EVENT_ID, COLUMN_VALUE, 
       row_number() over (partition by event_id, event_date order by column_value) rn,
       rep_cnt
from   repeat_events re,
       table( date_generator(
       case 
         when rep_month is not null then
           'FREQ=MONTHLY; INTERVAL=' || rep_month || '; '
       end ||  
       case
         when rep_weekday_no is not null then
           'BYDAY=' || decode(rep_weekday_no, 1, 'MON', 2, 'TUE', 3, 'WED') || ';'
       end 
          , rep_start_dt
          , rep_end_dt
       )) dg
)
where  rn <= rep_cnt;

I'm not sure what all the column values represent, so you'll need to figure out the mapping from these to scheduler syntax.

repeat events

montu, May 11, 2016 - 7:03 pm UTC

Okay sir i have tried your way.
it gives me below result.

EVENT_ID, COLUMN_VALUE, RN, REP_CNT, REP_DAY, REP_WEEK, REP_WEEKDAY_NO, REP_MONTH
1 09-05-16 1 10 null 3 1 2
1 16-05-16 2 10 3 1 2
1 23-05-16 3 10 3 1 2
1 30-05-16 4 10 3 1 2
1 04-07-16 5 10 3 1 2
1 11-07-16 6 10 3 1 2
1 18-07-16 7 10 3 1 2
1 25-07-16 8 10 3 1 2
1 05-09-16 9 10 3 1 2
1 12-09-16 10 10 3 1 2


But actual result should be every 1st day of 3rd week in every other month above result seems quite different to that.

AS i share my solution with you(before one) i really thought its 99% solved just don't to how to deal with that connect by. To make it 100% workable.
Chris Saxon
May 12, 2016 - 9:54 am UTC

This query:

with rws as ( 
select rownum r from dual 
connect by level <= ( 
select max ( rep_end_dt -rep_start_dt ) from repeat_events_test 
) 
) 
select * 
from repeat_events_test 
join rws 
on r <= ( rep_end_dt - rep_start_dt ) 


Gives you a row for all possible dates between the start and end for a given event.

You need to filter this by your logic to determine 1st day, 3rd week, etc.

with rws as ( 
  select rownum r from dual 
  connect by level <= ( 
    select max ( rep_end_dt -rep_start_dt ) 
    from repeat_events
  )
), all_dates as (
  select rep_start_dt + r - 1 dt, e.*
  from repeat_events e
  join rws 
  on r <= ( rep_end_dt - rep_start_dt )
)
  select * from all_dates
  where  dt = ...


If the analytic functions in your solution do what you want, paste them into the query and use them for filtering.

Like I said, I don't fully understand what the columns in your table mean. So I was just guessing. If you want the 1st day (Monday?) of every third week:

- Set the freq to BYWEEK
- Set the interval to 3
- Set BYDAY to MON

select * from (
select EVENT_ID, COLUMN_VALUE, 
       row_number() over (partition by event_id, event_date order by column_value) rn,
       rep_cnt
from   repeat_events re,
       table( date_generator(
       case 
         when rep_week is not null then
           'FREQ=WEEKLY; INTERVAL=' || rep_week || '; '
       end ||  
       case
         when rep_weekday_no is not null then
           'BYDAY=' || decode(rep_weekday_no, 1, 'MON', 2, 'TUE', 3, 'WED') || ';'
       end 
          , rep_start_dt
          , rep_end_dt
       )) dg
)
where  rn <= rep_cnt;

More to Explore

DBMS_SCHEDULER

More on PL/SQL routine DBMS_SCHEDULER here