--------------------- THEre is not too much data in   MwusDevicePatchStatus , 10K or less, so all 510_ 3 scripts will run IN teh same TRANSACTION        -------------------
if object_id('MwusDevicePatchStatus_102MR3') is not null -- created by the previous script
begin 

    DECLARE @debug bit
	set @debug  = 1



	 declare @tran int 
	 set @tran = @@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   
          

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


			SET @Step = ' INSERT  dbo.MwusDevicePatchStatus from MwusDevicePatchStatus_102MR3' 
			IF @debug = 1 PRINT @Step
			-------------------- 
			/* from Hans:
				If RowID is not preserved, then Defect 257260 (Windows 10 Patch > Installed Patch being reported as Downloaded in SC Patch Report (and MWData)) will reappear.
				If you could impact RowIDs based on Events (only for the cases where TimeLastUpdate is the same for a given Device and Patch), then we could hide the problem.
				Basically, Installed (5) state should have a higher (bigger) RowID than any other
			*/
            ;        
			with eventStatusPriority as
			(
				select  5 as Status, 103 as EventID, 90 as Priority
				union all select  5, 161, 90 
				union all select  5, 182, 90 
				union all select  5, 195, 90 
				union all select  5, 198, 90 
				union all select  5, 203, 90 
				union all select  5, 221, 90 
				----------------
				union all select  3, 104, 60 
				union all select  3, 162, 60 
				----------------
				union all select  2, 100, 80 
				union all select  2, 163, 80 
				union all select  2, 186, 80 
				union all select  2, 187, 80 
				union all select  2, 192, 80 
				union all select  2, 200, 80 
				union all select  2, 222, 80 
				union all select  2, 224, 80 
				----------------
				union all select  4, 101, 100
				union all select  4, 183, 100
				union all select  4, 190, 100
				union all select  4, 197, 100
				union all select  4, 223, 100
				union all select  4, 225, 100
				----------------
				union all select  6, 102, 70 
				union all select  6, 184, 70 
				union all select  6, 191, 70 
				union all select  6, 199, 70 
				----------------
				union all select  1, 105, 50 
			)
			,base as
			(
				SELECT   DeviceGuid
				        ,UpdateGuid  
						,RevisionNumber   
						,s.EventID           
						,LastChangeTime  
						,RowNbrReverce = Row_Number() over (partition by DeviceGuid, UpdateGuid order by LastChangeTime asc,  isnull(p.Priority,0) asc ) -- will be used to generate RowID in the same order as LastChangeTime and Priority
																																				     ----- for the same  LastChangeTime: the bigger Priority, the bigger RowID
				  FROM
						dbo.MwusDevicePatchStatus_102MR3   s
				  left
				  join eventStatusPriority                 p  on s.EventID = p.EventID
			)
	        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


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


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

			    -- rename MwusDevicePatchStatus_102MR3 --> MwusDevicePatchStatus_102MR3_used
        SET @Step = ' exec [dbo].[DBM_Conv_rename_Table] MwusDevicePatchStatus_102MR3 to MwusDevicePatchStatus_102MR3_used'
        IF @debug = 1 PRINT @Step
        --------------------
        exec [dbo].[DBM_Conv_rename_Table]
	                                        @tablename          = 'MwusDevicePatchStatus_102MR3'
                                           ,@newname            = null
                                           ,@suffix             =  '_used'
                                           ,@RenamePK            = 1
                                           ,@DropDF              = 1
                                           ,@DropFK              = 1
                                           ,@DropPK              = 0
                                           ,@DropNCIndex         = 0
                                           ,@OnlyIfTable         = 0
                                           ,@NotIfNewNameExists  = 1
                                           ,@debug               = @debug



	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 -- 510_102-MR3-HF2_MwusDevicePatchStatus_20-Data_ExpressOnly

