How to join one dynamic date table with my own table

Luffy

Hi every one,

My structure table named A is shown as below

Now I want to write report shows student_number, morning, lunch, evening from 01/3 to 30/6 for each day.

My method is

• Create a dynamic date table from start_date and end date from table A with sql code:

select A.student_number, A.morning, A.lunch, A.evening,

  (to_DATE(r.start_date,'dd/mm/RRRR') - 1) + LEVEL dtl,

         to_number(TO_char(to_date((to_date(r.start_date,'dd/mm/rrrr') - 1) + LEVEL), 'DD')) dates,

         to_number(TO_char(to_date((to_date(r.start_date,'dd/mm/rrrr') - 1) + LEVEL), 'MM')) monthss           

         FROM A r

        CONNECT BY LEVEL <= to_DATE(r.end_date,'dd/mm/RRRR') - (to_DATE(r.start_date,'dd/mm/RRRR') - 1)

But it get error when running to end_date day , it stills continue for end_date day (I dont't know how to fix that).

And run that sql statement is very low.

Anyone have a better idea? Thank you for your time and in advance.

cormaco

What is your exact Oracle version? If you are on version 12 or newer, you can use CROSS APPLY like this:

with A(student_number,start_date,end_date,morning,lunch,evening) as (
    select 'GB0117041427',date '2021-03-01',date '2021-06-30',1,1,1 from dual
)
select student_number,one_date,morning,lunch,evening
from A cross apply (select start_date + level - 1 as one_date from dual connect by start_date + level - 1 <= end_date)

Output (truncated):

STUDENT_NUMB ONE_DATE      MORNING      LUNCH    EVENING
------------ ---------- ---------- ---------- ----------
GB0117041427 03/01/2021          1          1          1
GB0117041427 03/02/2021          1          1          1
GB0117041427 03/03/2021          1          1          1
...
GB0117041427 06/28/2021          1          1          1
GB0117041427 06/29/2021          1          1          1
GB0117041427 06/30/2021          1          1          1



Luffy

Thank you @comarco . You have an awesome answer for me to solve all things. The first time I know about cross apply

KayK

Hi Luffy,

i have no solution for you. Only a question. You don't show us the structure of table A, you only show us the first record.

Are the columns start_date and end_date defined as a DATE in your table ? Or as a varchar ? It makes things easier if dates are defined as a date.

Then you do this ...TO_char(to_date((to_date(r.start_date,'dd/mm/rrrr')...

I expect start_date is a varchar then the first to_date makes sense, the second doesn't. To use to_date for a real date may lead to unexpected result.

regards

Kay

Luffy

Hi @KayK ,

Sorry for my typing about structure of table. I would like to show the value and column of A.

Start_date and End_date are Date format. But now I just find out that above code to create a view date. Thank for your idea.😊

cormaco

What is your exact Oracle version? If you are on version 12 or newer, you can use CROSS APPLY like this:

with A(student_number,start_date,end_date,morning,lunch,evening) as (
    select 'GB0117041427',date '2021-03-01',date '2021-06-30',1,1,1 from dual
)
select student_number,one_date,morning,lunch,evening
from A cross apply (select start_date + level - 1 as one_date from dual connect by start_date + level - 1 <= end_date)

Output (truncated):

STUDENT_NUMB ONE_DATE      MORNING      LUNCH    EVENING
------------ ---------- ---------- ---------- ----------
GB0117041427 03/01/2021          1          1          1
GB0117041427 03/02/2021          1          1          1
GB0117041427 03/03/2021          1          1          1
...
GB0117041427 06/28/2021          1          1          1
GB0117041427 06/29/2021          1          1          1
GB0117041427 06/30/2021          1          1          1



Luffy

Thank you @comarco . You have an awesome answer for me to solve all things. The first time I know about cross apply