September 10, 2015 at 8:35 am
Hi
I am struggling to pull correct data when linking tables. I am linking a table of distinct referrals to a table with multiple treatments for each referral. I want to get the first treatment and the first treatment date. I am using this code below. However I am getting duplicates because some records have a NULL Treatment type as well as a First treatment flag both with different dates. I need a CASE statement somewhere to ignore the NULLs if there is a 'First defnitive treatment' but I still need the NULLS for those that dont have a treatment. Can anyone help.
select 1.CAREID
,1.referral date
,2.Min(Treatment date) as FIRSTTREATMENT
,2.TreatmentType
from referraltable as 1
left join treatmenttable as 2 on 1.CAREID = 2.CAREID
where 2.TreatmentType = 'First defnitive treatment' or 2.TreatmentType is null
September 10, 2015 at 8:55 am
The query you posted isn't syntactically correct. Is this SQL Server or some other RDBMS, or are you just posting pseudo-code?
Anyway, if I understand correctly, all you need to do is wrap Treatmentdate in a MAX in your select list.
John
September 10, 2015 at 9:08 am
Sorry I should have parsed to make sure it was grammatically correct. This seems to work now
select A.CAREID
,A.referraldate
,Min(B.[Treatmentdate]) as FIRSTTREATMENT
,B.TreatmentType
from referraltable as A
left join treatmenttable as B on A.CAREID = B.CAREID
where TreatmentType = 'First definitive treatment' or TreatmentType is null
If I understand you correctly though, wrapping Treatmentdate in a MAX would only give me the last date. I want the earliest date available but currerntly I get duplicates when there is both a NULL Treatment Type and 'First definitive treatment' recorded in my treatmenttable. I want to ignore the NULLS when there is 'First definitive treatment' recorded.
thanks for you help
September 10, 2015 at 9:19 am
lawrence.simpson (9/10/2015)
If I understand you correctly though, wrapping Treatmentdate in a MAX would only give me the last date.
Not sure whether I'm missing something here, but why not use MIN instead?
John
September 10, 2015 at 9:20 am
lawrence.simpson (9/10/2015)
Sorry I should have parsed to make sure it was grammatically correct. This seems to work nowselect A.CAREID
,A.referraldate
,Min(B.[Treatmentdate]) as FIRSTTREATMENT
,B.TreatmentType
from referraltable as A
left join treatmenttable as B on A.CAREID = B.CAREID
where TreatmentType = 'First definitive treatment' or TreatmentType is null
If I understand you correctly though, wrapping Treatmentdate in a MAX would only give me the last date. I want the earliest date available but currerntly I get duplicates when there is both a NULL Treatment Type and 'First definitive treatment' recorded in my treatmenttable. I want to ignore the NULLS when there is 'First definitive treatment' recorded.
thanks for you help
As posted there is no way this works. You have an aggregate but no group by. What would really help is some ddl and sample data. Please take a few minutes and read the first link in my signature for best practices when posting questions. I suspect this is an easy fix but there just isn't much detail posted yet.
_______________________________________________________________
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/
September 10, 2015 at 9:49 am
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.
September 10, 2015 at 10:16 am
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply