Skip to Main Content
  • Questions
  • Find if event spanning several dates happened via SQL

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: October 23, 2019 - 5:36 pm UTC

Answered by: Connor McDonald - Last updated: November 01, 2019 - 1:07 am UTC

Category: SQL - Version: 11.1

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: On Procedures, Flushes, and Writes

You Asked

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.



and we said...

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.



and you rated our response

  (1 rating)

Reviews

Thank You Tom

October 31, 2019 - 4:48 pm UTC

Reviewer: Arun Kumar Jaiswal from India

Hi Tom,

Thank you for your help and your descriptive SQL.I love it.

Arun.
Connor McDonald

Followup  

November 01, 2019 - 1:07 am UTC

glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.