SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @WG1 INT, @filekey INT;
DECLARE @Results TABLE (
	Filekey INT,
	E404R INT,
	E601To404 INT,
	E404R0 INT
);

DECLARE employees_cursor CURSOR FOR
    SELECT Filekey
FROM EMPLOYEES
WHERE WG1 IN (10, 62)
	AND ACTIVESTATUS = 0
ORDER BY FILEKEY;

OPEN employees_cursor;

FETCH NEXT FROM employees_cursor INTO @filekey;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @WG1 = (SELECT WG1
	FROM EMPLOYEES
	WHERE FILEKEY = @filekey);

	DECLARE @INSTALLATION DATETIME;
	IF @WG1 = 10 
        BEGIN
		SELECT @INSTALLATION = '6/6/2005';
	END;
	IF @WG1 = 62 
        BEGIN
		SELECT @INSTALLATION = '3/19/2013';
	END;

	-- Installation date is needed because incidents before this date won't be calculated   
	-- so an employee state edit will not do anything to the incident   

	DELETE SUPEDITS   
    FROM SUPEDITS S   
    WHERE S.FILEKEY = @filekey
		AND S.EFFDATE = ISNULL((SELECT effdate
		FROM SUPEDITS
		WHERE filekey = @filekey
			AND EDITTYPE = 601
			AND EFFDATE = s.EFFDATE), '1/1/1900')
		AND S.EDITTYPE = 404
		-- AND I.EDITTYPE = 601   
		AND S.EFFDATE >= @INSTALLATION;

	-- Removes existing Employee State edits on the days that have change incidents being inserted   
	INSERT INTO @Results
	SELECT @filekey, @@ROWCOUNT, NULL, NULL;
	-- insert how many edits were deleted.

	UPDATE SUPEDITS 
    SET EDITTYPE = 404, -- Edittype 404 is the edittype for Employee State edits   
        PAYDESNUM = 0,   
        PREVPAYDES = 0,   
        REASON = CASE   
            -- Paydesnum is the rule that the incident is changed to. The reason will affect the calculation of the incident   
            WHEN PAYDESNUM = 2 THEN 20 -- Absent w/o Notify   
            WHEN PAYDESNUM = 3 THEN 23 -- Consecutive Absences   
            WHEN PAYDESNUM = 5 THEN 20 -- Tardy w/o Notify   
            WHEN PAYDESNUM = 7 THEN 20 -- Left Early w/o Notify   
            WHEN PAYDESNUM = 9 THEN 22 -- Remove Incident   
            WHEN PAYDESNUM = 10 THEN 21 -- No Incident   
            WHEN PAYDESNUM = 41 THEN 154 -- Abs w Notify  
            WHEN PAYDESNUM = 42 THEN 155 -- Abs wo Notify  
            WHEN PAYDESNUM = 43 THEN 156 -- Consec Abs  
            WHEN PAYDESNUM = 44 THEN 157 -- Tardy w Notify  
            WHEN PAYDESNUM = 45 THEN 158 -- Tardy wo Notify  
            WHEN PAYDESNUM = 46 THEN 159 -- Left Early w Notify  
            WHEN PAYDESNUM = 47 THEN 160 -- Left Early wo Notify  
            WHEN PAYDESNUM = 58 THEN 165 -- Traffic Exclusion
            ELSE 0   
        END   
    WHERE EDITTYPE = 601 -- Edittype 601 is the change incident edit.   
		AND FILEKEY = @filekey
		AND EFFDATE >= @INSTALLATION
		AND PAYDESNUM <> PREVPAYDES;

	UPDATE @Results 
    SET E601To404 = @@ROWCOUNT 
    WHERE Filekey = @filekey;
	-- Insert how many edits were changed from 601 to 404

	DELETE FROM SUPEDITS 
    WHERE EDITTYPE = 404
		AND REASON = 0;

	UPDATE @Results 
    SET E404R0 = @@ROWCOUNT 
    WHERE Filekey = @filekey;
	-- Insert how many 0 reason edits were removed.

	FETCH NEXT FROM employees_cursor INTO @filekey;
END;

CLOSE employees_cursor;

DEALLOCATE employees_cursor;

SELECT
	e.LASTNAME + ', ' + e.FIRSTNAME AS Emp,
	e.WG1,
	r.E404R,
	r.E601To404,
	r.E404R0
FROM @Results r
	INNER JOIN EMPLOYEES e
	ON r.Filekey = e.FILEKEY
ORDER BY 2, 1;