Hi Tom,
I have data like below
event_flag event_date
1 date1
1 date2
0 date3
1 date4
0 date5
0 date6
1 date7
1 date8
1 date9
...
Where event_flag indicates that if there is an event that has to be done. 1 -> Event , 0 -> No event(Holiday).
event_date is in ascending sorted order.
I want to have a third column like below
event_flag event_date events
1 date1 1
1 date2 1
0 date3 0
1 date4 2
0 date5 0
0 date6 0
1 date7 3
1 date8 3
1 date9 3
0 date10 0
1 date11 4
So column events should be increasing by 1 if there is a holiday between last and current event otherwise it should be same as the last one.
Could you please help to write the SQL query for this.
below is the sample data
CREATE TABLE event_dtl AS
SELECT CASE WHEN to_char(event_dt,'dy') IN ('sun','sat') THEN 0 ELSE 1 END AS event_flag,event_dt FROM
(SELECT TRUNC(SYSDATE,'month')+LEVEL-1 AS event_dt FROM dual CONNECT BY LEVEL<=31);
SELECT * FROM event_dtl;
event_flag event_dt
1 1 01-10-2019
2 1 02-10-2019
3 1 03-10-2019
4 1 04-10-2019
5 0 05-10-2019
6 0 06-10-2019
7 1 07-10-2019
8 1 08-10-2019
9 1 09-10-2019
10 1 10-10-2019
11 1 11-10-2019
12 0 12-10-2019
13 0 13-10-2019
14 1 14-10-2019
15 1 15-10-2019
16 1 16-10-2019
17 1 17-10-2019
18 1 18-10-2019
19 0 19-10-2019
20 0 20-10-2019
21 1 21-10-2019
22 1 22-10-2019
23 1 23-10-2019
24 1 24-10-2019
25 1 25-10-2019
26 0 26-10-2019
27 0 27-10-2019
28 1 28-10-2019
29 1 29-10-2019
30 1 30-10-2019
31 1 31-10-2019
The desired column events's value should be like below
event_flag event_dt events
1 1 01-10-2019 1
2 1 02-10-2019 1
3 1 03-10-2019 1
4 1 04-10-2019 1
5 0 05-10-2019 0
6 0 06-10-2019 0
7 1 07-10-2019 2
8 1 08-10-2019 2
9 1 09-10-2019 2
10 1 10-10-2019 2
11 1 11-10-2019 2
12 0 12-10-2019 0
13 0 13-10-2019 0
14 1 14-10-2019 3
15 1 15-10-2019 3
16 1 16-10-2019 3
17 1 17-10-2019 3
18 1 18-10-2019 3
19 0 19-10-2019 0
20 0 20-10-2019 0
21 1 21-10-2019 4
22 1 22-10-2019 4
23 1 23-10-2019 4
24 1 24-10-2019 4
25 1 25-10-2019 4
26 0 26-10-2019 0
27 0 27-10-2019 0
28 1 28-10-2019 5
29 1 29-10-2019 5
30 1 30-10-2019 5
31 1 31-10-2019 5
Or if we have holiday on Wednesdays too then
DROP TABLE event_dtl;
CREATE TABLE event_dtl AS
SELECT CASE WHEN to_char(event_dt,'dy') IN ('sun','sat','wed') THEN 0 ELSE 1 END AS event_flag,event_dt FROM
(SELECT TRUNC(SYSDATE,'month')+LEVEL-1 AS event_dt FROM dual CONNECT BY LEVEL<=31);
SELECT * FROM event_dtl;
event_flag event_dt
1 1 01-10-2019
2 0 02-10-2019
3 1 03-10-2019
4 1 04-10-2019
5 0 05-10-2019
6 0 06-10-2019
7 1 07-10-2019
8 1 08-10-2019
9 0 09-10-2019
10 1 10-10-2019
11 1 11-10-2019
12 0 12-10-2019
13 0 13-10-2019
14 1 14-10-2019
15 1 15-10-2019
16 0 16-10-2019
17 1 17-10-2019
18 1 18-10-2019
19 0 19-10-2019
20 0 20-10-2019
21 1 21-10-2019
22 1 22-10-2019
23 0 23-10-2019
24 1 24-10-2019
25 1 25-10-2019
26 0 26-10-2019
27 0 27-10-2019
28 1 28-10-2019
29 1 29-10-2019
30 0 30-10-2019
31 1 31-10-2019
and the desired column will be
event_flag event_dt events
1 1 01-10-2019 1
2 0 02-10-2019 0
3 1 03-10-2019 2
4 1 04-10-2019 2
5 0 05-10-2019 0
6 0 06-10-2019 0
7 1 07-10-2019 3
8 1 08-10-2019 3
9 0 09-10-2019 0
10 1 10-10-2019 4
11 1 11-10-2019 4
12 0 12-10-2019 0
13 0 13-10-2019 0
14 1 14-10-2019 5
15 1 15-10-2019 5
16 0 16-10-2019 0
17 1 17-10-2019 6
18 1 18-10-2019 6
19 0 19-10-2019 0
20 0 20-10-2019 0
21 1 21-10-2019 7
22 1 22-10-2019 7
23 0 23-10-2019 0
24 1 24-10-2019 8
25 1 25-10-2019 8
26 0 26-10-2019 0
27 0 27-10-2019 0
28 1 28-10-2019 9
29 1 29-10-2019 9
30 0 30-10-2019 0
31 1 31-10-2019 10
Arun.
I'll show you I built this up in increments.
SQL> CREATE TABLE event_dtl AS
2 SELECT CASE WHEN to_char(event_dt,'dy') IN ('sun','sat','wed') THEN 0 ELSE 1 END AS event_flag,event_dt FROM
3 (SELECT TRUNC(SYSDATE,'month')+LEVEL-1 AS event_dt FROM dual CONNECT BY LEVEL<=31);
Table created.
--
-- A new event is when my flag is 1 and the previous day is 0 (or it is the first row of the table)
--
SQL> select
2 case
3 when ( event_flag = 1 and lag(event_flag,1) over ( order by event_dt) = 0 ) or
4 row_number() over ( order by event_dt ) = 1
5 then event_dt end is_last_event,
6 e.*
7 from event_dtl e ;
IS_LAST_E EVENT_FLAG EVENT_DT
--------- ---------- ---------
01-OCT-19 1 01-OCT-19
0 02-OCT-19
03-OCT-19 1 03-OCT-19
1 04-OCT-19
0 05-OCT-19
0 06-OCT-19
07-OCT-19 1 07-OCT-19
1 08-OCT-19
0 09-OCT-19
10-OCT-19 1 10-OCT-19
1 11-OCT-19
0 12-OCT-19
0 13-OCT-19
14-OCT-19 1 14-OCT-19
1 15-OCT-19
0 16-OCT-19
17-OCT-19 1 17-OCT-19
1 18-OCT-19
0 19-OCT-19
0 20-OCT-19
21-OCT-19 1 21-OCT-19
1 22-OCT-19
0 23-OCT-19
24-OCT-19 1 24-OCT-19
1 25-OCT-19
0 26-OCT-19
0 27-OCT-19
28-OCT-19 1 28-OCT-19
1 29-OCT-19
0 30-OCT-19
31-OCT-19 1 31-OCT-19
31 rows selected.
--
-- For any day flagged as a new event, any following days also with event flag = 1 should be the treated
-- as the same day
--
SQL> with t as
2 (
3 select
4 case
5 when ( event_flag = 1 and lag(event_flag,1) over ( order by event_dt) = 0 ) or
6 row_number() over ( order by event_dt ) = 1
7 then event_dt end is_last_event,
8 e.*
9 from event_dtl e
10 )
11 select
12 case when event_flag = 1 then nvl2(is_last_event,is_last_event,lag(is_last_event ignore nulls) over ( order by event_dt)) end fill,
13 t.*
14 from t;
FILL IS_LAST_E EVENT_FLAG EVENT_DT
--------- --------- ---------- ---------
01-OCT-19 01-OCT-19 1 01-OCT-19
0 02-OCT-19
03-OCT-19 03-OCT-19 1 03-OCT-19
03-OCT-19 1 04-OCT-19
0 05-OCT-19
0 06-OCT-19
07-OCT-19 07-OCT-19 1 07-OCT-19
07-OCT-19 1 08-OCT-19
0 09-OCT-19
10-OCT-19 10-OCT-19 1 10-OCT-19
10-OCT-19 1 11-OCT-19
0 12-OCT-19
0 13-OCT-19
14-OCT-19 14-OCT-19 1 14-OCT-19
14-OCT-19 1 15-OCT-19
0 16-OCT-19
17-OCT-19 17-OCT-19 1 17-OCT-19
17-OCT-19 1 18-OCT-19
0 19-OCT-19
0 20-OCT-19
21-OCT-19 21-OCT-19 1 21-OCT-19
21-OCT-19 1 22-OCT-19
0 23-OCT-19
24-OCT-19 24-OCT-19 1 24-OCT-19
24-OCT-19 1 25-OCT-19
0 26-OCT-19
0 27-OCT-19
28-OCT-19 28-OCT-19 1 28-OCT-19
28-OCT-19 1 29-OCT-19
0 30-OCT-19
31-OCT-19 31-OCT-19 1 31-OCT-19
31 rows selected.
--
-- And finally, for any non-null FILL value, I rank them to get my event number
--
SQL> with t as
2 (
3 select
4 case
5 when ( event_flag = 1 and lag(event_flag,1) over ( order by event_dt) = 0 ) or
6 row_number() over ( order by event_dt ) = 1
7 then event_dt end is_last_event,
8 e.*
9 from event_dtl e
10 ), t1
11 as (
12 select
13 case when event_flag = 1 then nvl2(is_last_event,is_last_event,lag(is_last_event ignore nulls) over ( order by event_dt)) end fill,
14 t.*
15 from t
16 )
17 select
18 nvl2(fill,dense_rank() over ( order by fill ),0) as rk,
19 t1.* from t1
20 order by event_dt;
RK FILL IS_LAST_E EVENT_FLAG EVENT_DT
---------- --------- --------- ---------- ---------
1 01-OCT-19 01-OCT-19 1 01-OCT-19
0 0 02-OCT-19
2 03-OCT-19 03-OCT-19 1 03-OCT-19
2 03-OCT-19 1 04-OCT-19
0 0 05-OCT-19
0 0 06-OCT-19
3 07-OCT-19 07-OCT-19 1 07-OCT-19
3 07-OCT-19 1 08-OCT-19
0 0 09-OCT-19
4 10-OCT-19 10-OCT-19 1 10-OCT-19
4 10-OCT-19 1 11-OCT-19
0 0 12-OCT-19
0 0 13-OCT-19
5 14-OCT-19 14-OCT-19 1 14-OCT-19
5 14-OCT-19 1 15-OCT-19
0 0 16-OCT-19
6 17-OCT-19 17-OCT-19 1 17-OCT-19
6 17-OCT-19 1 18-OCT-19
0 0 19-OCT-19
0 0 20-OCT-19
7 21-OCT-19 21-OCT-19 1 21-OCT-19
7 21-OCT-19 1 22-OCT-19
0 0 23-OCT-19
8 24-OCT-19 24-OCT-19 1 24-OCT-19
8 24-OCT-19 1 25-OCT-19
0 0 26-OCT-19
0 0 27-OCT-19
9 28-OCT-19 28-OCT-19 1 28-OCT-19
9 28-OCT-19 1 29-OCT-19
0 0 30-OCT-19
10 31-OCT-19 31-OCT-19 1 31-OCT-19
31 rows selected.