lawrence.simpson (9/10/2015)

---

Apologies rookie mistake

This is my SQL

SELECT

R.[CARE_ID]

,min (T.[Treatment start date])as DateFirstTreatment

,T.[EVENT_DESC]

FROM [dbo].[BIvwReferrals]as R

left join dbo.BIvwAllTreatments as T on R.[CARE_ID] = T.[CARE_ID]--this links a unique referral to a treatment table

where

(T.[EVENT_DESC] = 'First definitive treatment for new primary cancer' or T.[EVENT_DESC]='First treatment for metastatic disease following an unknown primary' or T.[EVENT_DESC] is null)

and R.[CARE_ID]='12345' --anonymised

GROUP BY

R.[CARE_ID]

,T.[EVENT_DESC]

With results

CARE_IDDateFirstTreatmentEVENT_DESC

123452015-07-10 00:00:00.000NULL

123452015-07-15 00:00:00.000First definitive treatment for new primary cancer

This particular patient has had treatment but my SQL is bringing back 2 results including a NULL. I want to ignore NULL and bring back the date of the First definitive treatment if this is listed. If no First treatment then I need the NULL date. I will be removing the R.[CARE_ID]='12345' condition in the WHERE statement moving forward and will be adding a date condition to pull multiple CARE_IDs in future and I need to avoid duplicate rows.