CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
bwilliamson
bwilliamson

Reputation: 391

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

Upvotes: 2

Views: 84

Answers (3)

Cato
Cato

Reputation: 3701

    SELECT 
    u1.[Login_name],
    u1.[server_instance],
    COALESCE(MAX(u2.[last_login_time]),'No previous') AS Last_login_date,
    COALESCE(DATEDIFF(day, getdate(), MAX(u2.last_login_time)), 'No previous') AS Days
FROM 
    [dbo].[User_Login_Audit] ua1
    LEFT JOIN [dbo].[User_Login_Audit] ua2 ON ua1.[Login_name] = ua2.[Login_name] and 
                                                ua1.[server_instance] = ua2.[server_instance] and 
                                                u1.[last_login_time] > u2 .[last_login_time]
WHERE
    u1.Login_name NOT IN ('NT AUTHORITY\SYSTEM', 'sa','')
GROUP BY 
    u1.Login_name, u1.server_instance

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28930

 ;with cte
 as
( 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
 )
select *,
 DATEDIFF(day,getdate(),Last_login_date)
from cte

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146557

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

Upvotes: 2

Related Questions