Average Logins per day SQL Query

Question

Hi,

I have a users table in MSSQL and i want to find out the average number of logins for all users per day?

I am thinking something with division but not entirely sure the USER table looks like this

  [ID]
  ,[APPLICATION_ID]
  ,[LOGIN_NAME]
  ,[EMAIL]
  ,[MOBILE_PHONE1]
  ,[MOBILE_PHONE2]
  ,[TITLE]
  ,[FIRST_NAME]
  ,[LAST_NAME]
  ,[BIRTHDAY]
  ,[ADDRESS1]
  ,[ADDRESS2]
  ,[CITY]
  ,[COUNTRY_ID]
  ,[POSTAL_CODE]
  ,[PO_BOX]
  ,[HOME_PHONE]
  ,[OFFICE_PHONE]
  ,[FAX]
  ,[REMARKS]
  ,[DISPLAY_NAME]
  ,[MAIDEN_NAME]
  ,[ADDRESS3]
  ,[ADDRESS4]
  ,[KEYWORD1]
  ,[KEYWORD2]
  ,[KEYWORD3]
  ,[KEYWORD4]
  ,[CREATED_BY]
  ,[CREATED_ON]
  ,[MODIFIED_BY]
  ,[MODIFIED_ON]
  ,[DELETED]
  ,[DELETED_BY]
  ,[DELETED_ON]
  ,[VERSION]
  ,[IDENTIFICATION_NO]
  ,[GENDER_REFERENCE_ID]
  ,[IDENTIFICATION_REFERENCE_ID]
  ,[registration_date]
  ,[contact_person]
  ,[contact_email]
  ,[alternative_email]
  ,[electron_card_number]
  ,[LAST_LOGIN]
  ,[LAST_SUCCESSFUL_LOGIN]
  ,[LAST_UNSUCCESSFUL_LOGIN]

Answer 1

There is nowhere in that table that tracks when a user has logged in, other than the last time. You need a separarte table to record all logins, then you can calculate the average number per day.

Answer 2

From the table you provide, there is no way of calculating an average. To do this, you would need an historical record of user logins.

Answer 3

I would suggest creating another smaller table to help store histoical logins..

e.g.

[pk]
[USER_ID]
[LOGIN TIME]
etc...

This table could be linked to your main table using the USER_ID (or whatever key field you use to identify the user).

You could then query the smaller table to get a count of logins for all users per day.