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.
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.
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>
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.
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 :(
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
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
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 :)
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.
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;