0

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

2

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?

1

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   |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.