SHARE
TWEET

MergeVehicleByDmSCustomerId_Make_Model_Year

aboya Nov 20th, 2019 183 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. declare @VendorId int =2435
  4.  
  5. declare @NeedMergeCU table(dms_customerId varchar(50),  CountOf int)
  6. declare @VEH table(customerId int, vehicleId int, ManYear int, Make varchar(50),Model varchar(50), vin varchar(17))
  7. declare @cuMerge table(customerId int,vehicleId int)
  8. insert into @NeedMergeCU
  9. select c.dms_customerId, count(*) AS CountOf
  10. from Customer as c
  11. where c.VendorId= @VendorId and ltrim(rtrim(ISNULL(c.dms_customerId, ''))) !=''
  12. and  (select count(*) from vehicle as v where v.CustomerId = c.CustomerId)>0
  13. GROUP BY c.dms_customerId
  14. HAVING COUNT(*) > 1
  15.  
  16. declare @TMPdms_customerId  varchar(50);
  17. declare @MainCustomerId int =0;
  18. declare @MainVehiclId int = 0;
  19.  
  20. DECLARE CUCursor CURSOR FOR
  21. select dms_customerId from @NeedMergeCU
  22. OPEN CUCursor;
  23.     FETCH NEXT FROM CUCursor
  24.         INTO @TMPdms_customerId;
  25.         WHILE @@FETCH_STATUS = 0
  26.         BEGIN
  27.         delete from  @VEH;
  28.        
  29.  
  30.         insert into @VEH
  31.         Select c.CustomerId,v.vehicleId, ltrim(rtrim(ISNULL(v.ManYear, 0))) as ManYear,
  32.         ltrim(rtrim(ISNULL(v.Make, ''))) as Make,
  33.          REPLACE(ltrim(rtrim(ISNULL(Model, ''))), ' ', '') as Model,
  34.          ltrim(rtrim(ISNULL(v.VinNum, ''))) as vin from Customer as c
  35.         join Vehicle as v on c.CustomerId = v.CustomerId
  36.          where c.DMS_CustomerId =  @TMPdms_customerId and c.VendorId= @VendorId
  37.  
  38.          delete @VEH where Len(Make)=0 or Len(Model)=0  or ManYear=0
  39.          --DECLARE @TMP_customerId int=0
  40.          --DECLARE  @TMP_vehicleId int=0
  41.          DECLARE  @TMP_ManYear int =0
  42.          DECLARE  @TMP_Make varchar(50)
  43.          DECLARE @TMP_Model varchar(50)
  44.          DECLARE  @TMP_vin varchar(17)
  45.  
  46.             DECLARE VinCursor CURSOR FOR
  47.             select CustomerId,vehicleId,ManYear,Make,Model,vin from @VEH where Len(vin)>0
  48.              OPEN VinCursor;
  49.              FETCH NEXT FROM VinCursor
  50.             INTO @MainCustomerId,@MainVehiclId,@TMP_ManYear,@TMP_Make,@TMP_Model,@TMP_vin;
  51.        
  52.           WHILE @@FETCH_STATUS = 0
  53.           BEGIN
  54.           delete  from @cuMerge;
  55.           insert into @cuMerge
  56.           select customerId,VehicleId from @VEH where
  57.            VehicleId!= @MainVehiclId and Make = @TMP_Make and ManYear =@TMP_ManYear and Model= @TMP_Model and Len(vin)=0;
  58.  
  59.            if(@MainCustomerId>0 and @MainVehiclId > 0 and (select count(*) from @cuMerge)>0)begin
  60.                     select @MainCustomerId as MaincustomerId,
  61.                     substring ((select ',' + cast(customerId as varchar(20)) from @cuMerge where  customerId != @MainCustomerId for xml path('') ),2,8000 ) as oldCustomers,
  62.                      @MainVehiclId as MainVehId,
  63.                      substring ((select ',' + cast(vehicleId as varchar(20)) from @cuMerge where vehicleId!=@MainVehiclId  for xml path('') ),2,8000 ) as oldVehIds
  64.            
  65.            UPDATE dbo.Appointment set VehicleId = @MainVehiclId, customerId =@MainCustomerId where VehicleId in (select vehicleId from @cuMerge )
  66.                     UPDATE dbo.RO set VehicleId = @MainVehiclId, customerId =@MainCustomerId where VehicleId in (select vehicleId from @cuMerge )
  67.                     UPDATE VehicleInspection set vehicleId = @MainVehiclId where Vehicleid in (select vehicleId from @cuMerge )
  68.                     UPDATE Campaign SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge)
  69.                     UPDATE RFID SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge )
  70.                     UPDATE VehicleAlert SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge)
  71.                     UPDATE VehicleBackupLog SET VehicleId = @MainVehiclId WHERE VehicleId in (select vehicleId from @cuMerge)
  72.  
  73.                     delete from dbo.vehicle where vehicleid in (select vehicleId from @cuMerge )
  74.            end
  75.           FETCH NEXT FROM VinCursor
  76.           INTO @MainCustomerId,@MainVehiclId,@TMP_ManYear,@TMP_Make,@TMP_Model,@TMP_vin;
  77.           END
  78.        
  79.           CLOSE VinCursor;
  80.           DEALLOCATE VinCursor;
  81.  
  82.         FETCH NEXT FROM CUCursor
  83.         INTO  @TMPdms_customerId;
  84.         END
  85.        
  86.         CLOSE CUCursor;
  87.         DEALLOCATE CUCursor;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top