Using SQL Server 2000
I want to compare the table2.date between table1.from, table1.todate, if exist then value should be 0 (zero)
Table1
ID FromDate ToDate
001 20090801 20090815
002 20090817 20090820
…,
Table2
Id Date Value
001 20090730 100
001 20090731 200
001 20090801 300
001 20090802 400
…
001 20090815 0
001 20090816 250
…
From the above two table I want to ID, Date, value from table2 where table2.date between table1.fromdate and table1.todate then table2.value =0
Expected Output
Id Date Value
001 20090730 100
001 20090731 200
001 20090801 0
001 20090802 0
…
001 20090815 0
001 20090816 250
How to make a query for this condition?