SELECT dbo.PAT_Number(dbo.patient._id) as [Pat No],
dbo.address.postcode as [Postcode],
CONVERT(smallint, DATEDIFF(day, dbo.patient.date_of_birth, getdate()) / 365.25) AS [Age Now],
dbo.patient_main.sex as [Gender],
dbo.patient_main.ethnic_origin AS [E Code],
dbo.patient_main.ethnic_origin_userdesc AS [Ethnic Desc],
dbo.referral.patient_date as [Ref Date],
dbo.episode.patient_date AS [Epi Start],
dbo.episode.finish_date AS [Epi End],
dbo.episode.diagnosis__1 AS [D Code],
dbo.episode.diagnosis__1_userdesc AS Diagnosis,
dbo.contact.patient_date as [Con Date],
dbo.contact.activity__1 as [Act 1],
dbo.contact.activity__2 as [Act 2],
dbo.contact.activity__3 as [Act 3],
dbo.contact.new_ongoing_closed as [C Type],
dbo.staff.team as [S Team], --This will be from the episode since we are linked to
dbo.staff.code as [S Code], --staff from the episode.staff to staff._id
dbo.project.name as [Research Project Name],
dbo.research.patient_date AS [Res Date],
dbo.research.field_8 AS [Nature of Service],
dbo.research.field_9 AS [On CPReg],
dbo.research.field_11 AS [Severe/Mod LD],
dbo.research.field_14 AS [Serv Tier]

FROM dbo.episode
LEFT OUTER JOIN dbo.patient
ON dbo.episode.patient = dbo.patient._id
LEFT OUTER JOIN dbo.staff
ON dbo.episode.staff = dbo.staff._id
left join dbo.referral
on dbo.episode.referral = dbo.referral._id
left join 
(Select Min(patient_date),activity__1,activity__2,activity__3,episode From dbo.contact Group by 
activity__1,activity__2,activity__3,episode) as contact
on dbo.episode._id = dbo.contact.episode
left join dbo.patient_main
on dbo.patient._id = dbo.patient_main._id
LEFT OUTER JOIN dbo.research
ON dbo.patient._id = dbo.research.patient
LEFT JOIN dbo.project
ON dbo.research.project = dbo.project._id
LEFT JOIN dbo.address
ON dbo.patient._id= dbo.address.patient


WHERE len(dbo.episode.finish_date) = 0 AND
dbo.staff.team IN ('ONE','TWO','THREE')AND
dbo.contact.activity__1 IN ('ATT', 'LATE SEEN') AND
dbo.address.category IN ('PAT') AND
len(dbo.address.end_date) = 0

ORDER BY [Pat No], [Ref Date], [Epi Start], [Con Date]