CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
YVS1102
YVS1102

Reputation: 2748

SQL Server Joining with between condition

Please check my table first. My first table is called DinasHoHeader:

    Kode    Desc    startdate   enddate
      1     Desc 1  2016-11-08  2016-11-08
      2     Desc 2  2016-11-16  2016-11-16

and the second table is called CutOff

CutOffCode         Month    Year       from          to
CO-2016-10-16-15    10      2016    2016-09-16  2016-10-15
CO-2016-11-16-15    11      2016    2016-10-16  2016-11-15
CO-2016-12-16-15    12      2016    2016-11-16  2016-12-15

I want to join the two. Here is what I try

SELECT a.*, b.CutOffCode 
FROM DinasHoHeader a
LEFT JOIN CutOff b ON b.[From] BETWEEN a.[startdate] AND a.enddate  
                   AND b.[to] BETWEEN a.[startdate] AND a.enddate

But with my query I can't get the CutOffCode.

So my question is: how to join those two tables so I can get the cutoffcode?

Sorry for my bad English.

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think your logic is backwards:

select dhh.*, co.CutOffCode
from DinasHoHeader dhh left join
     CutOff co
     on dhh.[startdate] between co.[From] and co.[to] and
        dhh.[enddate] between co.[From] and co.[to];

This should at least have some matches.

Upvotes: 3

Related Questions