Replacing NULL values in LEFT JOIN with MAX function

Question

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).

EzLo · Accepted Answer · 2018-06-29 11:22:30Z

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?

Scott Hodgin - Retired · Accepted Answer · 2018-06-29 11:14:30Z

I used a common table expression to do the group by and then referenced that CTE with the CASE expression. Your CASE expression didn't really make sense to me as far as the replacement value of '5', but that's what I used.

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

| NAME   | LOGIN_TIME |
|--------|------------|
| ARNOLD | 27.08.17   |
| BRUCE  | 5          |
| RAMBO  | 20.07.17   |

Stack Exchange Network

Replacing NULL values in LEFT JOIN with MAX function

2 Answers 2

Your Answer

Hot Network Questions

Replacing NULL values in LEFT JOIN with MAX function

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions