last login from users

• Jack Corbett

  SSC Guru

  Points: 184395

  More actions

  October 9, 2008 at 8:30 am

  #882925

  I think this query returns what you want:

  [font="Courier New"]SELECT

     MEMBER.MEM_CONTACT,

     MEMBER.MEM_COMPANY,

     MEMBER.MEM_EMAIL,

     MEMBER.MEM_TIMESTAMP AS 'DATE REGISTERED',

     MAX(MEMBERLOGIN.MLO_TIMESTAMP) AS LAST_LOGIN_DATE

  FROM

     *****.DBO.MEMBER MEMBER JOIN

     *****.DBO.WEBCOUNTRY WEBCOUNTRY ON

         MEMBER.MEM_WCT_WEBCOUNTRYID = WEBCOUNTRY.WCT_WEBCOUNTRYID LEFT JOIN

     *****.DBO.MEMBERLOGIN MEMBERLOGIN ON

         MEMBER.MEM_MEMBERID = MEMBERLOGIN.MLO_MEM_MEMBERID

  WHERE

     ((MEMBER.MEM_ISONEMAILLIST=1) AND

     (WEBCOUNTRY.WCT_COUNTRY LIKE '%CANADA%'))

  GROUP BY

     MEMBER.MEM_CONTACT,

     MEMBER.MEM_COMPANY,

     MEMBER.MEM_EMAIL,

     MEMBER.MEM_TIMESTAMP

  [/font]

  A few comments:

  1. I changed your query to use ANSI Standard join syntax. It is easier to read, IMO, and the syntax you are using will be deprecated in a future version of SQL Server.

  2. I used a LEFT OUTER JOIN (LEFT JOIN) to the MEMBER_LOGIN table as this is how you wold find all users even if they have never logged in. Those whose LAST_LOGIN_DATE is null have never logged in.

  3. To get the LAST_LOGIN_DATE you need to use the MAX function along with a GROUP BY as demonstrated in the query.

  4. The query you posted used the LIKE operator without any wildcards so it actually evaluates to "=". I added leading and trailing wildcards. If you really mean "=" then use "=".

  If you have any more questions, add them to the thread.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question