Menu
I have two derived tables named Check Ins and Check Outs
Check Ins
CheckDate CheckIn
---------- ---------
08/02/2011 10:10:03
08/02/2011 15:57:16
07/19/2011 13:58:52
07/19/2011 16:50:55
07/26/2011 15:11:24
06/21/2011 12:36:47
08/16/2011 14:49:36
08/09/2011 13:52:10
08/09/2011 16:54:51
08/23/2011 15:48:58
09/06/2011 15:23:00
09/13/2011 10:09:27
09/13/2011 10:40:14
09/13/2011 11:43:14
09/13/2011 11:59:32
09/13/2011 17:05:24
09/20/2011 11:03:42
09/20/2011 12:08:50
09/20/2011 15:21:06
09/20/2011 15:34:29
09/27/2011 11:34:06
10/04/2011 11:37:59
10/04/2011 15:24:04
10/04/2011 16:57:44
10/11/2011 18:19:33
Check Outs
CheckDate CheckOut
---------- ---------
08/02/2011 13:29:40
08/02/2011 17:02:25
07/12/2011 17:06:06
07/19/2011 16:40:15
07/19/2011 17:07:35
07/26/2011 14:48:10
07/26/2011 17:27:08
05/31/2011 17:01:39
06/07/2011 17:04:29
06/14/2011 17:08:50
06/21/2011 17:03:46
06/28/2011 17:10:45
07/05/2011 17:02:48
08/16/2011 13:37:36
08/16/2011 17:06:34
08/09/2011 12:00:29
08/09/2011 13:29:36
08/09/2011 14:36:09
08/09/2011 17:00:38
08/23/2011 13:37:11
08/23/2011 17:01:37
09/06/2011 17:00:09
09/13/2011 10:11:50
09/13/2011 11:22:02
09/13/2011 11:47:35
09/13/2011 14:13:36
09/13/2011 14:14:25
09/13/2011 17:08:43
09/20/2011 09:54:55
09/20/2011 11:55:31
09/20/2011 11:55:36
09/20/2011 13:35:16
09/20/2011 15:26:02
09/20/2011 16:33:21
09/20/2011 17:07:52
09/27/2011 11:12:38
10/04/2011 13:26:31
10/04/2011 16:32:56
10/04/2011 17:02:35
10/11/2011 18:25:32
I want to pair the check ins with the check outs given the condition that both logs are on the same date and check out values cannot be earlier than check ins, so I have come up with this query
SELECT A.ChkDt
AS CheckDate,
B.CheckIn,
MIN(A.ChkTm)
AS CheckOut
FROM #tempAttLogs
AS A LEFT JOIN
(SELECT ChkDt
AS CheckDate,
MIN(ChkTm)
AS CheckIn
FROM #tempAttLogs
WHERE ChkTp = 'I'
GROUP BY ChkDt) B
ON A.ChkDt = B.CheckDate
WHERE ChkTp = 'O' AND
A.ChkTm > B.CheckIn
GROUP BY A.ChkDt, B.CheckIn
With a result set of
CheckDate CheckIn CheckOut
---------- --------- ---------
06/21/2011 12:36:47 17:03:46
07/19/2011 13:58:52 16:40:15
07/26/2011 15:11:24 17:27:08
08/02/2011 10:10:03 13:29:40
08/09/2011 13:52:10 14:36:09
08/16/2011 14:49:36 17:06:34
08/23/2011 15:48:58 17:01:37
09/06/2011 15:23:00 17:00:09
09/13/2011 10:09:27 10:11:50
09/20/2011 11:03:42 11:55:31
10/04/2011 11:37:59 13:26:31
10/11/2011 18:19:33 18:25:32
The thing is I also want to include the logs with no corresponding pairs. How can this be possible?
EDIT
My expected result set must include logs like this
CheckDate CheckIn CheckOut
---------- --------- ---------
05/23/2011 NULL 17:04:27
458
LEFT JOINs can return multiple matches on a single key value, depending on your requirements this may or may not be desirable. In Oracle, you can use the analytical function ROW_NUMBER to choose a single value when multiple matches on a key occur.
Here's a better formulation without changing anything. It moves the LEFT JOINs into the SELECT part and avoids the GROUP BY . Show activity on this post. Note that in your SELECT clause you should either use values that are grouped by (or functionally dependent on them), or use some aggregation.
Yes, indeed! You can use multiple LEFT JOINs in one query if needed for your analysis.
Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).
You can move any predicate referencing the right hand table into the ON clause instead of the WHERE to avoid turning the JOIN into an INNER JOIN
e.g.
ON A.ChkDt = B.CheckDate AND
A.ChkTm > B.CheckIn
WHERE ChkTp = 'O'
Instead of
ON A.ChkDt = B.CheckDate
WHERE ChkTp = 'O' AND
A.ChkTm > B.CheckIn
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
