Skip to content

Instantly share code, notes, and snippets.

@drmohundro
Created January 4, 2017 01:59
Show Gist options
  • Save drmohundro/39257907a5d41656c9d2bd810066296a to your computer and use it in GitHub Desktop.
Save drmohundro/39257907a5d41656c9d2bd810066296a to your computer and use it in GitHub Desktop.
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