SQL Full Outer Join
Posted
by
Torment March
on Stack Overflow
See other posts from Stack Overflow
or by Torment March
Published on 2011-11-26T01:36:35Z
Indexed on
2011/11/26
1:50 UTC
Read the original article
Hit count: 147
I have a table named 'Logs' with the following values :
CheckDate CheckType CheckTime
-------------------------------------------
2011-11-25 IN 14:40:00
2011-11-25 OUT 14:45:00
2011-11-25 IN 14:50:00
2011-11-25 OUT 14:55:00
2011-11-25 IN 15:00:00
2011-11-25 OUT 15:05:00
2011-11-25 IN 15:15:00
2011-11-25 OUT 15:20:00
2011-11-25 IN 15:25:00
2011-11-25 OUT 15:30:00
2011-11-25 OUT 15:40:00
2011-11-25 IN 15:45:00
I want to use the previous table to produce a result of:
CheckDate CheckIn CheckOut
-----------------------------------------
2011-11-25 14:40:00 14:45:00
2011-11-25 14:50:00 14:55:00
2011-11-25 15:00:00 15:05:00
2011-11-25 15:15:00 15:20:00
2011-11-25 15:25:00 15:30:00
2011-11-25 NULL 15:40:00
2011-11-25 15:45:00 NULL
So far I have come up with this result set :
CheckDate CheckIn CheckOut
-----------------------------------------
2011-11-25 14:40:00 14:45:00
2011-11-25 14:50:00 14:55:00
2011-11-25 15:00:00 15:05:00
2011-11-25 15:15:00 15:20:00
2011-11-25 15:25:00 15:30:00
2011-11-25 15:45:00 NULL
The problem is I cannot generate the log without CheckIns :
CheckDate CheckIn CheckOut
-----------------------------------------
2011-11-25 NULL 15:40:00
The sequence of CheckIn - CheckOut pairing and order is in increasing time value.
© Stack Overflow or respective owner
