Random Duplicate Results From SSRS

Hello everyone, I have a stored procedure that joins 4 tables. For more than a semester, the report is running fine until recently I got a random duplicate data. Below is my sp:

ALTER PROCEDURE [dbo].[spGet_eci_all_sales]
-- Add the parameters for the stored procedure here
@DateStart nvarchar(50),
@DateEnd nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT ROW_NUMBER() OVER(ORDER BY E.IssuanceType) AS Ref,
ROW_NUMBER() OVER(PARTITION BY IssuanceType ORDER BY IssuanceType) Nos,
e.BranchCode,
e.AgentID,
a.AgentName, --added December 21, 2017
e.DateIssued,
e.ApplicationTime,
e.IssuanceType,
e.MotorCINo,
e.CustomerNo,
c.CustomerName,
c.Village,
c.District,
c.Province,
c.CustTelephone,
c.MobilePhone,
c.CustEmail,
c.TaxPayersID,
e.VehicleType,
e.VehicleBrand,
e.VehicleModel,
e.VehiclePlateNo,
e.VehicleEngineNo,
e.VehicleChasisNo,
e.VehicleGrossTon,
e.VehicleSettingCap,
e.VehicleYearManufactured,
e.VehicleBeganUsing,
e.VehicleYearRegistered,
e.VehicleUsageType,
e.TaxStatus,
e.InsuranceType,
e.InsuranceOption,
e.CompulsoryPrem,
e.CompulsoryNCD,
e.CompulsoryNetPrem,
e.CoverAmount,
e.DeductiblePercent,
e.Deductible,
e.OwnPrem,
e.OwnNCD,
e.OwnNetPrem,
e.PeriodFrom,
e.PeriodTo,
e.DefenseRecourse,
e.PersonalAccident,
e.NetPremium,
e.RegistrationFee,
e.VAT,
e.TotalPremium,
ac.CommissionRate,
e.AgentGrossCommission,
e.AgentTax,
e.AgentNetCommission,
e.Username
FROM dbo.tblCustomers c INNER JOIN
dbo.tblMotorInsurance_eCI e ON c.CustomerNo = e.CustomerNo INNER JOIN
dbo.tblAgentCommission ac ON e.AgentID = ac.AgentID
INNER JOIN dbo.Agents a ON e.AgentID = a.AgentID --added December 21, 2017
WHERE (e.DateIssued BETWEEN @DateStart AND @DateEnd) and e.Remarks = 'SOLD' and e.BranchCode='01'
ORDER BY e.IssuanceType ASC
END

and below is the duplicate result:

Any suggestions on what went wrong? Thanks so much.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
Hello,
as you have provided the procedure but not provided the table structure and sample data. In that case it is little bit difficult to find the exact output because, I can't execute the procedure.
Can you try after changing as per below in join condition
FROM dbo.tblCustomers c INNER JOIN
dbo.tblMotorInsurance_eCI e ON c.CustomerNo = e.CustomerNo
INNER JOIN dbo.tblAgentCommission ac ON e.AgentID = ac.AgentID
INNER JOIN dbo.Agents a ON e.AgentID = a.AgentID --added December 21, 2017
and e.AgentID = ac.AgentID and c.CustomerNo = e.CustomerNo