Question: select all the rows with the date between 2 datetime columns in Oracle

Question

select all the rows with the date between 2 datetime columns in Oracle

Answers 1
Added at 2016-12-26 11:12
Tags
Question

Columns in the table are

ID  Name   SDate            EDate            RNo
1    abc   12/27/2016     12/31/2016       12345

And the result should be

ID  Name   SDate            EDate       MDate              RNo
1    abc   12/27/2016     12/31/2016    12/27/2016         12345
1    abc   12/27/2016     12/31/2016    12/28/2016         12345
1    abc   12/27/2016     12/31/2016    12/29/2016         12345
1    abc   12/27/2016     12/31/2016    12/30/2016         12345
1    abc   12/27/2016     12/31/2016    12/31/2016         12345
Answers to

select all the rows with the date between 2 datetime columns in Oracle

nr: #1 dodano: 2016-12-26 11:12

You can use Hierachical query - CONNECT BY to do so:

select ID, Name, SDate, EDate, 
  sDate + level - 1 MDate,
  RNo
from table
connect by sDate + level - 1 <= EDate;

The above works if source table has only one row.

If ID is unique, Below (as explained by @MathGuy) should work:

select ID, Name, SDate, EDate, 
  sDate + level - 1 MDate,
  RNo
from table
connect by sDate + level - 1 <= EDate
and prior id = id
and prior sys_guid() is not null;

If there is a compound key on the table, just join on all its columns in the same way as I did with id above.

Otherwise, you can use below:

Using CTE

with spread(lvl) as (
    select level - 1
    from (
        select max(EDate - SDate) + 1 maxdiff
        from my_table
    ) connect by level <= maxdiff
)
select t.id, t.Name, t.SDate, t.EDate,
    SDate + lvl MDate
from my_table t inner join spread s
on EDate - SDate >= lvl;

Without CTE

select t.id, t.Name, t.SDate, t.EDate,
    SDate + lvl MDate
from my_table t
inner join (
    select level - 1 lvl
    from (
        select max(EDate - SDate) + 1 maxdiff
        from my_table
    ) connect by level <= maxdiff
) s
on EDate - SDate >= lvl;
Source Show
◀ Wstecz