Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
T-SQL Has More Than CTE Example
; 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.