T-SQL Has More Than CTE Example

has-more-than-one-example.sql

; WITH ForImport AS (
	SELECT
		DISTINCT 
		ve.BvcmsId
		,vd.account AS CreditCardOrAch
		,vd.account_expiration AS Expires
		,vd.routing AS Routing
		,vd.address1 AS Address1
		,vd.address2 AS Address2
		,vd.city AS City
		,vd.state AS [State]
		,vd.zipcode AS Zip
		,vd.country AS Country
		,vd.first_name AS FirstName
		,vd.last_name AS LastName
		,vd.phone AS Phone
		,ve.VaultId
	FROM dbo.[_VaultExport] ve
	JOIN dbo.[_TnbVaultData] vd ON vd.customer_vault_id = ve.VaultId
	WHERE ve.BvcmsId IS NOT null
)
,HasMoreThan1 AS (
	SELECT
		count (*) AS count
		,bvcmsid
	FROM ForImport
	GROUP BY BvcmsId
	HAVING count(*) > 1
)
SELECT
	ve.*
	,vd.account
	,vd.account_expiration
	,vd.routing
FROM dbo.[_VaultExport] ve
LEFT JOIN dbo.[_TnbVaultData] vd ON vd.customer_vault_id = ve.VaultId
WHERE BvcmsId IN (SELECT BvcmsId FROM HasMoreThan1)
ORDER BY BvcmsId ASC