--USE [pioner]

DECLARE @ver int
SET @ver = 4

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[components]') AND type in (N'U'))
BEGIN
	IF EXISTS( SELECT veri FROM [components] WHERE name='mdb.ph_history' AND veri>=@ver )
	BEGIN
		PRINT N'Current PH_HISTORY version is equal or higher than '+ CAST(@ver AS varchar(10))
		--RAISERROR('Script terminated...',16,1) 
		RETURN	
	END		
END
ELSE
BEGIN
	PRINT N'Components table not found. Script exit'
	RETURN
END

BEGIN TRY

BEGIN TRAN

PRINT N'Script begin'

--добавляем колонку

EXEC sp_executesql 
N'
ALTER TABLE [dbo].[PH_HISTORY] ADD [GroupID] [int] NULL

PRINT ''Column GroupID added to table [PH_HISTORY]''
'

-- вычисляем значение для этой колонки
EXEC sp_executesql 
N'
UPDATE ph_hist
SET ph_hist.GroupID = 
(	
	SELECT MIN(ph_hist_sub.ID)
	FROM PH_HISTORY as ph_hist_sub
	WHERE ph_hist_sub.PH_ID = ph_hist.PH_ID
		AND ph_hist_sub.UserName = ph_hist.UserName
		AND ph_hist_sub.HostName = ph_hist.HostName
		AND ABS(DATEDIFF(second, ph_hist_sub.ModifyDate, ph_hist.ModifyDate)) < 5 
)
FROM [dbo].[PH_HISTORY] AS ph_hist
WHERE ph_hist.GroupID IS NULL

PRINT ''GroupID calculated for existing items''
'


UPDATE [dbo].[components] 
SET veri = @ver 
WHERE name='mdb.ph_history'

COMMIT TRAN

PRINT N'Script end!'

END TRY
BEGIN CATCH
	ROLLBACK TRAN
	PRINT N'Script error'
END CATCH;