Sql-server – Replacing NULL values in LEFT JOIN with MAX function

sql server

I have created a query that join two tables and returns a date of the last log in of a particular user. If a user logged in multiple times, the query returns the most recent date:

CREATE TABLE Table1 (
    USERID varchar(255),
    NAME varchar(255)
)
CREATE TABLE Table2 (
    USERID varchar(255),
    LOGIN_TIME varchar(255)
)
INSERT INTO Table1(USERID, NAME)
VALUES ('XYZ_1234', 'RAMBO'),
       ('XYZ_1235', 'ARNOLD'),
       ('XYZ_1236', 'BRUCE')
INSERT INTO Table2(USERID,LOGIN_TIME)
VALUES ('1234','17.07.17'),
       ('1234','20.07.17'),
       ('1235', '27.08.17')

SELECT T1.NAME, MAX (T2.LOGIN_TIME)
CASE WHEN T2.LOGIN_TIME IS NULL THEN 5 ELSE T2.LOGIN_TIME
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.USERID='XYZ_' + T2.USERID
GROUP BY T1.NAME

http://www.sqlfiddle.com/#!18/c119d/2

What I'm struggling with is to replace "null" values that are returned every time when a user exist in table1 and does not exist in table2.
For that I was trying to write a "CASE" function (line 2 in the link above).

Best Answer

You can use ISNULL(MAX(T2.LOGIN_TIME), 'Default Value') to replace the NULL.

But first you should consider that LOGIN_TIME varchar(255) with values such as 17.07.17 that holds dates should be DATE data type. Always use DATE or DATETIME when using dates, as varchars become troublesome when comparing or ordering (and even understanding them depending on culture info).

For example, the MAX() on a varchar value of 17.07.18 against 27.08.17 will return 27.08.17 because 27 is higher as literal than 17, but the year of the latter (2018) is higher than the former, thus returning an incorrect result. This won't happen if the column is DATE or DATETIME type.

This is the fixed SQL:

CREATE TABLE Table1 (
    USERID varchar(255),
    NAME varchar(255)
)
CREATE TABLE Table2 (
    USERID varchar(255),
    LOGIN_TIME DATE -- It's date here!
)
INSERT INTO Table1(USERID, NAME)
VALUES ('XYZ_1234', 'RAMBO'),
       ('XYZ_1235', 'ARNOLD'),
       ('XYZ_1236', 'BRUCE')
INSERT INTO Table2(USERID,LOGIN_TIME)
VALUES ('1234','2017-07-17'),
       ('1234','2017-07-20'),
       ('1235', '2017-08-27') -- ISO format

SELECT 
    T1.NAME, 
    ISNULL(CONVERT(VARCHAR(200), MAX(T2.LOGIN_TIME)), '5')
FROM 
    Table1 T1
    LEFT JOIN Table2 T2 ON T1.USERID ='XYZ_' + T2.USERID
GROUP BY 
    T1.NAME

If you are using a default value of a literal '5' then you have no other choice than converting the max login time to varchar so both types are compatible.

Maybe you wanted to display a particular date instead?

Related Question