0

This is my first post because I can usually find answers just by browsing the other questions here, but I have tried following instructions on a few of the suggestions, like this, this, this or this, but to no avail.

Ultimately I want to use my query to join two tables (in MS Dynamics AX) and show the last logged off date for users, along with various other details I have pulled from a number of tables. However, in the first instance, when I tried retrieving the last logged off date I had thousands of columns returned instead of the couple of hundred I expected (...and got without the last date column included).

But to make it simple, as the same behaviour is exhibited when just pulling user ID and last logged off date from a single table, this is my query (I've only returned the rightmost three characters of the username to anonymise the data):

Use MicrosoftDynamicsAX

SELECT  MAX(LOGOUTDATETIME) as LastLoggedOut,
        RIGHT(USERID,3) as UserName
FROM SYSUSERLOG

GROUP BY LOGOUTDATETIME,
    USERID

ORDER BY USERID

From this I get 17,632 rows returned, as it brings back every instance of a user with all the last logged off dates!

For instance, this is a small sample of the results returned by this query:

LastLoggedOut   UserName
2015-09-03 07:40:36.000 nts
2015-09-03 07:43:20.000 nts
2015-09-03 07:44:10.000 nts
2015-09-03 07:47:44.000 nts
2015-09-03 07:56:33.000 nts
2015-09-03 08:05:11.000 nts
2015-09-04 02:18:58.000 nts
2015-09-15 01:23:59.000 nts
2015-09-15 06:27:13.000 nts
2015-09-15 06:59:44.000 nts
2015-09-15 07:01:33.000 nts

Whereas, what I am looking for would be more like this:

LastLoggedOut   UserName
6/10/2016 2:14:57   nbr
6/10/2016 2:14:22   ulc
6/10/2016 2:14:18   nne
6/10/2016 2:14:10   nci
6/10/2016 2:13:30   hae
6/10/2016 2:12:46   001
6/10/2016 2:11:31   idb
6/10/2016 2:10:38   rin

I have tried swapping the WHERE clause for a HAVING, as well as trying a CTE and nesting a SELECT within the WHERE clause, but my knowledge of SQL is a little patchy and I was hoping one of you clever people might be able to offer some assistance.

Ta

Dan

Community
  • 1
  • 1
Dan Ladle
  • 3
  • 1

1 Answers1

0

You're grouping by LogoutDateTime. Just remove that.

Use MicrosoftDynamicsAX

SELECT  MAX(LOGOUTDATETIME) as LastLoggedOut,
        RIGHT(USERID,3) as UserName
FROM SYSUSERLOG

GROUP BY USERID

ORDER BY USERID
Alex Kwitny
  • 11,211
  • 2
  • 49
  • 71