Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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 16-Aug-12 23:47pm
Edited 16-Aug-12 23:49pm
v2
Comments
Santhosh Kumar J at 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
good
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.
  Permalink  
Comments
@amitgajjar at 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
0 Maciej Los 295
1 OriginalGriff 273
2 Aajmot Sk 234
3 Marcin Kozub 205
4 Richard MacCutchan 200
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 17 Aug 2012
Copyright © CodeProject, 1999-2014
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