December 4, 2015 at 10:38 am
I've Next Question . I am currently working on a location dimension. The following is not going so well for me. I want to be a customer who has several addresses flatten to one customer. So if there is a type 5 or 1 then I want to have the telephone number and mail in one column but does not occur more than once in the same customer code . You know how I have to change my syntax than the case statement I made now ?
SELECT
DISTINCT CT.Accountnum AS CustomerNumber
,UPPER(CAST(CT.DataAreaID as nvarchar(250))) AS DataAreaID
,DPT.Name AS CustomerName
,DPPA.Street AS BillingAddressStreet
,DPPA.ZipCode AS BillingAddressPostalCode
,DPPA.City AS BillingAddressCity
,DPPA.CountryRegionId AS BillingAddressCountry
,CASE
WHEN DPCI.Type = 1
THEN DPCI.Locator
ELSE NULL
END AS TelephoneNumber
, CASE
WHEN DPCI.Type = 5
THEN DPCI.Locator
ELSE NULL
END AS FaxNumber
,CASE
WHEN DPCI.Type = 3
THEN DPCI.Locator
ELSE NULL
END AS Website
,CASE
WHEN DPCI.Type = 2
THEN DPCI.Locator
ELSE NULL
END AS EmailAddress
,'Customer' AS BusinessRelationShip
,CT.LineOfBusinessId AS CustomerType
--PrimaryCustomerSector
--SecondaryCustomerSector
,CT.Segmentid AS PrimaryIndustrySegment
,CT.SPLSecondBusiness AS SecondaryIndustrySegment
FROM AX2012.CustTable CT
inner JOIN AX2012.DirPartyTable DPT
ON DPT.Recid = CT.Party
INNER JOIN AX2012.DirPartyPostalAddressView DPPA
ON CT.Party = DPPA.Party
INNER JOIN AX2012.DirPartyContactInfoView DPCI
ON CT.Party = DPCI.Party
where DPPA.ISPRIMARY = 1
AND DPCI.ISPRIMARY = 1
AND DPPA.VALIDTO = '2154-12-31 23:59:59.000'
Order by CT.ACCOUNTNUM
How i get it from 1 customer! Look picture for results.
December 5, 2015 at 8:19 am
Please see the following...
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply