Jes Sie

Jes Sie

  • 691
  • 1.2k
  • 262.5k

Random Duplicate Results From SSRS

Jan 17 2018 9:41 PM
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:
  1. ALTER PROCEDURE [dbo].[spGet_eci_all_sales]  
  2.     -- Add the parameters for the stored procedure here  
  3.     @DateStart nvarchar(50),  
  4.     @DateEnd nvarchar(50)  
  5. AS  
  6. BEGIN  
  7.     -- SET NOCOUNT ON added to prevent extra result sets from  
  8.     -- interfering with SELECT statements.  
  9.     SET NOCOUNT ON;  
  10.   
  11.     -- Insert statements for procedure here  
  12.     SELECT ROW_NUMBER() OVER(ORDER BY E.IssuanceType) AS Ref,   
  13.     ROW_NUMBER() OVER(PARTITION BY IssuanceType ORDER BY IssuanceType) Nos,  
  14.         e.BranchCode,   
  15.         e.AgentID,   
  16.         a.AgentName,  --added December 21, 2017  
  17.         e.DateIssued,  
  18.         e.ApplicationTime,    
  19.         e.IssuanceType,   
  20.         e.MotorCINo,   
  21.         e.CustomerNo,   
  22.         c.CustomerName,  
  23.         c.Village,  
  24.         c.District,   
  25.         c.Province,  
  26.         c.CustTelephone,  
  27.         c.MobilePhone,  
  28.         c.CustEmail,  
  29.         c.TaxPayersID,   
  30.         e.VehicleType,   
  31.         e.VehicleBrand,   
  32.         e.VehicleModel,   
  33.         e.VehiclePlateNo,   
  34.         e.VehicleEngineNo,   
  35.         e.VehicleChasisNo,   
  36.         e.VehicleGrossTon,   
  37.         e.VehicleSettingCap,  
  38.         e.VehicleYearManufactured,  
  39.         e.VehicleBeganUsing,  
  40.         e.VehicleYearRegistered,  
  41.         e.VehicleUsageType,  
  42.         e.TaxStatus,  
  43.         e.InsuranceType,   
  44.         e.InsuranceOption,  
  45.         e.CompulsoryPrem,  
  46.         e.CompulsoryNCD,  
  47.         e.CompulsoryNetPrem,  
  48.         e.CoverAmount,  
  49.         e.DeductiblePercent,   
  50.         e.Deductible,   
  51.         e.OwnPrem,  
  52.         e.OwnNCD,  
  53.         e.OwnNetPrem,  
  54.         e.PeriodFrom,   
  55.         e.PeriodTo,   
  56.         e.DefenseRecourse,  
  57.         e.PersonalAccident,  
  58.         e.NetPremium,   
  59.         e.RegistrationFee,   
  60.         e.VAT,   
  61.         e.TotalPremium,  
  62.         ac.CommissionRate,   
  63.         e.AgentGrossCommission,   
  64.         e.AgentTax,   
  65.         e.AgentNetCommission,  
  66.         e.Username  
  67.     FROM     dbo.tblCustomers c INNER JOIN  
  68.              dbo.tblMotorInsurance_eCI e ON c.CustomerNo = e.CustomerNo INNER JOIN  
  69.                          dbo.tblAgentCommission ac ON e.AgentID = ac.AgentID   
  70.                          INNER JOIN dbo.Agents a ON e.AgentID = a.AgentID --added December 21, 2017  
  71.   
  72.     WHERE  (e.DateIssued BETWEEN @DateStart AND @DateEnd) and e.Remarks = 'SOLD' and e.BranchCode='01'   
  73.     ORDER BY e.IssuanceType ASC  
  74. END  
and below is the duplicate result:
 
Any suggestions on what went wrong? Thanks so much. 

Answers (1)