YVS1102 YVS1102 - 15 days ago 8
SQL Question

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.

Answer

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.