lawrence.simpson (9/10/2015)
Apologies rookie mistakeThis 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.
We can help but you need to give us something to work with. We can't write queries against a little bit of data. The article I referenced shows examples of what you should post. Yes it takes some effort from you but you will be rewarded with tested, accurate and fast code by the volunteers around here who do this stuff for free because we enjoy it. And of course make sure you anonymize your data. We don't want or need actual data but enough to demonstrate the scope of the issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/