Click here to Skip to main content
15,884,629 members
Please Sign up or sign in to vote.
1.00/5 (5 votes)
SQL
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
Posted
Updated 16-Aug-12 22:49pm
v2
Comments
Santhosh Kumar Jayaraman 17-Aug-12 4:52am    
Why cant you check that? instead of asking us? I hope you have all the tables and sql server

1 solution

This is not a reasonable question. Run it against your data. That's what it executes. Break it down to see what smaller parts of it do, every select in there is a statement you can run on it's own. So, run them. Buy a SQL book and read it. We don't have access to your data and are not going to spend an hour deciphering this when you'd do better to read up on concepts like outer joins ( which just contains all the records in the outer table, even the ones that don't match the inner one ), and play with the data, to learn how it works for yourself.

Overall, this looks like an over complex bit of SQL, it's definitely poorly written. Either way, whoever gave it to you, should explain it to you, if they can. It looks like the blind leading the blind, to me. You'd do better to just learn SQL at a reasonable pace, not because your job expects you to do things you have no idea how to do.
 
Share this answer
 
Comments
AmitGajjar 17-Aug-12 8:53am    
Correct... i don't know why people post bulk of code and expecting explanation.

anyway... 5+ for best answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900