MSSQL-연속 출석일 수 조회 쿼리

LOGIN_LOG 테이블에서 연속 출석일수를 조회하는 쿼리 입니다. 

≫ 테이블 구조

  - 특정 기간 동안의 유저 로그인 기록 입니다. 일자별로 여러개 row가 있을 수 있는데, 

    우리가 원하는 것은 연속 출석일 수 를 구하는 것이기 때문에 유저별,일자별 그룹핑 하였습니다.

  - 결과 테이블 설명

1. 37번 유저 : 19일, 20일, 21일, 22일 연속해서 로그인 하였기 때문에 연속 출석일 수 = 4일

2. 38번 유저 : 6일, 7일 연속 출석일 수 = 2, 26일, 27일, 28일 연속 출석일 수 = 3일 

   로 계산 되었습니다.

기반 테이블	연속 출석일 수 계산 결과

≫ Query

DECLARE @v_start_date datetime, @v_end_date datetime;
SET @v_start_date = '2018-02-01 00:00:00';
SET @v_end_date = '2018-02-28 23:59:59';
 
with tmp_user_login_list as (
    select ta.usn, ta.login_date
    from (
        select usn, convert(char(10),login_date,23) login_date
        from TBL_USER_CONNECTION_LOG where LOGIN_DATE between @v_start_date and @v_end_date
    ) ta
    group by ta.usn, ta.login_date
    --order by ta.usn, ta.login_date asc
),
tmp_user_min_login_date as (
    select usn, min(convert(char(10),login_date,23)) start_login_date
    from TBL_USER_CONNECTION_LOG where LOGIN_DATE between @v_start_date and @v_end_date
    group by usn
)
select usn, MIN(login_date) std_dt, MAX(login_date) end_dt , COUNT(*) cnt
from ( 
        select  ta.usn, login_date
        ,   ROW_NUMBER() over (order by  ta.usn, login_date) idx        -- 자료의 일자별 순서
        ,   DATEDIFF( dd, login_date, tb.start_login_date )  no            -- 유저의 해당 기간 최초 접속 일
        from  tmp_user_login_list ta
        inner join tmp_user_min_login_date tb on ta.USN = tb.USN
)  r
group by usn, idx + no
having count(*) > 1        -- 연속 로그인 일수가 1일 이상인 자료
order by usn, idx + no  desc