법인별 지정일 시간대별 사용자 접속 내역 (AX 2012)

/*
작 성 자 : 박병욱
작 성 일 : 2018-11-01
내    용 : 법인별 지정일 시간대별 사용자 접속 내역
사 용 법 : 하단 주석 처리 되어있는 임시 테이블을 생성 후 쿼리 실행
파라미터 : @UTCUNIT (UTC 시간 설정)
           @DATE (지정일)
*/

--입력파라미터
DECLARE @UTCUNIT INT;
DECLARE @DATE NVARCHAR(10);
--커서
DECLARE @COMPANY NVARCHAR(4);
DECLARE @USERID NVARCHAR(10);
DECLARE @LOGIN_DATE DATETIME;
DECLARE @LOGIN_TIMEUNIT INT;
DECLARE @LOGIN_TIME TIME;
DECLARE @LOGOUT_DATE DATETIME;
DECLARE @LOGOUT_TIMEUNIT INT;
DECLARE @LOGOUT_TIME TIME;
--커서루프
DECLARE @USERTIMELOOP INT;

--파라미터 설정
SET @UTCUNIT = 9; --KOREA STANDARD TIME
SET @DATE = '2018-10-01';

/*
--임시 테이블을 먼저 생성합니다.
CREATE TABLE #USERLOGBYCOMPANY
(
COMPANY NVARCHAR(4),
LOGDATE DATETIME,
LOGTIME INT,
USERID  NVARCHAR(10)
)
--SELECT * FROM #USERLOGBYCOMPANY
--DROP TABLE #USERLOGBYCOMPANY
*/
DELETE #USERLOGBYCOMPANY;
DECLARE USERCURSOR CURSOR FAST_FORWARD READ_ONLY
FOR
    SELECT
    USERTIMESHEETLIST.COMPANY,
        USERTIMESHEETLIST.USERID,
        --USERTIMESHEETLIST.TYPE,
        USERTIMESHEETLIST.LOGIN_DATE,
        DATEPART(HOUR,USERTIMESHEETLIST.LOGIN_TIME) AS LOGIN_TIMEUNIT,
        USERTIMESHEETLIST.LOGIN_TIME,
        USERTIMESHEETLIST.LOGOUT_DATE,
        DATEPART(HOUR,USERTIMESHEETLIST.LOGOUT_TIME) AS LOGOUT_TIMEUNIT,
        USERTIMESHEETLIST.LOGOUT_TIME
    FROM (SELECT
    USERCOMPANY.COMPANY,
            USERTIMESHEET.USERID,
            USERTIMESHEET.TYPE,
            MIN(USERTIMESHEET.LOGIN_DATE) AS LOGIN_DATE,
            MIN(DATEPART(HOUR,USERTIMESHEET.LOGIN_TIME)) AS LOGIN_TIMEUNIT,
            MIN(USERTIMESHEET.LOGIN_TIME) AS LOGIN_TIME,
            MAX(USERTIMESHEET.LOGOUT_DATE) AS LOGOUT_DATE,
            MAX(DATEPART(HOUR,USERTIMESHEET.LOGOUT_TIME)) AS LOGOUT_TIMEUNIT,
            MAX(USERTIMESHEET.LOGOUT_TIME) AS LOGOUT_TIME
            FROM (SELECT
                    USERID,
                    CASE
                      WHEN TYPE = 0 THEN 'LOGIN'
                      WHEN TYPE = 1 THEN 'CHANGE PASSWORD'
                    END AS TYPE,
                    CAST(DATEADD(HH, 9, CREATEDDATETIME) AS DATE) AS LOGIN_DATE,
                    CAST(DATEADD(HH, 9, CREATEDDATETIME) AS TIME) AS LOGIN_TIME,
                    CASE
                      WHEN LOGOUTDATETIME = '1900-01-01 00:00:00.000' THEN CAST(DATEADD(HH, 9, CREATEDDATETIME) AS DATE) -- 비정상 LOGOUT으로 인해 타임이 없는 경우 로그인 일자로 처리
                      WHEN LOGOUTDATETIME != '1900-01-01 00:00:00.000' THEN CAST(DATEADD(HH, 9, LOGOUTDATETIME) AS DATE)
                    END AS LOGOUT_DATE,
                    CASE
                      WHEN LOGOUTDATETIME = '1900-01-01 00:00:00.000' THEN '23:59:59.000' -- 비정상 LOGOUT으로 인해 타임이 없는 경우 그날 마지막 시간으로 처리
                      WHEN LOGOUTDATETIME != '1900-01-01 00:00:00.000' THEN CAST(DATEADD(HH, 9, LOGOUTDATETIME) AS TIME)
                    END AS LOGOUT_TIME
                FROM SYSUSERLOG
                WHERE TYPE = 0
                AND CREATEDDATETIME != '1900-01-01 00:00:00.000') AS USERTIMESHEET
            INNER JOIN USERINFO AS USERCOMPANY
            ON USERCOMPANY.ID = USERTIMESHEET.USERID
            WHERE 1=1
            AND USERTIMESHEET.LOGIN_DATE = @DATE
            GROUP BY USERCOMPANY.COMPANY,
                     USERTIMESHEET.USERID,
                     USERTIMESHEET.TYPE,
            USERTIMESHEET.LOGIN_DATE) AS USERTIMESHEETLIST;

