I am trying to get all accounts with duplicate end dates. I need info from four tables that only have the FK LDC_ACCT_ID in common. When i join the tables i get tons of dupes. I understand why but I cant get around it. Help!! Here is the query:
In SQL Server 2014
SELECT l.ALTERNATE_ID
,l.LDC_ACCT_NO
,ia.LDC_ACCT_ID
,count(ia.END_DT) AS SRVC_END
,mu.QTY_QUAL_CD
FROM LDC_ACCOUNT l
JOIN INVOICE_ADVISE ia ON l.LDC_ACCT_ID = ia.LDC_ACCT_ID
JOIN MONTHLY_USAGE mu ON l.LDC_ACCT_ID = mu.LDC_ACCT_ID
JOIN LDC_STATUS_HISTORY sh ON l.LDC_ACCT_ID = sh.LDC_ACCT_ID
WHERE sh.STATUS_CD NOT IN (
'FIN'
,'TERM'
,'PEFI'
)
AND mu.QTY_QUAL_CD = 'QD'
GROUP BY ia.LDC_ACCT_ID
,l.LDC_ACCT_NO
,l.ALTERNATE_ID
,ia.END_DT
HAVING (count(ia.end_dt) > 1)
ORDER BY ia.LDC_ACCT_ID DESC
Aucun commentaire:
Enregistrer un commentaire