Duplicate Records

  • Hi,

    I am in new to SSRS, Please find the below link, I don’t want to show the duplicate records in my report, right now it is showing duplicate records in the report.

    SELECT distinct

    Agreements.AgreementName,

    Counterparty.ParentEntityName,

    Counterparty.EntityName COUNTERPARTYNAME,

    DisputeAgings.CptyMarginCallAmtInSysCcy,

    DisputeAgings.CptyMarginCallAmtInAgmtCcy,

    CASE WHEN NetExposureFavorOf = 'Counterparty' THEN MarginCalls.NetExposure*(-1)

    WHEN NetExposureFavorOf = 'Principal' THEN MarginCalls.NetExposure

    ELSE 0 END NETEXPOSURE,

    DisputeAgings.CptyDisputeAmtInSysCcy,

    DisputeAgings.CptyDisputeAmtInAgmtCcy,

    DisputeAgings.CptyAge,

    MarginCalls.DisputeReasons,

    MarginCalls.DisputeComment,

    Principal.EntityName PRINCIPALNAME,

    Agreements.PrincipalManagingLocationName,

    MarginCalls.CptyNetExposureFavorOf,

    MarginCalls.CptyNetExposure,

    MarginCalls.CptyVariationAnticDemand,

    MarginCalls.CptyLockUpAnticDemand,

    CASE WHEN (MarginCalls.CollateralMarginType = 'Variation') THEN (DisputeAgings.CptyDisputeAmtInAgmtCcy) WHEN (MarginCalls.CollateralMarginType = 'Lockup') THEN (DisputeAgings.CptyDisputeAmtInAgmtCcy) ELSE 0 END TEST

    FROM

    Agreements,

    Entities Counterparty,

    DisputeAgings,

    MarginCalls,

    Entities Principal

    WHERE

    ( Agreements.PrincipalId=Principal.EntityId )

    AND ( Counterparty.EntityId=Agreements.CounterpartyId )

    AND ( MarginCalls.AgreementId=Agreements.AgreementId )

    AND ( Agreements.AgreementId=DisputeAgings.AgmtId )

    AND

    case when MarginCalls.IsCancelled = 1 then 'Y' else 'N' END IN ( 'N' )

    AND

    MarginCalls.CallType = 'Demand'

    AND

    MarginCalls.CalculationDate = trunc(sysdate)

    AND

    DisputeAgings.RecordDate = trunc(sysdate)

    AND

    DisputeAgings.CptyMarginCallAmtInAgmtCcy <> 0

    AND

    DisputeAgings.CptyMarginCallAmtInSysCcy <> 0

    order by Agreements.AgreementName

    http://www.flickr.com/photos/73609688@N08/6941926026/

  • I don't think there are duplicates based on the rows in the picture included.

    gsc_dba

  • gsc_dba (4/27/2012)


    I don't think there are duplicates based on the rows in the picture included.

    The dispute amount (or the join to it) makes the row unique, I suspect that is the place to start, check the join conditions.

    gsc_dba

  • i would suggest you start using the ANSI-92 SQL syntax it makes debugging duplicates much easier

    i rewrote for you....

    also - is it possible you are seeing duplicates in RS because you are not showing all the columns in the query ? it will look like duplicates in teh report, but the underlying data is correct - the use of "DISTINCT" guarantees that

    SELECT distinct

    Agreements.AgreementName,

    Counterparty.ParentEntityName,

    Counterparty.EntityName COUNTERPARTYNAME,

    DisputeAgings.CptyMarginCallAmtInSysCcy,

    DisputeAgings.CptyMarginCallAmtInAgmtCcy,

    CASE WHEN NetExposureFavorOf = 'Counterparty' THEN MarginCalls.NetExposure*(-1)

    WHEN NetExposureFavorOf = 'Principal' THEN MarginCalls.NetExposure

    ELSE 0 END NETEXPOSURE,

    DisputeAgings.CptyDisputeAmtInSysCcy,

    DisputeAgings.CptyDisputeAmtInAgmtCcy,

    DisputeAgings.CptyAge,

    MarginCalls.DisputeReasons,

    MarginCalls.DisputeComment,

    Principal.EntityName PRINCIPALNAME,

    Agreements.PrincipalManagingLocationName,

    MarginCalls.CptyNetExposureFavorOf,

    MarginCalls.CptyNetExposure,

    MarginCalls.CptyVariationAnticDemand,

    MarginCalls.CptyLockUpAnticDemand,

    CASE WHEN (MarginCalls.CollateralMarginType = 'Variation') THEN (DisputeAgings.CptyDisputeAmtInAgmtCcy) WHEN (MarginCalls.CollateralMarginType = 'Lockup') THEN (DisputeAgings.CptyDisputeAmtInAgmtCcy) ELSE 0 END TEST

    FROM

    Agreements

    Inner join DisputeAgings on Agreements.AgreementId=DisputeAgings.AgmtId

    Inner join MarginCalls on MarginCalls.AgreementId=Agreements.AgreementId

    Inner join Counterparty ON Counterparty.EntityId=Agreements.CounterpartyId

    inner join Principal on Agreements.PrincipalId=Principal.EntityId

    WHERE

    MarginCalls.IsCancelled<>1

    AND MarginCalls.CallType = 'Demand'

    AND MarginCalls.CalculationDate = trunc(sysdate)

    AND DisputeAgings.RecordDate = trunc(sysdate)

    AND DisputeAgings.CptyMarginCallAmtInAgmtCcy <> 0

    AND DisputeAgings.CptyMarginCallAmtInSysCcy <> 0

    order by Agreements.AgreementName

    MVDBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply