SELECT p.person_id, w.wbs_id,w.name,TRUNC(md.date_day) date_day, p.company_nr, mtsl.hrd, mtsl.validation_status_id, x.sum_hrd FROM mw_time_sheet mts, mw_time_sheet_line mtsl, mfc_mobile_worker mmw, person p, fc_assignment fa, wbs w, mw_day md,
(SELECT SUM(m.hrd) sum_hrd,m.mfc_mobile_worker_id, TRUNC(md.date_day) date_Day FROM mw_time_sheet_line m, mw_day md GROUP BY m.mfc_mobile_worker_id, TRUNC(md.date_day)) x
WHERE mts.mw_time_sheet_id = mtsl.mw_time_sheet_id
AND mtsl.mfc_mobile_worker_id = mmw.mfc_mobile_worker_id
AND mmw.mfc_mobile_worker_id = x.mfc_mobile_worker_id
AND mmw.erp_id = p.person_id
AND p.vo_person_category_id = 102503
AND mtsl.mw_day_id = md.mw_day_id
AND md.date_day = x.date_day
AND x.date_day >= TO_DATE('01/02/2024','DD/MM/YYYY')
AND mtsl.validation_status_id = 48
AND mtsl.fc_assignment_id = fa.fc_assignment_id
--and fa.erp_id = w.wbs_id
AND x.sum_hrd > (SELECT SUM(trf_dhr) FROM pd_trf_day ptd
WHERE TRUNC(ptd.date_day) = TRUNC(md.date_day) --join with mw day
AND ptd.person_id = p.person_id
--and ptd.wbs_id = w.wbs_id
GROUP BY ptd.person_id,TRUNC(ptd.date_day))
ORDER BY 1,4;