USP_GET_LOGIN_USAGE_TRACKING_DATA

    CREATE procedure dbo.USP_GET_LOGIN_USAGE_TRACKING_DATA (
        @ProductName varchar(4)
    )
    as
    BEGIN
        DECLARE @TotalUsers INT;
        DECLARE @TotalDeletedUsers INT;
        DECLARE @TotalDefaultSupervisors INT;
        DECLARE @TotalDefaultSupervisorsLoginsInCurrentYear INT;
        DECLARE @LastLoginDateDefaultSupervisors DATE;
        DECLARE @TotalSupervisorUsers INT;
        DECLARE @TotalSupervisorsLoginsInCurrentYear INT;
        DECLARE @LastLoginDateSupervisorUsers DATE;
        DECLARE @TotalAdminUsers INT;
        DECLARE @TotalAdminUsersLoginsInCurrentYear INT;
        DECLARE @LastLoginDateAdminUsers DATE;
        DECLARE @TotalRegularUsers INT;
        DECLARE @TotalRegularUsersLoginsInCurrentYear INT;
        DECLARE @LastLoginDateRegularUsers DATE;
        DECLARE @CurrentYear INT;
        DECLARE @MostRecentAdminUserLogin DATE;
        DECLARE @MostRecentRegularUserLogin DATE;

        SET @MostRecentAdminUserLogin = Cast('1753-1-1' AS DATE)
        SET @MostRecentRegularUserLogin = Cast('1753-1-1' AS DATE)
        SET @CurrentYear =Year(Getutcdate())

        SELECT @TotalUsers = Count(ID)
        FROM   Clientusers

        SELECT @TotalDeletedUsers = Count(ID)
        FROM   Clientusers  WHERE Deleted = 1

        DECLARE @DefaultSupervisors TABLE
          (
             ID            INT,
             DateLastLogin DATETIME NULL
          )

        INSERT INTO @DefaultSupervisors
        SELECT ID,
               DateLastLogin
        FROM   dbo.ClientUsers
        WHERE  InternalUser = 1

        DECLARE @SupervisorUsers TABLE
          (
             ID            INT,
             DateLastLogin DATETIME NULL
          )

        INSERT INTO @SupervisorUsers
        SELECT ID,
               DateLastLogin
        FROM   dbo.ClientUsers
        WHERE  IsSupervisor = 1

        DECLARE @AdminUsers TABLE
          (
             ID INT
          )

        INSERT INTO @AdminUsers
        SELECT DISTINCT CLIENTUSERS.ID
        FROM   dbo.CLIENTUSERS
               INNER JOIN dbo.USERROLES
                       ON USERROLES.ClientUsersID = CLIENTUSERS.ID
               INNER JOIN dbo.CLIENTROLES
                       ON CLIENTROLES.ID = USERROLES.ClientRolesID
               INNER JOIN dbo.CMSSECURITYGROUPCLIENTROLECLIENTSITE
                       ON CMSSECURITYGROUPCLIENTROLECLIENTSITE.CLIENTROLESID = CLIENTROLES.ID
               INNER JOIN dbo.CMSSECURITYGROUP
                       ON CMSSECURITYGROUP.ID = CMSSECURITYGROUPCLIENTROLECLIENTSITE.CMSSECURITYGROUPID
               INNER JOIN dbo.CMSSECURITYGROUPTASK
                       ON CMSSECURITYGROUPTASK.CMSSECURITYGROUPID = CMSSecurityGroup.ID
        WHERE  CLIENTUSERS.InternalUser = 0
               AND CLIENTUSERS.IsSupervisor = 0

        DECLARE @RegularUsers TABLE
          (
             ID            INT,
             DateLastLogin DATETIME NULL
          )

        INSERT INTO @RegularUsers
        SELECT ID,
               DateLastLogin
        FROM   dbo.ClientUsers
        WHERE  ClientUsers.id NOT IN (SELECT ID
                                      FROM   @DefaultSupervisors
                                      UNION
                                      SELECT ID
                                      FROM   @SupervisorUsers
                                      UNION
                                      SELECT ID
                                      FROM   @AdminUsers)

        SELECT @TotalDefaultSupervisors = Count(ID),
               @LastLoginDateDefaultSupervisors = Max(DateLastLogin)
        FROM   @DefaultSupervisors

        SELECT @TotalDefaultSupervisorsLoginsInCurrentYear = Count(ID)
        FROM   @DefaultSupervisors DS
        WHERE  DS.DateLastLogin IS NOT NULL
               AND Year(DS.DateLastLogin) = @CurrentYear

        SELECT @TotalSupervisorUsers = Count(ID),
  @LastLoginDateSupervisorUsers = Max(DateLastLogin)
        FROM   @SupervisorUsers

        SELECT @TotalSupervisorsLoginsInCurrentYear = Count(ID)
        FROM   @SupervisorUsers
        WHERE  DateLastLogin IS NOT NULL
               AND Year(DateLastLogin) = @CurrentYear

        SELECT @TotalAdminUsers = Count(DISTINCT CLIENTUSERS.UserName),
               @LastLoginDateAdminUsers = Max(CLIENTUSERS.DateLastLogin)
        FROM   dbo.CLIENTUSERS
               INNER JOIN @AdminUsers AU
                       ON AU.ID = Clientusers.ID

        SELECT @TotalAdminUsersLoginsInCurrentYear = Count(CLIENTUSERS.ID)
        FROM   dbo.CLIENTUSERS
               INNER JOIN @AdminUsers AU
                       ON AU.ID = Clientusers.ID
        WHERE  DateLastLogin IS NOT NULL
               AND Year(DateLastLogin) = @CurrentYear

        SELECT @TotalRegularUsers = Count(ID),
               @LastLoginDateRegularUsers = Max(DateLastLogin)
        FROM   @RegularUsers

        SELECT @TotalRegularUsersLoginsInCurrentYear = Count(ID)
        FROM   @RegularUsers
        WHERE  DateLastLogin IS NOT NULL
               AND Year(DateLastLogin) = @CurrentYear

        DECLARE @OrganizationDetails NVARCHAR(400)

        SELECT @OrganizationDetails = CLARIFYSITEID,
               @OrganizationDetails = @OrganizationDetails + ' - ' + INSTALLATIONNAME
        FROM   dbo.INSTALLATIONINFO;

        IF @LastLoginDateDefaultSupervisors IS NOT NULL
           AND @LastLoginDateDefaultSupervisors > @MostRecentAdminUserLogin
          BEGIN
              SET @MostRecentAdminUserLogin = @LastLoginDateDefaultSupervisors
          END

        IF @LastLoginDateSupervisorUsers IS NOT NULL
           AND @LastLoginDateSupervisorUsers > @MostRecentAdminUserLogin
          BEGIN
              SET @MostRecentAdminUserLogin = @LastLoginDateSupervisorUsers
          END

        IF @LastLoginDateAdminUsers IS NOT NULL
           AND @LastLoginDateAdminUsers > @MostRecentAdminUserLogin
          BEGIN
              SET @MostRecentAdminUserLogin = @LastLoginDateAdminUsers
          END

        IF @LastLoginDateRegularUsers IS NOT NULL
          BEGIN
              SET @MostRecentRegularUserLogin = @LastLoginDateRegularUsers
          END

        SELECT @ProductName + '-' + 'UserLogins' + '-'
               + @OrganizationDetails                             AS 'Group',    
               @TotalUsers                                        AS TotalUsers,
               @TotalDeletedUsers                                 AS TotalDeletedUsers,
               @TotalDefaultSupervisors                           AS TotalDefaultSupervisors,
               @TotalSupervisorUsers                              AS TotalSupervisorUsers,
               @TotalAdminUsers                                   AS TotalAdminUsers,
               @TotalRegularUsers                                 AS TotalRegularUsers,
               @TotalDefaultSupervisorsLoginsInCurrentYear        AS TotalDefaultSupervisorsLoginsInCurrentYear,
               @TotalSupervisorsLoginsInCurrentYear               AS TotalSupervisorsLoginsInCurrentYear,
               @TotalAdminUsersLoginsInCurrentYear                AS TotalAdminUsersLoginsInCurrentYear,
               @MostRecentAdminUserLogin                          AS MostRecentAdminUserLogin,
               @TotalRegularUsersLoginsInCurrentYear              AS TotalRegularUsersLoginsInCurrentYear,
               @MostRecentRegularUserLogin                        AS MostRecentRegularUserLogin,
              'Organization has '
               + CONVERT(VARCHAR(max), @TotalUsers)
               + ' users out which ' + CONVERT(VARCHAR(max), @TotalDeletedUsers) + ' are in deleted state.'
               + CONVERT(VARCHAR(max), @TotalDefaultSupervisors +@TotalSupervisorUsers+@TotalAdminUsers )
               + ' have organizational rights and '
               + CONVERT(VARCHAR(max), @TotalRegularUsers)
               + ' regular BBIS Users.'
               + CONVERT(VARCHAR(max), @TotalDefaultSupervisorsLoginsInCurrentYear + @TotalSupervisorsLoginsInCurrentYear + @TotalAdminUsersLoginsInCurrentYear)
               + '/'
               + CONVERT(VARCHAR(max), @TotalDefaultSupervisors +@TotalSupervisorUsers+@TotalAdminUsers )
               + ' users with organizational rights logged-in in the current year('
               + CONVERT(VARCHAR(max), @CurrentYear)
               + ') and the most recent login was on '
               + CONVERT(VARCHAR(10), @MostRecentAdminUserLogin, 101)
               + '. '
               + CONVERT(VARCHAR(max), @TotalRegularUsersLoginsInCurrentYear)
               + '/'
               + CONVERT(VARCHAR(max), @TotalRegularUsers )
               + ' regular users logged-in in the current year('
               + CONVERT(VARCHAR(max), @CurrentYear)
               + ') and the most recent login was on '
               + CONVERT(VARCHAR(10), @MostRecentRegularUserLogin, 101)
               + '. ' as LoginDetails
    end