SQL Datediff with group by

Question

I have a table called Login. I want to get the No. of days from the last login per user
See sample data below:

tableName: Login    
ID|UserName|LoginDateTime  
1 |User1   |04/09/12 18:07:06  
2 |User1   |04/09/12 18:07:51  
3 |User1   |04/09/12 18:21:41  
4 |Admin   |17/09/12 15:36:30  
5 |Admin   |17/09/12 15:36:30

Here is my SQL command:

SELECT LoginDateTime, UserName,(DATEDIFF (DAY, [LoginDateTime], GETDATE ())) 
AS [datediff] 
FROM Login;

Result:

ID|UserName|LoginDateTime    |No. of Days  
1 |User1   |04/09/12 18:07:06|414  
2 |User1   |04/09/12 18:07:51|414  
3 |User1   |04/09/12 18:21:41|414  
4 |Admin   |17/09/12 15:36:30|401  
5 |Admin   |17/09/12 15:36:30|401

But I want to remove the duplicate records

ID|UserName|LoginDateTime    |No. of Days   
1 |User1   |04/09/12 18:07:06|414  
2 |Admin   |17/09/12 15:36:30|401

Appreciate any help.

Esteban Elverdin · Accepted Answer · 2013-10-22T21:06:35.943

3

Try this, if you want to get the latest login for each user.

SELECT UserName, MAX([LoginDateTime]) AS LoginDateTime,
   (DATEDIFF (DAY, MAX([LoginDateTime]), GETDATE ())) as [datediff]
FROM Login
GROUP BY UserName

The idea is to use GROUP BY to group all your rows by Username, and Using MAX aggregate function get only the row with the latest LoginDateTime, then calculate the number of days using that latest loginDateTime.

edited Oct 22 '13 at 21:06

answered Oct 22 '13 at 17:47

Esteban Elverdin

3,552
1
17
21

thanks for the reply but I got an error "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – user2617053 Oct 22 '13 at 18:04
I updated the answer some minutes ago, check I added MAX to the loginDateTime within datediff function – Esteban Elverdin Oct 22 '13 at 18:05
1

Strange, the ID is in the GROUP BY, that was not causing any issue. – Esteban Elverdin Oct 22 '13 at 18:12
@Anjali, yes probably, I tried to explain a bit more the answer. – Esteban Elverdin Oct 22 '13 at 18:22
@Anjali, show your evidence if the query works fine if you include field ID – user2617053 Oct 22 '13 at 18:54

SQL Datediff with group by

1 Answers1