Help with Query- Please read

Analytics
Fan

Help with Query- Please read

Trying to get all the results from one membernumber (member) onto one line. Cannot seem to figure out the logic.

 

 

SELECT DISTINCT

fn.NOTE_CK

,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

,SK_2_SP_REF.Region

 

FROM FT_NOTE fn

 

inner JOIN HP_TX_OWN_TABLES.SK_2_SP_REF

on fn.MEMBER_CCMS_ID=SK_2_SP_REF.CCMS_ID

 

inner JOIN DIM_DATE N_ENTERED

ON fn.NOTE_DATE_DIM_CK=N_ENTERED.DATE_DIM_CK

--and N_ENTERED.DATE_DATE between ........

 

inner JOIN DIM_NOTE_TYPE

ON DIM_NOTE_TYPE.NOTE_TYPE_DIM_CK=fn.NOTE_TYPE_DIM_CK

inner JOIN DIM_NOTE_QUESTION

ON DIM_NOTE_QUESTION.NOTE_TYPE_DIM_CK=FN.NOTE_TYPE_DIM_CK

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

ON fn.NOTE_CK=FT_NOTE_ANSWER.NOTE_CK

and fn.NOTE_TYPE_DIM_CK=FT_NOTE_ANSWER.NOTE_TYPE_DIM_CK

and DIM_NOTE_QUESTION.NOTE_QUESTION_DIM_CK=FT_NOTE_ANSWER.NOTE_QUESTION_DIM_CK

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