Duplicate records in Query Result 1

[MwTV]

The following sql script that is within the query pane of Sql Query Analyzer is resulting in duplicate records. Any insight as to the cause and solution? Also, how do I format the "CHGAMT" column as currency?

Thanks in advance.

Select distinct FU.CLAIM_ID
,DP.PROV_FULL_NM
,DP.SRC_SYS_PROVIDER_NBR AS PROVIDNUM
,DP.PROVIDER_TAX_ID AS PROVTAXID
,MBR_CURRENT_MCAID_NBR AS CURMCAIDNUM
,FSC.FINANCE_SVC_CATEGORY_CD AS PTTYPE
,PPA.PRODUCT_CD AS PRODUCTCODE
,PPA.ACCRUAL_PRODUCT_DESC AS PRODDESCRIP
,CONVERT(VARCHAR, DD.DATE_SRC_DT, 101) AS SVCDATE
,SUM(UTILIZATION_CHRG_AMT) AS CHGAMT
,SUM (CLA.CLM_LINE_ALLOW_AMT) AS ALLOWEDAMT
,SUM (UTILIZATION_PAID_AMT) AS PAIDAMT
FROM dbo.LOAD_FACT_UTILIZATION_CLAIM_DETAIL FU (NOLOCK)
INNER JOIN DIM_FINANCE_SVC_CATEGORY FSC (NOLOCK)
ON FU.FINANCE_SVC_CATEGORY_ID = FSC.FINANCE_SVC_CATEGORY_ID
INNER JOIN dbo.CLAIM_LINE_ADJUSTED CLA (NOLOCK) ON FU.CLAIM_ID = CLA.CLAIM_ID
INNER JOIN DIM_DATE DD (NOLOCK) ON FU.DATE_ID = DD.DATE_ID
INNER JOIN DIM_PLAN_PRODUCT_ACCRUAL PPA (NOLOCK)
ON FU.PLAN_PRODUCT_ACCRUAL_ID = PPA.PLAN_PRODUCT_ACCRUAL_ID
INNER JOIN DIM_PROVIDER DP (NOLOCK) ON FU.SVC_PROV_ID = DP.PROVIDER_ID
INNER JOIN MEMBER MBR (NOLOCK) ON FU.MEMBER_ID = MBR.MEMBER_ID
INNER JOIN DIM_BILL_ITEM DBI (NOLOCK) ON FU.BILL_ITEM_ID = DBI.BILL_ITEM_ID

WHERE DD.DATE_SRC_DT BETWEEN ‘01/01/07’ AND ‘06/15/07’
AND DP.PROVIDER_TAX_ID = ‘25689000’
AND PPA.RPT_MARKET_CD = (‘GFACC’)

GROUP BY FU.CLAIM_ID
,DP.PROV_FULL_NM
,DP.SRC_SYS_PROVIDER_NBR
,DP.PROVIDER_TAX_ID
,MBR_CURRENT_MCAID_NBR
,FSC.FINANCE_SVC_CATEGORY_CD
,PPA.PRODUCT_CD
,PPA.ACCRUAL_PRODUCT_DESC
,DD.DATE_SRC_DT
,CLA.CLM_LINE_ALLOW_AMT
,DBI.BILL_ITEM_CD
,DBI.BILL_ITEM_DESC

 

[mrdenny]

Since you already have the DISTINCT keyword at the begining of the query, the records have to be different in some little way. You will need to go through the data and find the slight differences.

Currency specific formatting (adding the $ for example) is done in the front end, not at the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com