|
Vivienne Shaw
Starting Member
2 Posts |
 Posted - 2006-08-17 : 05:30:00
|
| I have the following tables: - referrals, episodes and contacts. There will be one referral, there could be several episodes Linked to the referral and several contacts linked to each episode. If there is a link from the episode to the referral there will also be a link from the contact to the referral.What I want to do is return the min(contact.date) for each episode.I can't make it work. I have tried min in the select, where, having. I am thinking I need a sub query somewhere and perhaps it should be in the join. BUT HOW?This is my code so far: -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 dbo.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) = 0ORDER BY [Pat No], [Ref Date], [Epi Start], [Con Date] |
|
Min - HELP PLEASE