Help with Query----Eeek

Analytics
Fan

Help with Query----Eeek

Hi, I am trying to get all results by member number on one line, so for example I would like to see results in table that look like this on one line per membernumber.

 

Note_ck, Noteentereddate, MemberNumber, QuestionOrder, QuestionTxt 1, Response, questiontxt2, response, questiontxt3, response, MemberName, Region

123546,

 

Right now with the following query I am getting results that look like this: I would like all the bolded question text with associated answers per member.

 

236,523 11/16/2016 584201 0 Field Worker Name Margarita.Merjil MARY CLARK Clarksville
236,523 11/16/2016 584201 18 Date Start 2017-01-09 00:00:00 MARY CLARK Clarksville
236,523 11/16/2016 584201 19 Date Start Time 2017-01-09T09:00:00 MARY CLARK Clarksville
236,523 11/16/2016 584201 20 Date End 2017-01-09 00:00:00 MARY CLARK Clarksville
236,523 11/16/2016 584201 21 Date End Time 2017-01-09T13:00:00 MARY CLARK Clarksville

thanks to whoever can help!

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 SK_2_SP_REF.MEMBER_NAME ORDER BY N_ENTERED.DATE_DATE DESC) = 1

order by 3

 

thanks to whoever can help!