Forum Stats

  • 3,734,274 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

How to join one dynamic date table with my own table

Luffy
Luffy Member Posts: 35 Red Ribbon
edited Mar 12, 2021 3:49AM in SQL & PL/SQL

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.

Tagged:

Best Answers

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown
    Accepted Answer

    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
  • Luffy
    Luffy Member Posts: 35 Red Ribbon
    edited Mar 12, 2021 10:45AM Accepted Answer
    Thank you @comarco . You have an awesome answer for me to solve all things. The first time I know about cross apply
    


Answers

  • KayK
    KayK Member Posts: 1,639 Bronze Crown

    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
    Luffy Member Posts: 35 Red Ribbon

    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
    cormaco Member Posts: 1,559 Bronze Crown
    Accepted Answer

    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
  • Luffy
    Luffy Member Posts: 35 Red Ribbon
    edited Mar 12, 2021 10:45AM Accepted Answer
    Thank you @comarco . You have an awesome answer for me to solve all things. The first time I know about cross apply
    


Sign In or Register to comment.