Brian Brian - 4 months ago 15
SQL Question

Counting DATEDIFF from MAX records

Using SQL Server 2008 R2 I am trying to pull together the last login time from users that are being stored in a table daily.

The query below pulls that information for each user:

SELECT
[Login_name],
MAX([last_login_time]) as Last_login_date,
[server_instance]
FROM
[dbo].[User_Login_Audit]
WHERE
Login_name NOT IN ('NT AUTHORITY\SYSTEM', 'sa','')
GROUP BY
Login_name, server_instance


The results are as follows:

User_name | 2016-07-28 | Server/Instance


What I want is to be able to add the number of days between
GETDATE()
that the max record date and add it as the last column..

I have tried using the following code, but it returns a value for each day that has been recorded for that user. Any help would be appreciated as I am sure I'm missing something obvious.

SELECT
[Login_name],
MAX([last_login_time]) AS Last_login_date,
[server_instance],
DATEDIFF(day, getdate(), last_login_time) AS Days
FROM
[dbo].[User_Login_Audit]
WHERE
Login_name NOT IN ('NT AUTHORITY\SYSTEM', 'sa','')
GROUP BY
Login_name, server_instance, last_login_time

Answer

Try this:

SELECT [Login_name],
    max([last_login_time]) Last_login_date,
  [server_instance],
  DATEDIFF(day, getdate(), max([last_login_time])) Days
FROM [dbo].[User_Login_Audit]
where  Login_name not in ('NT AUTHORITY\SYSTEM', 'sa','')
Group by Login_name, server_instance
Comments