-- Begin of MW-7740: TimeLastAssetScan conversion
-- ME table data conversion
CREATE VIEW [dbo].[vw_Device_TimeLastAssetScan]
AS
SELECT m.[DeviceGuid]
	,cast(d.[DateTimeValue] AS DATETIME) AS [TimeLastAssetScan]
	,m.[DateCreated]
FROM [dbo].[ManagedElementAttributeData] d WITH (NOLOCK)
JOIN [dbo].[ManagedElement] m ON d.[ManagedElementGuid] = m.[ManagedElementGuid]
WHERE m.[ManagedElementTypeGuid] = '0610A168-9632-48CF-B355-72FAE932E201'
	AND d.[ManagedElementAttributeTypeGuid] = 'A4487DCF-93BB-4233-9FBA-2D68A6621EC9'
GO

INSERT INTO [dbo].[Asset_Device] (
	[DeviceGuid]
	,[TimeLastAssetScan]
	,[DateCreated]
	,[DateModified]
	)
SELECT a.[DeviceGuid]
	,a.[TimeLastAssetScan]
	,a.[DateCreated]
	,[DateModified] = GETDATE()
FROM vw_Device_TimeLastAssetScan a
JOIN [dbo].Device d ON a.[DeviceGuid] = d.[DeviceGuid]

GO
DROP VIEW [dbo].[vw_Device_TimeLastAssetScan];
GO
-- Remove DeviceGuid from ManagedElementAttributeType as its ManagedElementGuidColumnName is DeviceGuid (IsSingleInstance = 1)
GO
DELETE
FROM [ManagedElementAttributeData]
WHERE ManagedElementAttributeTypeGuid = '9A5B8474-0E7E-4299-BC24-F0013226237F'
GO

DELETE dbo.[ManagedElementAttributeType]
WHERE [ManagedElementAttributeTypeGuid] = '9A5B8474-0E7E-4299-BC24-F0013226237F'
GO

-- End of MW-7740: TimeLastAssetScan conversion
GO
