Value should be 0 between the two dates?

问题:

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?

回答1:

This will show value for the records present, 0 for the records missing:

SELECT  t2.id, t2.date,
        COALESCE(
        (
        SELECT  TOP 1 t2.value
        FROM    table1 t1
        WHERE   t2.date BETWEEN t1.fromdate AND t1.todate
                AND t2.id = t1.id
        ), 0) AS value
FROM    table2 t2

This will work other way around: 0 for the records present, value for the records missing:

SELECT  t2.id, t2.date,
        COALESCE(
        (
        SELECT  TOP 1 0
        FROM    table1 t1
        WHERE   t2.date BETWEEN t1.fromdate AND t1.todate
                AND t2.id = t1.id
        ), t2.value) AS value
FROM    table2 t2

回答2:

select t2.id, t2.date, coalesce(T.value, 0) value
from table2 t2
left join 
     (select t22.id, t22.date, t22.value
        from table2 t22
       where not exists (select null from table1 t1
                          where t22.date between t1.fromdate and t1.todate)
     ) T on t2.id = T.id and t2.date = T.date