Oracle SQL with Date Range

Question

I have this table with date range:

SID	StartDate	EndDate	CID	Time_Stamp
1001	2021-04-14	2021-04-15	1002	2021-04-14
1003	2021-04-14	2021-04-16	1004	2021-04-14

What I need is to break down the dates so it would look like this.

SID	StartDate	EndDate	CID	Time_Stamp
1001	2021-04-14	2021-04-15	1002	2021-04-14
1001	2021-04-15	2021-04-15	1002	2021-04-14
1003	2021-04-14	2021-04-16	1004	2021-04-14
1003	2021-04-15	2021-04-16	1004	2021-04-14
1003	2021-04-16	2021-04-16	1004	2021-04-14

I have the following code but it does not break down the dates and only gives the date range.

if trunc(EndDate) > trunc(StartDate) then
   lv_start_date := trunc(StartDate);
   WHILE trunc(lv_start_date) <= trunc(EndDate) LOOP
     insert into mytable values (SID, trunc(lv_start_date), trunc(EndDate), 
                                        CID,Time_Stamp);
     commit;
     lv_start_date := lv_start_date + 1;
   END LOOP;
end if;

Is there any way to improve the code to get the expected result?

Littlefoot · Accepted Answer · 2021-04-14 04:23:18Z

1

A little bit of hierarchical query and you get what you want:

SQL> select sid,
  2         startdate + column_value - 1 as startdate,
  3         enddate,
  4         cid,
  5         time_stamp
  6  from test cross join
  7    table(cast(multiset(select level from dual
  8                        connect by level <= enddate - startdate + 1
  9                       ) as sys.odcinumberlist))
 10  order by sid, startdate;

       SID STARTDATE  ENDDATE           CID TIME_STAMP
---------- ---------- ---------- ---------- ----------
      1001 2021-04-14 2021-04-15       1002 2021-04-14
      1001 2021-04-15 2021-04-15       1002 2021-04-14
      1003 2021-04-14 2021-04-16       1004 2021-04-14
      1003 2021-04-15 2021-04-16       1004 2021-04-14
      1003 2021-04-16 2021-04-16       1004 2021-04-14

SQL>

answered Apr 14, 2021 at 4:23

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kmar Over a year ago

This worked as well. What about if I wanted to delete the result I got from the select statement? How would it look like?

Littlefoot Over a year ago

One option might be this: delete from test where sid in (select sid from test cross join ... as sys.odcinumberlist)));

Collectives™ on Stack Overflow

Oracle SQL with Date Range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related