Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Counting UserTypes as well as how many times each user type has logged in

    I have three tables: UserType, UserAccount, and UserAccountLoginHistory and I've stripped out most unrelated fields:

    Code:
    
    UserType
    ----------
    UserTypeId
    UserType
    Active
    ...
    
    
    UserAccount
    -----------
    UserId
    UserTypeId
    LastName
    FirstName
    MiddleName
    UserName
    ...
    Address1
    Address2
    City
    State
    Zip
    ...
    
    
    UserAccountLoginHistory
    ----------------
    UserAccountLoginHistoryId
    UserId
    dateLogin
    ...
    I can easily write a query to tell me how many users of each user type I have:

    Code:
    select 
    	ut.UserType
    	, count(ua.UserId) as [Count]
    from
    	UserAccount ua
    	inner join UserType ut
    		on ut.UserTypeId = ua.UserTypeId
    group by
    	ut.UserType
    order by
    	count(ua.UserId) desc
    I believe, but am not sure, that this query will return how many users of each type have ever logged in:

    Code:
    select 
    	ut.UserType
    	, count(ua.UserId) as [Count]
    from
    	UserAccount ua
    	inner join UserType ut
    		on ut.UserTypeId = ua.UserTypeId
    	inner join UserAccountLoginHistory ualh
    		on ualh.UserIdx = ua.UserIdx
    group by
    	ut.UserType
    order by
    	count(ua.UserId) desc
    What I need to do is not only display in a tabular format the count of Users of each UserType, but also how many Users of each UserType have logged in in the past day and include a zero in the last column if none of that type have logged in.

    How could I do this?

    Edit:
    A sample output would look something like this:
    Code:
    UserType	Count	LoginsToday
    --------	-----	-----------
    Admin		5	23
    Assistant	12	5
    Recruiter	12	8
    Officer		5	0
    Maintenance	10	0
    Last edited by wey97; 03-17-10 at 18:04.

  2. #2
    Join Date
    Mar 2003
    Posts
    130
    I've gotten as far as a CTE to give me the past 24 hours logins:

    Code:
    with uax (UserTypeId, Past24HrLogins)
    as
    (
    	select
    		ut.UserTypeId
    		, count(ualh.UserId) as Past24HrLogins
    	from 
    		UserAccountLoginHistory ualh
    	inner join UserAccount ua 
    		on ua.UserId = ualh.UserId
    	inner join UserType ut
    		on ut.UserTypeId = ua.UserTypeId
    	where 
    		ualh.dateLogin > dateadd(day, -1, getdate())
    	group by 
    		ut.UserTypeId	
    )
    
    select 
    	ut.UserType
    	, isnull(uax.Past24HrLogins, 0) as Past24HrLogins
    from 
    	UserType ut
    	
    	left join uax 
    		on ut.UserTypeId = uax.UserTypeId
    		
    order by 
    	Past24HrLogins desc
    I still need to show the total number of users of that type side by side with the past 24 hour logins.

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    I can select from two different CTEs and join them on UserType.

    Code:
    with utx1 (
    	UserTypeId
    	, UserCount
    )
    as
    (
    	select
    		ut.UserTypeId
    		, count(ut.UserType) as UserCount
    	from
    		UserType ut	
    	inner join 
    		UserAccount ua 
    			on ua.UserTypeId = ut.UserTypeId
    	group by 
    		ut.UserTypeId
    )
    
    ,
    
    utx2 (
    	UserTypeId
    	, Past24HrLogins
    )
    as
    (
    	select
    		ut.UserTypeId
    		, count(ualh.UserId) as Past24HrLogins
    	from 
    		UserAccountLoginHistory ualh
    	inner join 
    		UserAccount ua 
    			on ua.UserId = ualh.UserId
    	inner join 
    		UserType ut
    			on ut.UserTypeId = ua.UserTypeId
    	where
    		ualh.dateLogin >= dateadd(day, -1, getdate())
    	group by
    		ut.UserTypeId
    )
    
    
    select
    	ut.UserType
    	, isnull(utx1.UserCount, 0) as UserCount
    	, isnull(utx2.Past24HrLogins, 0) as Past24HrLogins
    from 
    	UserType ut
    left join 
    	utx1
    		on utx1.UserTypeId = ut.UserTypeId
    left join 
    	utx2 
    		on utx2.UserTypeId = ut.UserTypeId
    order by
    	UserCount desc,
    	Past24HrLogins desc
    Do you think this is optimal?
    Last edited by wey97; 03-18-10 at 12:41.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't know where you're going with those CTEs but it seems to me you can do what you asked in post #1 with a couple of left outer joins...
    Code:
    SELECT ut.UserType
         , COUNT(ua.UserId) AS [Count]
         , COUNT(ualh.UserId) AS LoginsToday
      FROM UserType AS ut
    LEFT OUTER
      JOIN UserAccount AS ua
        ON ua.UserTypeId = ut.UserTypeId
    LEFT OUTER
      JOIN UserAccountLoginHistory AS ualh
        ON ualh.UserId = ua.UserId
       AND ualh.dateLogin > DATEADD(DAY, -1, GETDATE())
    GROUP 
        BY ut.UserType
    ORDER 
        BY COUNT(ua.UserId) DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2003
    Posts
    130
    That works fine.

    Now I need to include an additional column that will give me the number of logins in the past week. CTEs were the only way I could do that. I'm not sure if left outer will work.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ut.UserType
         , COUNT(ua.UserId) AS [Count]
         , COUNT(ualh.UserId) AS LoginsLastWeek
         , COUNT(CASE WHEN ualh.dateLogin > DATEADD(DAY, -1, GETDATE())
                      THEN 'uff-da' 
                      ELSE NULL END) AS LoginsToday
      FROM UserType AS ut
    LEFT OUTER
      JOIN UserAccount AS ua
        ON ua.UserTypeId = ut.UserTypeId
    LEFT OUTER
      JOIN UserAccountLoginHistory AS ualh
        ON ualh.UserId = ua.UserId
       AND ualh.dateLogin > DATEADD(DAY, -7, GETDATE())
    GROUP 
        BY ut.UserType
    ORDER 
        BY COUNT(ua.UserId) DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2003
    Posts
    130
    Thanks, that worked fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •