YVS1102 YVS1102 - 9 months ago 57
SQL Question

SQL Server Joining with between condition

Please check my table first. My first table is called


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

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

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

Sorry for my bad English.

Answer Source

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.