Trying to get all the results from one membernumber (member) onto one line. Cannot seem to figure out the logic.
,N_ENTERED.DATE_DATE as NoteEnteredDate
,fn.MEMBER_CCMS_ID as MemberNumber
,DIM_NOTE_QUESTION.QUESTION_SEQ as QuestionOrder
,DIM_NOTE_QUESTION.QUESTION as QuestionText
,FT_NOTE_ANSWER.RESPONSE as Response
,SK_2_SP_REF.MEMBER_NAME as MemberName
FROM FT_NOTE fn
inner JOIN HP_TX_OWN_TABLES.SK_2_SP_REF
inner JOIN DIM_DATE N_ENTERED
--and N_ENTERED.DATE_DATE between ........
inner JOIN DIM_NOTE_TYPE
inner JOIN DIM_NOTE_QUESTION
inner JOIN FT_NOTE_ANSWER --for better performance it appears that question text, number, note type name are also accounted for in columns in FT_NOTE_ANSWER
WHERE fn.PLAN_DIM_Ck IN ('12','61','20') --12=medicaid, 20=mediare, 61=Ambeter/Exchange
AND fn.source_dim_ck=45 --45=sourced from TruCare
and FT_NOTE_ANSWER.RESPONSE not ='?'
AND DIM_NOTE_QUESTION.QUESTION in ('Field Worker Name','Date Start','Date End','Date Start Time','Date End Time') qualify rank() over (PARTITION BY fn.MEMBER_CCMS_ID ORDER BY N_ENTERED.DATE_DATE DESC) = 1
order by 3