Using SQL Server 2000
I want to get Table2.TimeIn Table2.TimeOut according to Table1.personid and also If Table1.Date = Table3.Date then it should take a Table3.TimeIn, Table3.TimeOut.
3 Tables
Table1
ID Date
001 20090503
001 20090504
001 20090506
002 20090505
002 20090506
So on…,
Table2
ID TimeIn TimeOut
001 08:00:00 18:00:00
002 08:00:00 21:00:00
So on…,
Table3
ID Date TimeIn TimeOut
001 20090504 10:00:00 22:00:00
001 20090505 17:00:00 23:00:00
002 20090505 12:00:00 21:00:00
So on…,
Select Table1.ID,
Table1.Date,
Table2.TimeIn,
Table2.TimeOut
from Table1
Inner Join Table2 on Table1.ID = Table2.ID
If Table1.Date = Table3.Date then it should take Table3.TimeIn, Table3.TimeOut else Table2.TimeIn, Table2.Timeout
Expected Output
ID Date TimeIn TimeOut
001 20090503 08:00:00 18:00:00
001 20090504 10:00:00 22:00:00
001 20090506 08:00:00 18:00:00
002 20090505 12:00:00 21:00:00
002 20090506 08:00:00 21:00:00
So on…,
How to write a query for this condition?