OPEN USERCURSOR;

FETCH NEXT FROM USERCURSOR INTO @COMPANY, @USERID, @LOGIN_DATE, @LOGIN_TIMEUNIT, @LOGIN_TIME, @LOGOUT_DATE, @LOGOUT_TIMEUNIT, @LOGOUT_TIME

WHILE @@FETCH_STATUS = 0
BEGIN

   SET @USERTIMELOOP = @LOGOUT_TIMEUNIT - @LOGIN_TIMEUNIT;

   IF @USERTIMELOOP = 0 AND (@LOGOUT_TIMEUNIT-@LOGIN_TIMEUNIT) != 0
   BEGIN
      INSERT INTO #USERLOGBYCOMPANY (COMPANY, LOGDATE, LOGTIME, USERID) VALUES (@COMPANY, @DATE, 0, @USERID);
   END
   ELSE
   BEGIN
      IF @LOGIN_DATE != @LOGOUT_DATE
  BEGIN
     SET @USERTIMELOOP = 23 - @LOGIN_TIMEUNIT;
  END

  SET @USERTIMELOOP += 1;

      WHILE @USERTIMELOOP != 0
      BEGIN
         INSERT INTO #USERLOGBYCOMPANY (COMPANY, LOGDATE, LOGTIME, USERID) VALUES (@COMPANY, @DATE, (@LOGIN_TIMEUNIT - 1) + @USERTIMELOOP, @USERID);
         SET @USERTIMELOOP -= 1;
        IF @USERTIMELOOP <= 0
        BEGIN
    SET @USERTIMELOOP = 0;
            BREAK;
            END
      END
   FETCH NEXT FROM USERCURSOR INTO @COMPANY, @USERID, @LOGIN_DATE, @LOGIN_TIMEUNIT, @LOGIN_TIME, @LOGOUT_DATE, @LOGOUT_TIMEUNIT, @LOGOUT_TIME
   END
END
CLOSE USERCURSOR;
DEALLOCATE USERCURSOR;

SELECT COMPANY AS '법인',
       CAST(LOGDATE AS DATE) AS '조회일자',
       ISNULL([0], 0) AS '00 시',
   ISNULL([1], 0) AS '01 시',
   ISNULL([2], 0) AS '02 시',
   ISNULL([3], 0) AS '03 시',
   ISNULL([4], 0) AS '04 시',
   ISNULL([5], 0) AS '05 시',
   ISNULL([6], 0) AS '06 시',
   ISNULL([7], 0) AS '07 시',
   ISNULL([8], 0) AS '08 시',
   ISNULL([9], 0) AS '09 시',
   ISNULL([10], 0) AS '10 시',
   ISNULL([11], 0) AS '11 시',
   ISNULL([12], 0) AS '12 시',
   ISNULL([13], 0) AS '13 시',
   ISNULL([14], 0) AS '14 시',
   ISNULL([15], 0) AS '15 시',
   ISNULL([16], 0) AS '16 시',
   ISNULL([17], 0) AS '17 시',
   ISNULL([18], 0) AS '18 시',
   ISNULL([19], 0) AS '19 시',
   ISNULL([20], 0) AS '20 시',
   ISNULL([21], 0) AS '21 시',
   ISNULL([22], 0) AS '22 시',
   ISNULL([23], 0) AS '23 시'
FROM(SELECT COMPANY, LOGDATE, LOGTIME, USERCOUNT
     FROM(SELECT COMPANY, LOGDATE, LOGTIME, COUNT(USERID) AS USERCOUNT
          FROM #USERLOGBYCOMPANY
     GROUP BY COMPANY, LOGDATE, LOGTIME) AS USERLISTBYCOMPANY) AS COMPANYBYTIME
PIVOT
(
   SUM(USERCOUNT)
   FOR LOGTIME IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
) AS USERLISTBYCOMPANYPIVOT
ORDER BY COMPANY