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])