Cross joining sql tables by date

Question

I have a following table , table1

date            value   
-------------------------
2015-01-01      0
2015-01-02      0
2015-01-03      0
2015-01-04      0

And have a table2

datestart       dateend         value   
-------------------------------------
2015-01-02      2015-01-03      1

I would like to get a results like following

date            value   
-------------------------
2015-01-01      0
2015-01-02      1
2015-01-03      1
2015-01-04      0

I tried to use cross apply

select table1.date, temp.value
from table1
cross join
(select table2.value from table2 where
table2.startdate <= table1.date and table2.enddate > table1.date) as temp

but I end up with

date            value   
-------------------------
2015-01-02      1
2015-01-03      1

What is wrong in my code?

Answer 1

You can use a left join like this:

select table1.date, coalesce(table2.value,0) Value
from table1
left join table2
on table1.date between table2.startdate and table2.enddate 
order by 1

Although it'll get messy if you have overlapping dates in table 2. This may not be what you intended, but if you wanted to sum all values of the ranges that each date falls into you would do something like this:

select table1.date, sum(coalesce(table2.value,0)) Value
from table1
left join table2
on table1.date between table2.startdate and table2.enddate 
group by table1.date

Otherwise you would get duplicate dates in your output.