Peak Employee Report

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @EMPLOYEES TABLE (
  FK INT,
  ACTIVEWORKGROUP INT)
  
DECLARE @EELIST TABLE(EENAME VARCHAR(250),IDNUM VARCHAR(20),WGROUP VARCHAR(300))

DECLARE @WGINFO TABLE(WGNUM INT,WGNAME VARCHAR(300),EECOUNT INT)  


DECLARE
  @ITERATION SMALLINT,
  @COUNTER SMALLINT,
  @LENGTH SMALLINT,
  @CURRENTCHARACTER VARCHAR(1),
  @CURRENTVALUE VARCHAR(16),
  @INDEX SMALLINT,
  @STARTVALUE SMALLINT,
  @ENDVALUE SMALLINT,
  @START DATETIME,
  @END DATETIME,
  @ITEMTYPE SMALLINT,
  @STARTDATE DATETIME,
  @ENDDATE DATETIME,
  @EECOUNT INT,
  @HIGHDATE INT
/*
SET @STARTDATE = '4/1/2023'
set @enddate = '4/30/2023'

*/
SET @STARTDATE ={mindate}
set @enddate = {maxdate}

SET @ITERATION=1


WHILE (@ITERATION<=1)
BEGIN
IF @ITERATION=1
--*****************************Sets @Start and @END to the top (newest) EventDate inside of the search range. for example search range starts 4/1 and today is 4/13 is will come back with 4/13
BEGIN
	SET @ITEMTYPE=0
		SELECT TOP 1 
			@START = EVENTDATE
         FROM DAILYSTATE 
         WHERE (EVENTDATE >= @STARTDATE AND EVENTDATE <= @ENDDATE) AND ITEMTYPE = @ITEMTYPE
         GROUP BY EVENTDATE, INSTANCES
         ORDER BY INSTANCES DESC, EVENTDATE DESC
     SET @END = @START
END
--**********************************************************************************************************************************************************************************************

  WHILE (@START <= @END)
      BEGIN
        SET @COUNTER = 0
        SELECT 
          @LENGTH = DATALENGTH(REFDATA) --returns the number of bytes used to represent an expression (Appear to be the number of characters. RefDATA appears to be a list of filekeys for the day.)
          FROM DAILYSTATE 
          WHERE EVENTDATE = @START AND ITEMTYPE = @ITEMTYPE
          SET @CURRENTVALUE = ''

        WHILE (@COUNTER <= @LENGTH)
          BEGIN
            SELECT @CURRENTCHARACTER = SUBSTRING(REFDATA, @COUNTER, 1) 
              FROM DAILYSTATE 
              WHERE EVENTDATE = @START AND ITEMTYPE = @ITEMTYPE
    
            IF @CURRENTCHARACTER != ',' SET @CURRENTVALUE = @CURRENTVALUE + @CURRENTCHARACTER

IF @CURRENTCHARACTER = ',' OR @COUNTER = @LENGTH
              BEGIN
                SET @INDEX = CHARINDEX(':', @CURRENTVALUE)
               
                IF @INDEX = 0 OR @INDEX IS NULL 
                  BEGIN
                    IF @ITEMTYPE = 0
                      INSERT INTO @EMPLOYEES VALUES (CONVERT(SMALLINT,@CURRENTVALUE), 0)
                    
               
                  END
        
 IF @INDEX > 0
                  BEGIN
                    SET @STARTVALUE = CONVERT(SMALLINT,LEFT(@CURRENTVALUE,@INDEX-1))
                    SET @ENDVALUE = CONVERT(SMALLINT,RIGHT(@CURRENTVALUE,LEN(@CURRENTVALUE)-@INDEX))
                                       
                    WHILE (@STARTVALUE <= @ENDVALUE)
                      BEGIN
                        IF @ITEMTYPE = 0
                          INSERT INTO @EMPLOYEES VALUES (@STARTVALUE, 0)
                       
             
                        SET @STARTVALUE = @STARTVALUE + 1
                      END
                  END
                
                IF @CURRENTCHARACTER = ',' SET @CURRENTVALUE = ''
              END
            SET @COUNTER = @COUNTER + 1
          END
        SET @START = DATEADD(DAY,1,@START)
      END      
    SET @ITERATION = @ITERATION + 1
  END
SET @START = DATEADD(DAY,-1,@START)
  
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP=(SELECT WG2 FROM EMPLOYEES WHERE FILEKEY=FK AND WGEFFDATE<=@START)
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP=(SELECT TOP 1 WG2 FROM EMPHOMEWGHIST WHERE FILEKEY=FK AND EFFDATE<=@START ORDER BY EFFDATE DESC)WHERE ACTIVEWORKGROUP IS NULL
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP= (SELECT WG2 FROM EMPLOYEES WHERE FILEKEY=FK)WHERE ACTIVEWORKGROUP IS NULL


INSERT INTO @EELIST SELECT 'Active Employee List ',DATENAME(MONTH,@START)+' '+DATENAME(YEAR,@START),NULL
INSERT INTO @EELIST SELECT NULL,NULL,NULL
INSERT INTO @EELIST SELECT 'High Water Mark Date',CONVERT(VARCHAR(10),@START,101),NULL
INSERT INTO @EELIST SELECT NULL,NULL,NULL
INSERT INTO @EELIST SELECT DISTINCT E.LASTNAME+', '+E.FIRSTNAME,E.IDNUM,WG2.NAME FROM EMPLOYEES E,
WORKGROUP2 WG2,@EMPLOYEES A WHERE A.FK=E.FILEKEY AND A.ACTIVEWORKGROUP=WG2.WGNUM ORDER BY 3,1

INSERT INTO @WGINFO SELECT NULL,'Active Employee Count Per Division '+DATENAME(MONTH,@START)+' '+DATENAME(YEAR,@START)
,NULL
INSERT INTO @WGINFO SELECT NULL,NULL,NULL
INSERT INTO @WGINFO SELECT NULL,'High Water Mark Date'+' '+CONVERT(VARCHAR(10),@START,101),NULL
INSERT INTO @WGINFO SELECT NULL,NULL,NULL
INSERT INTO @WGINFO SELECT DISTINCT WGNUM,NAME,NULL FROM WORKGROUP2 ORDER BY 2

UPDATE @WGINFO SET EECOUNT=(SELECT COUNT(*) FROM @EMPLOYEES WHERE ACTIVEWORKGROUP=WGNUM)WHERE WGNUM
IS NOT NULL

INSERT INTO @WGINFO SELECT NULL,NULL,NULL
INSERT INTO @WGINFO SELECT NULL, 'TOTAL',(SELECT SUM(EECOUNT) FROM @WGINFO)
INSERT INTO @EELIST SELECT NULL,NULL,NULL
INSERT INTO @EELIST SELECT NULL, 'TOTAL',(SELECT COUNT(FK) FROM @EMPLOYEES)

SELECT * FROM @EELIST

SELECT * FROM @WGINFO

--Select * from @EMPLOYEES