SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE NumberOfConjuntivePI (
	@par1 VARCHAR(50),
	@par2 VARCHAR(50),
	@par3 VARCHAR(50),
	@par4 VARCHAR(50)
)
AS
BEGIN	
	DECLARE @res INT;
	SET NOCOUNT ON;
    IF ((SELECT count(name) FROM sys.tables WHERE name='aux')=1)
		DROP TABLE aux;
	CREATE TABLE aux (task varchar(50) primary key);
	INSERT INTO aux (task) (
		SELECT 
		distinct a1.value
		FROM events as e1, events as e2, correlationAttributes as a1, correlationAttributes as a2
		WHERE a1.attribute = @par1 and a2.attribute = @par2 and a1.value=a2.value 
		AND a1.value<>'' AND a1.value IS NOT NULL
		and a1.idEvent<>a2.idEvent and a1.idEvent=e1.id and a2.idEvent=e2.id
		AND e1.timestamp<e2.timestamp
	);
	INSERT INTO aux (task) (
		SELECT 
		distinct a1.value 
		FROM events as e1, events as e2, correlationAttributes as a1, correlationAttributes as a2
		WHERE a1.attribute = @par3 and a2.attribute = @par4 and a1.value=a2.value 
		AND a1.value<>'' AND a1.value IS NOT NULL
		and a1.idEvent<>a2.idEvent and a1.idEvent=e1.id and a2.idEvent=e2.id
		AND e1.timestamp<e2.timestamp
	);
	SELECT @res= count(*) FROM aux;
	IF ((SELECT count(name) FROM sys.tables WHERE name='aux')=1)
		DROP TABLE aux;
	--PRINT @res;
	IF ((SELECT count(name) FROM sys.tables WHERE name='ConjuntivePI')=1)
		DROP TABLE ConjuntivePI;
	CREATE TABLE ConjuntivePI(name varchar(50) primary key, value real);
	INSERT INTO ConjuntivePI VALUES ('ConjuntivePI', @res);
END;
GO