CODEWITHSUNDEEP
sqlsql-servert-sql
Giorgi Pilishvili
Giorgi Pilishvili

Reputation: 91

Compare dates in select

I have that query:

SELECT *
FROM   (SELECT *
        FROM   EventLog AS el
               RIGHT JOIN (SELECT Min(id) AS gId
                           FROM   EventLog
                           WHERE  Value IS NOT NULL
                           GROUP  BY Value) AS e
                       ON el.Id = e.gId) AS el
       LEFT JOIN (SELECT userid           AS uId,
                         Max([timestamp]) AS ts
                  FROM   UserAdditionStamp
                  GROUP  BY UserId) AS ua
              ON el.UserId = ua.uId
WHERE  el.EventTypeId = 3

And example data selected from that is:

Id  UserId     ts                              TimeStamp
-----------------------------------------------
461  1         2014-11-17 10:01:27.443      2013-01-08 16:28:52.557
468  25        NULL                         2013-01-08 17:06:15.967
441  1         2014-11-17 10:01:27.443      2013-01-09 11:00:43.947
461  29        2013-01-05 16:28:52.557      2013-01-09 13:29:19.143

How can I compare ts and TimeStamp dates? How to get which one is max date?

Main idea is that if ts is less than TimeStamp I should insert data in another table.

Upvotes: 0

Views: 72

Answers (1)

Carsten Massmann
Carsten Massmann

Reputation: 28196

SELECT *, CASE WHEN coalesce(timestamp,'01/01/1901')>coalesce(ts,'01/01/1901') THEN 1 ELSE 0 END TimeStampBigger
FROM   (SELECT *
        FROM   EventLog AS el
               RIGHT JOIN (SELECT Min(id) AS gId
                           FROM   EventLog
                           WHERE  Value IS NOT NULL
                           GROUP  BY Value) AS e
                       ON el.Id = e.gId) AS el
       LEFT JOIN (SELECT userid           AS uId,
                         Max([timestamp]) AS ts
                  FROM   UserAdditionStamp
                  GROUP  BY UserId) AS ua
              ON el.UserId = ua.uId
WHERE  el.EventTypeId = 3

TimeStamp and ts are both available as column names in the outer select and can therefore be compared. Either as output in an extra column (as I did it above: TimeStampBigger) or as another condition in the where clause.

Upvotes: 1

Related Questions