with ztp_PH_data as
(SELECT distinct Ambassador_Name as Name,
        Review_Date,
        Violation_Category,
        Causal_Factor,
        TID_in_NICE
FROM `ZTP`
where Violation_Category is not null
and Review_Date <= cast('{0}' as date)
and 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