Attempting to get all results on 1 line based on the MemberNumber. So for each membernumber one line would portray all results.
,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
The above query is giving the details per MEMBER_CCMS_ID for the lastest N_ENTERED.DATE_DATE.
In doing so it is doing the following steps:
1. it is joining all the tables with a inner join based on the joining columns and getting only the values that are common in all the tables
2. Then it is partitioning i.e returning only one entry per MEMBER_CCMS_ID and selecting the latest N_ENTERED.DATE_DATE row in case of conflict
3. The final results are then sorted in ascending order basis the member ID