SELECT DISTINCT C.customer_key, SR.source_id, SR.wholesaler_code, SR.wholesaler_branch_code, SR.customer_code, SR.customer_name, SR.address_1+', '+ SR.address_2+', '+ CASE WHEN SR.address_3 = '' OR SR.address_3 IS NULL THEN '' ELSE SR.address_3 END +', '+postal_code AS [address], ISNULL(M.orgcode,1) AS orgcode, B.bricknumber AS brick_id, 'N' as customer_comark, 'N' AS customer_growth, 'N' AS customer_pn --SELECT DISTINCT SR.*,c.source_ind ,c.wholesaler_branch_code--,W.wholesaler_branch_code FROM stagedw.dbo.wholesaler_customer_pharmed_kzn_src SR --(889 row(s) affected) INNER JOIN idxdw.dbo.idx_aspen_inmarket_customer C ON SR.source_id = C.source_ind --'PHARM' AND SR.wholesaler_code = C.wholesaler_code AND SR.wholesaler_branch_code = C.wholesaler_branch_code AND SR.customer_code = C.customer_code LEFT OUTER JOIN edw.dbo.dim_aspen_inmarket_customer IC ON SR.customer_code = IC.customer_code AND SR.wholesaler_code = IC.wholesaler_code AND SR.wholesaler_code = IC.wholesaler_code AND SR.source_id = IC.source_id LEFT OUTER JOIN JNBVDW02A.edw.dbo.dim_ims_gsk_wholesaler W ON --C.wholesaler_branch_code = 'KZN' -- GTNG needs to be added.. W.wholesaler_branch_code --AND C.wholesaler_code = W.wholesaler_code LEFT OUTER JOIN (SELECT wholesalerid,accnumber,orgcode,updatetype,d.wholesalerlinkid FROM JNBVDW02A.pharmacare.dbo.medpages_wholesaler_org_link t LEFT JOIN stagedw.dbo.st_medpages_wholesaler_org_link d ON d.wholesalerlinkid = t.wholesalerlinkid WHERE d.wholesalerlinkid IS NULL AND updatetype <> 'D' ) AS M ON W.wholesaler_distributor_code = M.wholesalerid AND c.customer_code = M.accnumber LEFT OUTER JOIN (SELECT DISTINCT orgcode,bricknumber FROM JNBVDW02A.pharmacare.dbo.medpages_organisation ) AS B ON M.orgcode = B.orgcode WHERE IC.customer_code IS NULL
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)