April 17, 2012 at 12:17 pm
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
April 27, 2012 at 5:26 am
I don't think there are duplicates based on the rows in the picture included.
gsc_dba
April 27, 2012 at 5:28 am
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
April 27, 2012 at 6:00 am
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