Extract Transform Load Prescription-Claim-Insert Stored-Procedure

                  SET 
                    ANSI_NULLS ON GO 
                  SET 
                    QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[blindedclientsRXClaimInsert] as begin 
                  set 
                    nocount on;
              insert into blindedclients_mdm.dbo.ndc (ndcnumber, ndcname) 
              select 
                distinct ltrim(
                  rtrim(rx1.[RX_NDC_ID_CD])
                ), 
                ltrim(
                  rtrim(rx1.drugname)
                ) 
              from 
                (
                  select 
                    [RX_NDC_ID_CD], 
                    max(DRUG_NAME) as drugname 
                  from 
                    blindedclients_mdm.dbo.rx 
                  where 
                    [RX_NDC_ID_CD] is not null 
                  group by 
                    [RX_NDC_ID_CD]
                ) as rx1 
                left join blindedclients_mdm.dbo.ndc as rx2 on ltrim(
                  rtrim(rx1.[RX_NDC_ID_CD])
                ) = rx2.ndcnumber 
              where 
                rx2.ndcnumber is null if object_id(
                  '#regencerxclaiminserttable', 'u'
                ) is not null begin 
              drop 
                table end;
              with as (
                select 
                  distinct coalesce(
                    mem.memberid, 
                    case when rx.primy_grp_id_cd = '10012785' then -3 when rx.primy_grp_id_cd = '63000000' then -4 when rx.primy_grp_id_cd = '10007440' then -6 when rx.primy_grp_id_cd = '10015894' then -13 when rx.primy_grp_id_cd = '10002156' then -20 when rx.primy_grp_id_cd = '10017746' then -25 when rx.primy_grp_id_cd = '10016010' then -12 when left(rx.primy_grp_id_cd, 2) = '68' then -26 when rx.primy_grp_id_cd = '63000002' then -27 when rx.primy_grp_id_cd = '63000001' then -27 when rx.[PRIMY_GRP_ID_CD] = '10019108' then -30 end
                  ) as [memberid], 
                  concat(
                    ltrim(
                      rtrim(rx.[CLM_ID_CD])
                    ), 
                    '-', 
                    ltrim(
                      rtrim(rx.[CLI_NUM_CD])
                    ), 
                    '-', 
                    rx.PD_AMT, 
                    '-', 
                    rx.BILLD_CHRG_AMT
                  ) as [rxclaimnumber], 
                  '' as [writtendate], 
                  case when rx.[CDM_RX_FILL_DT_KEY] = 0 then '' else cast(
                    cast(
                      rx.[CDM_RX_FILL_DT_KEY] as varchar(10)
                    ) as date
                  ) end as [filleddate], 
                  case when rx.[PROC_DT] = 0 then '' else cast(
                    cast(
                      rx.[PROC_DT] as varchar(10)
                    ) as date
                  ) end as [paiddate], 
                  coalesce(ndc.ndcid,-1) as [ndcid], 
                  -1 as [pharmacyid], 
                  -1 as [providerid], 
                  case when cast(
                    cast(rx.[NEW_RFILL_CNT] as int) as varchar(15)
                  ) = 0 then 0 else 1 end as [newrefillcode], 
                  cast(
                    rx.[RX_DAW_CD] as varchar(15)
                  ) as [dawcode], 
                  null as [compoundflag], 
                  null as [formularyflag], 
                  null as [rxtier], 
                  -1 as [drugtypeid], 
                  cast(rx.[RX_QTY_PD_CNT] as int) as [metricquantity], 
                  case when cast(rx.[BILLD_CHRG_AMT] as float) = 0 then cast(
                    replace(rx.[RX_DAYS_SPLY_CNT], '-', '') as int
                  )*-1 else cast(rx.[RX_DAYS_SPLY_CNT] as int) end as [daysupply], 
                  cast(rx.[BILLD_CHRG_AMT] as float) as [billedamount], 
                  cast(rx.[PD_AMT] as float) as [paidamount], 
                  cast(rx.[RX_COPAY_AMT] as float) as [copayamount], 
                  cast(rx.[RX_APLD_DED_AMT] as float) as [deductibleamount], 
                  coalesce(
                    cast(rx.[COIN_AMT] as float), 
                    ''
                  ) as [coinsuranceamount], 
                  cast(
                    rx.[RX_INGRDT_COST_PD_AMT] as float
                  ) as [ingredientcost], 
                  cast(
                    rx.[RX_DSPNSG_FEE_PD_AMT] as float
                  ) as [dispensingfee], 
                  cast(
                    rx.[RX_SALES_TAX_PD_AMT] as float
                  ) as [taxamount], 
                  0 as [hraamount], 
                  concat(
                    ltrim(
                      rtrim(rx.[CLM_ID_CD])
                    ), 
                    '-', 
                    ltrim(
                      rtrim(rx.[CLI_NUM_CD])
                    ), 
                    '-', 
                    rx.PD_AMT, 
                    '-', 
                    rx.BILLD_CHRG_AMT
                  ) as [sourcerecordid] 
                from 
                  blindedclients_mdm.dbo.rx as rx 
                  left join blindedclients_mdm.dbo.ndc ndc on coalesce(
                    ltrim(
                      rtrim(rx.[RX_NDC_ID_CD])
                    ), 
                    'QQQQQ'
                  ) = ndc.ndcnumber 
                  left join (
                    select 
                      max(memberid) as memberid, 
                      sourcerecordid, 
                      companyid 
                    from 
                      blindedclients_mdm.dbo.member 
                    group by 
                      sourcerecordid, 
                      companyid
                  ) as mem on concat(
                    ltrim(
                      rtrim(rx.CURR_CTG_PERS_KEY_CD)
                    ), 
                    '-', 
                    rx.[PRIMY_GRP_ID_CD]
                  ) = mem.sourcerecordid 
                  and mem.companyid in (3, 4, 12, 13, 20, 25, 26, 27, 30) 
                  left join blindedclients_mdm.dbo.rxclaim rxc on concat(
                    ltrim(
                      rtrim(rx.[CLM_ID_CD])
                    ), 
                    '-', 
                    ltrim(
                      rtrim(rx.[CLI_NUM_CD])
                    ), 
                    '-', 
                    rx.PD_AMT, 
                    '-', 
                    rx.BILLD_CHRG_AMT
                  ) = rxc.[rxclaimnumber] 
                  and rxc.sourcerecordid is not null 
                where 
                  rxc.rxclaimid is nulL
              ) 
              select 
                * into 
              from 
                insert into blindedclients_mdm.[dbo].[rxclaim] (
                  [memberid], [rxclaimnumber], [writtendate], 
                  [filleddate], [paiddate], [ndcid], 
                  [pharmacyid], [providerid], [newrefillcode], 
                  [dawcode], [compoundflag], [formularyflag], 
                  [rxtier], [drugtypeid], [metricquantity], 
                  [daysupply], [billedamount], [paidamount], 
                  [copayamount], [deductibleamount], 
                  [coinsuranceamount], [ingredientcost], 
                  [dispensingfee], [taxamount], [hraamount], 
                  [sourcerecordid]
                ) 
              select 
                * 
              from 
              drop 
                table end GO