How to find if one record contains a value

I have a query where I the fields Patient Name, Contact Date, and a field that shows if a certain type of note was used during the contact called Teach Back YN. The type of note field is brought into the query from a temp table. There can be many notes for each contact date. Per contact date, if any of the notes are of the type "Teach Back" then the value of the Teach Back YN field should be "Y" if none of the notes are of the type "Teach Back" the the value of the Teach Back YN field should be "N." Right now I am using a case statement to check the Note Type field but that returns both Y and N values for the Teach Back YN field when a contact date has notes that have a type of "Teach Back" and at the same time there are other types within the contact date. So there are multiple records for each contact date. I was wondering how I can show one record for each Contact Date and only a Y or and N for Teach Back YN, if one of the multiple notes is of the type "Teach Back" or not?

--Create the table for "Notes"
if OBJECT_ID('tempdb.dbo.#note_lst') is not null drop table #note_lst

SELECT
n.Type
,n.CSN

into #note_lst
FROM NOTES n --There are many note types per CSN

--Compile Query
SELECT
pe.PatientName
,pe.ContactDate
,case when n.Type = 'teach back' then 'Y'
else 'N' end [TEACH_BACK_YN]

FROM PAT_ENC pe
INNER JOIN #note_lst n on n.CSN = pe.CSN -- "CSN" is the Contact ID

SELECT
pe.PatientName
,pe.ContactDate
,MAX(case when n.Type = 'teach back' then 'Y'
else 'N' end) [TEACH_BACK_YN]

FROM PAT_ENC pe
INNER JOIN note_lst n on n.CSN = pe.CSN -- "CSN" is the Contact ID
GROUP BY 
pe.PatientName
,pe.ContactDate

This will pull the max value but the query needs to check all n.Type values per Encounter Date, and then if any one of them equals "teach back" then TEACH_BACK_YN will equal; "Y" else "N"

Y is greater than N. If Y is ever present, it will be the MAX value and will be used; if there is never a Y, then N will be the MAX value.

Did you actually test the query? (I can't test it because you didn't provide any usable sample data.) Or are you just objecting because you don't think it will work?

2 Likes

I apologize! I guess I misunderstood you! I will try that.

I believe that this worked! Thank you very much!