- hosts: all
  tasks: 
  - name: Concurrent User
    community.general.mssql_script:
      login_user: "{{ database_admin_user }}"
      login_password: "{{ database_admin_password }}"
      login_host: "{{ database_host }}"
      db: "{{ db_name }}"
      script: |
        use {{ db_name }}
        DECLARE @start Datetime = '{{ begin_date }}'; 
        DECLARE @end Datetime = '{{ end_date }}'; 
        WITH RankedDays AS (
        SELECT
        login_day,
        column_{{ month }}{{ year }}_concurrent_user_count,
        ROW_NUMBER() OVER (PARTITION BY MONTH(login_day) ORDER BY column_{{ month }}{{ year }}_concurrent_user_count DESC) AS rn
        FROM   
        (
        select login_day, max(column_{{ month }}{{ year }}_concurrent_user_count) column_{{ month }}{{ year }}_concurrent_user_count from (
        SELECT 
        DISTINCT CAST(ul_main.logindate AS DATE) AS login_day, 
        (	SELECT 
        COUNT(DISTINCT CHECKSUM(ul_sub.fk_userid, ul_sub.createcomputer)) 
        FROM 
        tblUserLogin ul_sub 
        INNER JOIN tblApplication a ON a.pk_applicationid = ul_sub.fk_applicationid 
        WHERE 
        a.enumvalue IN (2, 3, 4, 5, 7, 11, 13, 15, 6, 9, 18, 10) --no rosercatalog!
        AND ul_sub.logindate <= ul_main.logindate
        AND (
        CASE 
        WHEN lastheartbeat < logoutdate 
        THEN lastheartbeat
        ELSE logoutdate
        END
        ) >= ul_main.logindate
        ) AS column_{{ month }}{{ year }}_concurrent_user_count
        FROM 
        tblUserLogin ul_main 
        WHERE 
        ul_main.logindate >= @start
        AND ul_main.logindate < @end
        ) max_of_each_month group by login_day ) sub_query
        WHERE login_day LIKE '{{ year }}%'
        )
        SELECT
        login_day,
        column_{{ month }}{{ year }}_concurrent_user_count
        FROM
        RankedDays
        WHERE
        rn <= 1
        ORDER BY
        MONTH(login_day),
        column_{{ month }}{{ year }}_concurrent_user_count DESC;