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