How to Display a value for the dates?

Using SQL Server 2005

Database1

Table1

ID Date Status

001 23-02-2009 Worked
001 24-02-2009
001 25-02-2009
001 26-02-2009 Worked
002 24-02-2009 Worked
002 25-02-2009
002 26-02-2009
002 27-02-2009
002 28-02-2009 Worked

...,

Database2

Table2

ID FromDate ToDate Reason

001 24-02-2009 25-02-2009 ShortLeave
002 25-02-2009 27-02-2009 MedicalLeave

...,

Tried Query

SELECT DISTINCT t1.ID, 开发者_开发知识库
t1.Date, 
CASE WHEN t2.ID IS NULL THEN t1.Status ELSE 'Leave' END AS Workedtime      
from Database1.dbo.table1 AS t1 LEFT OUTER JOIN Database2.dbo.table2 AS t2 
ON t1.ID COLLATE DATABASE_DEFAULT = t2.ID 
AND t1.Date BETWEEN t2.FromDATE AND t2.ToDATE

It should display a Reason from table2 Instead of Leave

Expected Output

ID Date Status

001 23-02-2009 Worked
001 24-02-2009 ShortLeave
001 25-02-2009 ShortLeave
001 26-02-2009 Worked
002 24-02-2009 Worked
002 25-02-2009 MedicalLeave
002 26-02-2009 MedicalLeave
002 27-02-2009 MedicalLeave
002 28-02-2009 Worked

...,

So It should display a Reason Instead of Leave where table1.id = table2.id and t1.date between t2.fromdate and t2.todate.

How to modify my query?

Need Query Help

---

Your own query was almost there, you just need to use the Reason column from Table2 when it's available:

SELECT t1.ID, t1.Date, ISNULL(t2.Reason, t1.Status) AS WorkedTime
FROM Table1 AS t1
    LEFT JOIN Table2 AS t2
        ON t1.ID COLLATE DATABASE_DEFAULT = t2.ID
            AND t1.Date BETWEEN t2.FromDate AND t2.ToDate
ORDER BY t1.ID, t1.Date