Need Help with Query Logic

Database
Fan

Need Help with Query Logic

Attempting to get all results on 1 line based on the MemberNumber. So for each membernumber one line would portray all results.

 

 

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

 

 

1 REPLY

Re: Need Help with Query Logic

Hello,

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

 

Cheers !