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.