views:

101

answers:

2

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  A: 

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
Quassnoi
Getting Wrong output.
Gopal
Could you pleast post the Wrong output you get?
Quassnoi
Showing error in "SELECT TOP 1 t2.value FROM table1 t1" Because i don't have value in table1, check the table
Gopal
This value is a correlated value, taken from the outer subquery. Could you please post the exact error message?
Quassnoi
Got It, But it is working as opposite, when the date is not available in table1, Now it is showing 0, if the date was compared then 0 else not 0, it should work like that only.
Gopal
Just replace `0` and `t2.value` in the subquery. See the post update.
Quassnoi
And also it was not comparing with id's also. 0 is displaying for all id's
Gopal
`@Gopal`: fixed the `id` issue too.
Quassnoi
It was not comparing with id's.
Gopal
Query Check this.SELECT t2.personid, t2.cardeventdate, COALESCE ((SELECT TOP 1 t2.workedtime FROM MultiShiftDatabase.dbo.tb_leave t1 WHERE t2.cardeventdate BETWEEN t1.DDATE AND t1.DDATE1 AND t2.personid = t1.id COLLATE DATABASE_DEFAULT), 'absent') AS workedtime from table t2
Gopal
Something like this.
Quassnoi
Care to explain the downvote?
Quassnoi
A: 
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
najmeddine