WITH base_data AS (SELECT PdTrfDay.date_day,PdTrfDay.person_id,PdTrfDay.work_code_id, NVL(PdTrfDay.trf_dhr,0) trf_dhr, WorkCode.WORK_CODE_NAME
FROM pd_trf_day PdTrfDay, work_code WorkCode
WHERE PdTrfDay.work_code_id = WorkCode.work_code_id
AND WorkCode.work_code_std_category_id = 2
--and PdTrfDay.person_id = 471152 and date_day between to_date('13/01/2023 00:00:00', 'DD/MM/YYYY HH24:MI:SS') and to_date('15/01/2023 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
)
SELECT "PERSON_ID","FIRST_DATE","LAST_DATE","SICK_DAYS_IN_A_ROW","SUMMED_HRS","MATCHES", "WORK_CODE_ID","WORK_CODE_NAME", "CL" FROM base_data
match_recognize(
PARTITION BY person_id, work_code_id
ORDER BY date_day
measures
NVL(FIRST(date_day),date_day) AS first_date,
NVL(LAST(date_day),date_day) AS last_date,
DECODE(COUNT(*),0,1,COUNT(*)) AS sick_days_in_a_row,
NVL(SUM(trf_dhr),trf_dhr) AS summed_hrs,
match_number() AS matches,
work_code_id AS wc_id,
work_code_name AS work_code_name,
classifier() AS CL
pattern (strt consecutive*)
define
consecutive AS date_day = (prev(date_day)+1) AND work_code_id = consecutive.work_code_id
)
ORDER BY person_id,first_date,last_date;