Joining IRA (CF) tables

binbh commented 1 year ago

Since we are trying to join IRA tables with other IRA tables we should use CF fields (INSITUTION_CODE, STRM) instead of IR legacy fields (COLLEGE_IR, TERM_DATE). Maybe it is something we can discuss at the next meeting.

binbh commented 1 year ago

Here is an example of what I mean

--student enrollment table
from IRDB_DW.WC_IRA_PS_STDNT_CAR_TERM_TS CART

--institution table that has two John Jay records since John Jay was comprehensive college and now senior college        
inner join IRDB_DW.WC_IRA_INSTITUTION_D INST
        on CART.INSTITUTION = INST.IR_INSTITUTION_CODE
       and CART.STRM between INST.IR_BEGIN_TERM_CODE
                         and INST.IR_END_TERM_CODE

MarkCasazza commented 1 year ago

This type of coding needs to be done with LU_PS_INSTRUCT_MODE as well now that there are two “O” records, one for prior to June 2020 when it meant hybrid and one for June 2020 to the present where it’s online… the begin/end STRM values are structured the same way.

-mvc

Mark Casazza Director, CUNY Business Intelligence City University of New York Office of Applied Research, Evaluation, and Data Analytics (OAREDA) Data and Reporting Unit 555 West 57th Street, Suite 1140 New York, NY 10019

Mark.Casazza10 @.**@.> http://cuny.edu/ir

From: binbh @.> Reply-To: betsyCC/OAREDA_Syntax_Library @.> Date: Wednesday, January 18, 2023 at 13:28 To: betsyCC/OAREDA_Syntax_Library @.> Cc: Subscribed @.> Subject: Re: [betsyCC/OAREDA_Syntax_Library] Joining IRA (CF) tables (Issue #14)

ATTENTION: This email came from an external source. Do not open attachments or click on links from unknown senders or unexpected emails.

Here is an example of what I mean

--student enrollment table

from IRDB_DW.WC_IRA_PS_STDNT_CAR_TERM_TS CART

--institution table that has two John Jay records since John Jay was comprehensive college and now senior college

inner join IRDB_DW.WC_IRA_INSTITUTION_D INST

    on CART.INSTITUTION = INST.IR_INSTITUTION_CODE

   and CART.STRM between INST.IR_BEGIN_TERM_CODE

                     and INST.IR_END_TERM_CODE

— Reply to this email directly, view it on GitHubhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_betsyCC_OAREDA-5FSyntax-5FLibrary_issues_14-23issuecomment-2D1387525281&d=DwMCaQ&c=mRWFL96tuqj9V0Jjj4h40ddo0XsmttALwKjAEOCyUjY&r=FW1qjambfYDPaDeXWU8eVRHudNTomyvPglsIs-VyQ6Y&m=yBk53F8gVQK3aXEHck1nJ_QTqVHdRiBkBwI5VAHeCXWgGwb_C6cAuVVrg0WQZKhh&s=TFI4l0f6xiu2SAIzgDv3zky2-Q45hniQeb8kFqNP7W0&e=, or unsubscribehttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_ABNWQE4MDLKI4Y6NG3DNCYDWTAY4DANCNFSM6AAAAAAT7M4G7Q&d=DwMCaQ&c=mRWFL96tuqj9V0Jjj4h40ddo0XsmttALwKjAEOCyUjY&r=FW1qjambfYDPaDeXWU8eVRHudNTomyvPglsIs-VyQ6Y&m=yBk53F8gVQK3aXEHck1nJ_QTqVHdRiBkBwI5VAHeCXWgGwb_C6cAuVVrg0WQZKhh&s=KYqBzh6RhBlXJZ14VxovHrWx5WNiY47nVky7_8b6_Cw&e=. You are receiving this because you are subscribed to this thread.Message ID: @.***>

betsyCC / OAREDA_Syntax_Library

Joining IRA (CF) tables #14