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]
-
- @DateStart nvarchar(50),
- @DateEnd nvarchar(50)
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
-
-
- 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,
- 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
-
- 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.