Assuming version 11.4. here is a place to start from:
Select tk.WOID, tk.TASK, Case When IsNull(tk.CLSDDATE,GetDate()+1) > GetDate() then 'OPEN' else 'CLOSED' end as State
, cr.Instructions, cr.DueDate, crs.ChangeRequestStatusName
, dec.ReviewerFULLNAME, usr.EMAILADDR, dec.DecisionDate
from TASKS tk
inner join ChangeRequest cr on (tk.WOID = cr.WOID)
inner join ChangeRequestStatus crs on (cr.ChangeRequestStatusId = crs.ChangeRequestStatusId)
inner join Decision dec on (cr.ChangeRequestId = dec.ChangeRequestId)
inner join TIUSER usr on (dec.ReviewerUSERID = usr.USERID)
This will result in a listing of all Work Orders with a CFR and the people whose input was requested. You can then filter on the task state, the CFR status, and whether there was a response.
If you only wanted the assigned technician's email, cut out the Decision table/fields and change the join for TIUser to
inner join TIUSER usr on (tk.RESPONS = usr.FULLNAME)
I hope this helps!
Thank you very much
Let me try this and see what results i get