SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRAN

EXEC SP_RENAME N'ASM_SOFTWARE_MEASURE', N'ASM_SOFTWARE_MEASURE_OLD';
GO

CREATE TABLE [dbo].[ASM_SOFTWARE_MEASURE] (
	fl_int_reg_id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	fl_int_csidpc INT,
	fl_int_pcid INT,
	fl_int_processid INT,
	fl_str_user NVARCHAR(50),
	fl_str_title NVARCHAR(255),
	fl_dat_fromdate DATETIME,
	fl_dat_todate DATETIME,
	fl_int_status INT,
	fl_flt_cpu FLOAT,
	fl_int_memory INT
)
GO

CREATE TABLE ASM_TEMP_MEASURE (
	fl_int_reg_id INT NOT NULL,
	fl_int_csidpc INT,
	fl_int_pcid INT,
	fl_str_user NVARCHAR(50),
	fl_dat_fromdate DATETIME,
	fl_dat_todate DATETIME,
	fl_flt_cpu FLOAT,
	fl_int_memory INT,
	CONSTRAINT PK_ASM_TEMP_MEASURE PRIMARY KEY (fl_int_reg_id)
)
GO

DELETE TOP(1) FROM ASM_SOFTWARE_MEASURE_OLD
	OUTPUT deleted.fl_int_csidpc,
		deleted.fl_int_pcid,
		deleted.fl_int_processid,
		deleted.fl_str_user,
		deleted.fl_str_title,
		deleted.fl_dat_fromdate,
		deleted.fl_dat_todate,
		deleted.fl_int_status,
		deleted.fl_flt_cpu,
		deleted.fl_int_memory
	INTO ASM_SOFTWARE_MEASURE;
GO 9

ALTER TABLE ASM_SOFTWARE_MEASURE ADD
	CONSTRAINT FK_ASM_SM_COMPLETESOFTWARE FOREIGN KEY (fl_int_csidpc) REFERENCES [dbo].[COMPLETESOFTWARE] ([csidpc]),
	CONSTRAINT FK_ASM_SM_SYSTEM FOREIGN KEY (fl_int_pcid) REFERENCES [dbo].[SYSTEM] ([pcid]);

GO

DROP TABLE ASM_SOFTWARE_MEASURE_OLD;

GO
CREATE INDEX IDX001_ASM_SOFTWARE_MEASURE ON ASM_SOFTWARE_MEASURE (
	fl_int_reg_id
)
INCLUDE (
	fl_int_csidpc,
	fl_int_pcid,
	fl_str_user,
	fl_dat_fromdate,
	fl_dat_todate,
	fl_flt_cpu,
	fl_int_memory
)
GO
UPDATE ASM_SINC_DATES SET fl_int_consolidate = 0;
GO
DECLARE @date DATETIME;

	SELECT @date = fl_dat_date FROM ASM_SINC_DATES WHERE fl_int_id = 4;
	
	UPDATE ASM_SINC_DATES SET
		fl_int_consolidate = (
				SELECT MIN(fl_int_reg_id) FROM ASM_SOFTWARE_MEASURE WHERE fl_dat_fromdate > @date
			),
		fl_int_delete =
			CASE fl_int_id WHEN 1 THEN fl_int_delete
				WHEN 2 THEN NULL
				WHEN 3 THEN fl_int_delete * 31
				WHEN 4 THEN fl_int_delete END;

COMMIT TRAN;
GO