LEFT JOIN with more than one condition in ON - SQL Server

Question

Hi, guys, all right?

I’m putting together a query, and I need to do a Join. In this case, the AIV table records have a start date and may or may not have an end date. I want them to cross as follows:

1 - If the end date of the AIV table is null, the date of the CPC table must be greater than the start date of the AIV;

2 - If the end date of the AIV table is not null, the date of the CPC table must be between the start date and the end date of the AIV;

It turns out that when I try to write that way:

LEFT JOIN ApontamentoImpactoVigencia AIV ON 
                                    AIV.AssuntoId = AA.AssuntoId 
                                    AND 
                                    IIF(AIV.Fim_Data IS NOT NULL, (CPC.Data between AIV.Inicio_Data and AIV.Fim_Data), CPC.Data >= AIV.Inicio_Data)

It presents error, not accepting the "between" and nor the sign of >=

If I try to do it this way:

LEFT JOIN ApontamentoImpactoVigencia AIV ON 
                                AIV.AssuntoId = AA.AssuntoId 
                                AND 
                                CPC.Data >= AIV.Inicio_Data

And

LEFT JOIN ApontamentoImpactoVigencia AIV ON 
                                AIV.AssuntoId = AA.AssuntoId 
                                AND 
                                CPC.Data between AIV.Inicio_Data and AIV.Fim_Data

SQL Server accepts (but then does not meet the criteria I need). Therefore, the error is not in the "between" or in the ">="..... I mean, it has something to do with the IIF.

(With CASE WHEN the same thing happens)

Anyone have any idea how to fix this ?? Thanks in advance!

Answer 1

Good night,

I think the way you put it won’t work because IIF expects one value for when the condition is true and another for when it isn’t, and not another condition. And the same goes for Case. Here is a suggested test using the Coalesce function to change the end value of the between if the end date is null:

LEFT JOIN ApontamentoImpactoVigencia AIV ON 
    AIV.AssuntoId = AA.AssuntoId 
    AND 
    CPC.Data between AIV.Inicio_Data and coalesce(AIV.Fim_Data, CPC.Data)

Edit: traditional alternative

LEFT JOIN ApontamentoImpactoVigencia AIV ON 
    AIV.AssuntoId = AA.AssuntoId 
    AND 
    (
      CPC.Data >= AIV.Inicio_Data and 
      (
         CPC.Data <= AIV.Fim_Data or
         AIV.Fim_Data is null
      )
    )

I hope it helps