Date comparing between two dates?

Question

SQL Server 2000

Table1

ID Date Workedtime

001 23-02-2009 08:00:00
001 24-02-2009 15:00:00
001 25-02-2009 17:00:00
002 ...

So on..,

Table2

ID FromDate ToDate

001 24-02-2009 25-02-2009
002 27-02-2009 03-03-2009

...

I want to compare the table1 date and ID with Table2 ID FromDate, ToDate, means Table1 Date compare with FromDate ToDate, if Date is comes in between fromdate and todate, the worktime should display absent.

Expected Output

ID Date Workedtime

001 23-02-2009 08:00:00
001 24-02-2009 absent
001 25-02-2009 absent
002 ...

Need Query Help

Answer 1

This should help from what you have specified above

DECLARE @Table1 TABLE(
     ID VARCHAR(10),
     Date DATETIME,
     Workedtime VARCHAR(10)
)

INSERT INTO @Table1 (ID,Date,Workedtime) SELECT '001', '23 Feb 2009', '08:00:00'
INSERT INTO @Table1 (ID,Date,Workedtime) SELECT '001', '24 Feb 2009', '15:00:00'
INSERT INTO @Table1 (ID,Date,Workedtime) SELECT '001', '25 Feb 2009', '17:00:00'

DECLARE @Table2 TABLE(
     ID VARCHAR(10),
     FromDate DATETIME,
     ToDate DATETIME
)

INSERT INTO @Table2 (ID,FromDate,ToDate) SELECT '001', '24 Feb 2009', ' 25 Feb 2009'

SELECT  t1.ID,
     t1.Date,
     CASE WHEN t2.ID IS NULL THEN t1.Workedtime ELSE 'absent' END Workedtime
FROM    @Table1 t1 LEFT JOIN
     @Table2 t2 ON t1.ID = t2.ID
        AND t1.Date BETWEEN t2.FromDate AND t2.ToDate