SELECT first_date, last_date, work_code,TO_CHAR(first_date,'Day') "FROM",TO_CHAR(last_date,'Day') "TO",'Sick',SUM(hrs) FROM (SELECT ptd.*
FROM   pd_trf_day  ptd, work_code wc, person p, vo_person_category vopc
WHERE ptd.work_code_id = wc.work_code_id
AND wc.work_code_std_category_id = 2
AND ptd.person_id = p.person_id
AND p.vo_person_category_id = vopc.vo_person_category_id
AND TO_CHAR(ptd.date_day, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN') -- not sure if weekends should be excluded since there are records for them
AND (:person_id IS NULL OR ptd.person_id = :person_id) -- person_id param
AND (:vehicle_owner_id IS NULL OR vopc.vehicle_owner_id = :vehicle_owner_id) -- vehicle_owner_id param
AND (:vo_person_category_id IS NULL OR vopc.vo_person_category_id = :vo_person_category_id) -- vo_person_category_id param
AND (:from_date IS NULL OR ptd.date_day >= :from_date) -- from_date param
AND (:TO_DATE IS NULL OR ptd.date_day <= :TO_DATE) -- to_date param
)
match_recognize (
  ORDER BY date_day
  measures
    FIRST(date_day) AS first_date,
    LAST(date_day) AS last_date,
    NVL(trf_dhr,0) AS hrs,
    COUNT(*) AS days,
    work_code_id AS work_code,
    match_number() AS grp
  pattern ( strt consecutive* )
  define
    consecutive AS date_day = ( prev ( date_day ) + 1 )
)
GROUP BY first_date,last_date,work_code
ORDER BY first_date;