Click here to Skip to main content
12,895,911 members (47,791 online)
Rate this:
Please Sign up or sign in to vote.
	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
		--C.wholesaler_branch_code = 'KZN' -- GTNG needs to be added.. W.wholesaler_branch_code
	 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
					DISTINCT orgcode,bricknumber 
				FROM JNBVDW02A.pharmacare.dbo.medpages_organisation
				) AS B
	ON M.orgcode = B.orgcode
WHERE IC.customer_code IS NULL
Posted 16-Aug-12 22:47pm
Updated 16-Aug-12 22:49pm
Santhosh Kumar J 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

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.
@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)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170424.1 | Last Updated 17 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100