with ztp_PH_data as
(SELECT distinct fld_name as Name,
        fld_review_date as Review_Date,
        fld_violation_category as Violation_Category,
        fld_causal_factor as Causal_Factor,
        fld_tid_in_nice as TID_in_NICE
FROM `tbl_home_zt_compliance_fact`
where fld_violation_category is not null
and fld_review_date <= cast('{0}' as date)
and fld_review_date >= cast('{1}' as date)
)
,
names_info as (
  SELECT distinct Person_ID as Employee_ID, Name
FROM `weekly_roster`
where Person_ID is not null
)
,
ztp_report as
(select distinct Employee_ID
      ,Name
      ,Review_Date
      ,Violation_Category
      ,Causal_Factor
      ,TID_in_NICE
from ztp_PH_data
left join names_info using(Name)
)
select distinct * from ztp_report