Peak Employee by Location with Peak User Accounts Report

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @EMPLOYEES TABLE (FK INT, ACTIVEWORKGROUP INT)
DECLARE @ESSINFO TABLE (ESSCOUNT INT, WG int)  
DECLARE @WGINFO TABLE(WGNUM INT,WGNAME VARCHAR(300),EECOUNT INT, ESSCOUNT INT) 
DECLARE @USERS TABLE(USERID INT, AccType 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 = '1/1/2023'
set @enddate = '1/31/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

--************************************************************************************************************************************************
--           Farse the filekey for the day out of REFDATA
--************************************************************************************************************************************************
  WHILE (@START <= @END)
      BEGIN
        SET @COUNTER = 0
        SELECT 
          @LENGTH = DATALENGTH(REFDATA) -- 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 WG1 FROM EMPLOYEES WHERE FILEKEY=FK AND WGEFFDATE<=@START)
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP=(SELECT TOP 1 WG1 FROM EMPHOMEWGHIST WHERE FILEKEY=FK AND EFFDATE<=@START ORDER BY EFFDATE DESC)WHERE ACTIVEWORKGROUP IS NULL
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP= (SELECT WG1 FROM EMPLOYEES WHERE FILEKEY=FK)WHERE ACTIVEWORKGROUP IS NULL

INSERT INTO @ESSINFO Select COUNT(DISTINCT EA.FILEKEY), e.ACTIVEWORKGROUP 
						FROM EMPLOYEEACTIVITY EA 
						Inner join @EMPLOYEES e on EA.filekey = e.FK
						WHERE EA.FILEKEY IN (SELECT fk FROM @EMPLOYEES) 
						group by e.ACTIVEWORKGROUP

INSERT INTO @WGINFO SELECT NULL,'Active Employee Count Per Location '+DATENAME(MONTH,@START)+' '+DATENAME(YEAR,@START),NULL,NULL

INSERT INTO @WGINFO SELECT NULL,NULL,NULL,NULL
INSERT INTO @WGINFO SELECT NULL,'Peak Employee Date'+' '+CONVERT(VARCHAR(10),@START,101),NULL,NULL
INSERT INTO @WGINFO SELECT NULL,NULL,NULL,NULL
INSERT INTO @WGINFO SELECT NULL,(Select 'Peak User Account: '+Convert(VARCHAR(10), MAX(Instances)) from DAILYSTATE where ITEMTYPE = 3 and EVENTDATE between @STARTDATE and @ENDDATE),null,null
INSERT INTO @WGINFO SELECT NULL,NULL,NULL,NULL
INSERT INTO @WGINFO SELECT DISTINCT WGNUM,NAME,NULL,NULL FROM WORKGROUP2 ORDER BY 2

UPDATE @WGINFO SET EECOUNT=(SELECT COUNT(*) FROM @EMPLOYEES WHERE ACTIVEWORKGROUP=WGNUM)WHERE WGNUM IS NOT NULL
UPDATE @WGINFO SET ESSCOUNT=(Select es.ESSCOUNT FROM @ESSINFO es WHERE es.WG=WGNUM)WHERE WGNUM IS NOT NULL 

INSERT INTO @WGINFO SELECT NULL,NULL,NULL,NULL
INSERT INTO @WGINFO SELECT NULL, 'TOTAL',(SELECT SUM(EECOUNT) FROM @WGINFO),(Select SUM(ESSCOUNT) from @WGINFO)

SELECT * FROM @WGINFO where (EECOUNT!=0 or EECOUNT IS NULL)