--------------------- Data Conversion  MwusDevicePatchStatus_02    NOT IN TRANSACTION !!!          -------------------
if  ( 0 = dbo.udf_Conv_DoesIndexExist  ('MwusDevicePatchStatus','MwusDevicePatchStatus_EventID_LastChangeTime' )) -- created in the next script
and (
		   dbo.udf_DBM_VersionNumber() = ''                                        /* not real-life case but a special testing */
		or dbo.udf_Conv_CompareVersion (dbo.udf_DBM_VersionNumber(),10,2,2,0) < 0   -- if @FromVersion < 10.2.2   10-SP2-MR2
   )
begin 

    DECLARE @debug bit
	set @debug  = 0

    DECLARE @LastN int
													set @LastN = 5 -- last N report(s)

    declare @DeviceGuid uniqueidentifier


	 --declare @tran int = @@trancount


	declare @Script nvarchar(1000) 
	set @Script = '7470_102-MR2_MwusDevicePatchStatus_20-Data_Express.sql'
    IF @debug = 1 PRINT @Script

	declare @Step   nvarchar(1000) 
	set @Script = 'Start'

	begin try

	--if @tran = 0  
	--	   BEGIN TRANSACTION   
          
		declare cr cursor local fast_forward
		for
			select d.DeviceGuid
			  from dbo.PatchManagedDevice  pmd
              JOIN dbo.Device              d    ON pmd.DeviceID = d.DeviceID
             where not exists  -- this will make the data conversion re-runnable: if it fails, the next time it runs it will ignore already migrated data
			 (
				select *
				  from dbo.MwusDevicePatchStatus s
				 where s.DeviceGuid = d.DeviceGuid
			 )


		SET @Step = ' SET IDENTITY_INSERT dbo.MwusDevicePatchStatus ON ' 
		IF @debug = 1 PRINT @Step
		----------------------
        SET IDENTITY_INSERT dbo.MwusDevicePatchStatus ON 

	    open cr
		while 1 = 1 begin

			fetch next from cr into
									@DeviceGuid 
								 -- , @UpdateGuid 
			
			if @@Fetch_Status  <> 0 break
			;

			SET @Step = ' INSERT  dbo.MwusDevicePatchStatus @DeviceGuid=' + cast(@DeviceGuid as varchar(50))
			IF @debug = 1 PRINT @Step
			-------------------- 
            ;        
			with base as
			(
				SELECT   UpdateGuid  
						,RevisionNumber   
						,EventID           
						,LastChangeTime  
						,RowNbr        = Row_Number() over (partition by UpdateGuid order by LastChangeTime desc) -- to take LastN per Device/Patch
						,RowNbrReverce = Row_Number() over (partition by UpdateGuid order by LastChangeTime asc)  -- will be used to generate RowID in rhw same order as LastChangeTime
				  FROM
						dbo.MwusDevicePatchStatus_102MR1
                 where
				        DeviceGuid = @DeviceGuid
			)
	        INSERT  dbo.MwusDevicePatchStatus
			(
				 DeviceGuid        
				,UpdateGuid      
				,RevisionNumber   
				,EventID           
				,LastChangeTime
				-------------   
				 ,RowID               
			)
			SELECT 
					 @DeviceGuid        
					,UpdateGuid      
					,RevisionNumber   
					,EventID           
					,LastChangeTime   
				-------------   
				  , RowNbrReverce      -- RowID      -- this will generate duplicate RowID values, it's OK        
			  FROM
					base
			  where 
			        RowNbr < @LastN + 1 



		end
		close cr
		deallocate cr

               
		SET @Step = ' SET IDENTITY_INSERT dbo.MwusDevicePatchStatus OFF ' 
		IF @debug = 1 PRINT @Step
		----------------------
        SET IDENTITY_INSERT dbo.MwusDevicePatchStatus OFF 


		-- for 102-MR1 we are keeping the old data, prebably will truncate it on upgrade to the next version
		--if @debug = 0
		--	TRUNCATE TABLE dbo.MwusDevicePatchStatus_102MR1


	--if @tran = 0  
	--	   COMMIT   
		---------------------------------
end try
---------------------
begin catch

    declare @ErrorMessage       nvarchar(max)
    set @ErrorMessage = [dbo].[udf_ErrorInfo_Script](@Script,@Step)

 --   if (XACT_STATE() != 0) 
	--and @tran = 0  
	--	rollback transaction
        
	SET IDENTITY_INSERT dbo.MwusDevicePatchStatus OFF 

    RaisError (@ErrorMessage,16,255)

end catch
end -- 'MwusDevicePatchStatus-02.sql'

