How do I print just 5 pages only based on this query below. It renders sometimes a full month or even 4 days when i try the following dates and disregards the actual date range. The query is linked to Jasper and passes a date range as parameters
to_date('16032020','DDMMYYYY') - to_date('20032020','DDMMYYYY')
to_date('23032020','DDMMYYYY') - to_date('27032020','DDMMYYYY')
to_date('30032020','DDMMYYYY') - to_date('03042020','DDMMYYYY')
to_date('06042020','DDMMYYYY') - to_date('10042020','DDMMYYYY')
Jasper Parameter[
^]
Actual Query
select e.employee_id,
e.initials || ' ' || regexp_replace(e.surname, '\([^()]*\)', '') farm_manager,
r.research_station_id,
r.name,
dr.attendance_date attendance_date
from research_station r
join employees e
on (e.employee_id = r.farm_manager)
cross join (select ($P{Date_from} + rownum - 1) attendance_date
from dual
connect by level <= ($P{Date_from} - sysdate + 1)) dr
join employee_jobs ej
on (e.employee_id = ej.employee_id)
where r.research_station_id = $P{Research_Station_id}
What I have tried:
Test Query
select e.employee_id,
e.initials || ' ' || regexp_replace(e.surname, '\([^()]*\)', '') farm_manager,
r.research_station_id,
r.name,
dr.attendance_date attendance_date
from research_station r
join employees e on (e.employee_id = r.farm_manager)
cross join (select (to_date('16032020','DDMMYYYY') + rownum - 1) attendance_date from dual
connect by level <= to_date('20032020','DDMMYYYY') - sysdate + 1) dr
join employee_jobs ej on (e.employee_id = ej.employee_id)
where r.research_station_id = 2;