WITH FlattenedLegalEntityCodes AS ( SELECT [CID], STRING_AGG([LegalEntityCode], ', ') AS LegalEntityCodes FROM [QTrax].[dbo].[ClientsLegalEntityLink] WHERE [Active] = 1 GROUP BY [CID] ), ThreeSixtyClients AS ( SELECT A.[CID] AS "360ClientId", A.[CName] AS "360ClientName", A.[CAddress] AS "360ClientAddress", A.[CCity] AS "360ClientCity", A.[CState] AS "360ClientState", A.[CZip] AS "360ClientPostalCode", A.[CountryCode] AS "360CountryCode", CASE WHEN A.[CRef3Phone] IS NULL THEN 1 ELSE 0 END AS "360Active", Z.[LegalEntityCodes] AS "360LegalEntityCodes", B.[Id] AS "JoinKey", B.[EffectiveEndTime] FROM [QTrax].[dbo].[Clients] A LEFT JOIN FlattenedLegalEntityCodes Z ON A.[CID] = Z.[CID] LEFT JOIN [Integrations].[acostamdm].[MasterDataMapping] B ON A.[CID] = B.[EntityId] AND B.[EntityType] = 'Client' AND B.[MappedType] = 'Acosta Client' AND B.[IsActive] = 1 ), MasterDataClients AS ( SELECT C.[Id] AS "MDMClientId", C.[Name] AS "MDMClientName", C.[Address] AS "MDMClientAddress", C.[City] AS "MDMClientCity", C.[State] AS "MDMClientState", C.[PostalCode] AS "MDMClientPostalCode", C.[CountryId] AS "MDMCountryCode", C.[Active] AS "MDMActive", D.[Id] AS "JoinKey", D.[EffectiveEndTime] FROM [Integrations].[acostamdm].[Client] C LEFT JOIN [Integrations].[acostamdm].[MasterDataMapping] D ON C.[Id] = D.[MappedId] AND D.[EntityType] = 'Client' AND D.[MappedType] = 'Acosta Client' AND D.[IsActive] = 1 ) SELECT E.[360ClientId], E.[360ClientName], E.[360ClientAddress], E.[360ClientCity], E.[360ClientState], E.[360ClientPostalCode], E.[360CountryCode], E.[360Active], E.[360LegalEntityCodes], F.[MDMClientId], F.[MDMClientName], F.[MDMClientAddress], F.[MDMClientCity], F.[MDMClientState], F.[MDMClientPostalCode], F.[MDMCountryCode], F.[MDMActive], COALESCE(E.[EffectiveEndTime],F.[EffectiveEndTime]) AS EffectiveEndTime FROM ThreeSixtyClients E FULL JOIN MasterDataClients F ON E.[JoinKey] = F.[JoinKey] ORDER BY COALESCE(E.[360ClientName], F.[MDMClientName])
Clients mapped extract
Modified on: Wed, Apr 23, 2025 2:15 PM
Yes No
Sorry we couldn't be helpful. Help us improve this article with your feedback.