Problem with datetime comparison

Problem with datetime comparison

  

Doing the query below. I dont have any records on {Interaction}. for some reason {Candidate}.[LastUpdate] >= {Interaction}.[LastUpdate] is returning that {Interaction}.[LastUpdate] is always bigger even if is always empty. Anyone knows why?

SyntaxEditor Code Snippet

SELECT {Candidate}.[Id],
{Candidate}.[FirstName],
{Candidate}.[LastName],
{Candidate}.[Email],
{Candidate}.[PhoneNumber],
{Candidate}.[Function],
{Candidate}.[LinkedinLink],
{Candidate}.[LastUpdate],
{Interaction}.[LastUpdate],
CASE WHEN {Candidate}.[LastUpdate] >= {Interaction}.[LastUpdate] THEN {Candidate}.[LastUpdate] ELSE {Interaction}.[LastUpdate] END LastUpdateMax
FROM {Candidate} LEFT JOIN {Interaction} on {Candidate}.[Id] = {Interaction}.[CandidateId]

Hi Marcelo,

It might be because, the {Interaction} do not have any records, due to which the first case condition is false and giving output of ELSE which is no date [default Date].


Let me try to replicate it at my end and give you more information.


Shashank...

Solution

Hi Marcelo,

Try this in your CASE statement:

CASE WHEN {Interaction}.[Id] IS Null or {Candidate}.[LastUpdate] >= {Interaction}.[LastUpdate] THEN {Candidate}.[LastUpdate] ELSE {Interaction}.[LastUpdate] END LastUpdateMax
FROM {Candidate} LEFT JOIN {Interaction} on {Candidate}.[Id] = {Interaction}.[CandidateId]

You need to test first if there's an Interaction record for the corresponding Candidate. If it's NULL then you want the LastUpdate from the Candidate.

See if it works.

Cheers,

José

Solution

Hi Marcelo, 

Shashank is probably right. 

Try this:

SELECT {Candidate}.[Id],
{Candidate}.[FirstName],
{Candidate}.[LastName],
{Candidate}.[Email],
{Candidate}.[PhoneNumber],
{Candidate}.[Function],
{Candidate}.[LinkedinLink],
{Candidate}.[LastUpdate],
{Interaction}.[LastUpdate],
CASE WHEN {Candidate}.[LastUpdate] >= {Interaction}.[LastUpdate] 

or {Interaction}.[LastUpdate] is null


THEN {Candidate}.[LastUpdate] ELSE {Interaction}.[LastUpdate] END LastUpdateMax
FROM {Candidate} LEFT JOIN {Interaction} on {Candidate}.[Id] = {Interaction}.[CandidateId]

 Hope it helps

best regards

Graça


Hi all,

Yes it looks like I need to check if {Interaction}  has a record. In aggregates is the same thing.

Thanks All