Crosstab Query Bug

[goodguy]

Checktimes table has the following records:
1, 2010-12-01 08:12:11, I.
2, 2010-12-01 08:33:25, I.
1. 2010-12-01 09:25:45, 0.
2. 2010-12-01 11:15:15, 0.
1. 2010-12-01 14:33:55, 1.
2. 2010-12-01 15:11:22, 1.
2, 2010-12-01 15:35:44, 0.
2. 2010-12-01 16:22:33, 1.
1, 2010-12-01 17:44:01, O.
2, 2010-12-01 18:02:37, O.

CHECKTYPE 'I' and 'O' indicate normal clocking in and out respectively, while '0' and '1' indicate clocking out and back in for field jobs.

I have a crosstab query for generating a report to filter field jobs and the time taken as follows:
Date: 2010-12-01
USERID TIMEOUT TIMEIN DURATION
1 09:25:45 14:33:55 05:08:10
2 11:15:15 15:11:22 03:56:07
2 15:35:44 16:22:33 00:56:49

The query uses joins to retrieve columns from two other tables:
SELECT a.CHECKDATE, DEPARTMENTS.DEPTNAME, a.USERID, USERINFO.Name, Max(a.outTime) AS outTime, b.inTime, Format((b.inTime-outtime),"Short Time") AS Duration
FROM DEPARTMENTS INNER JOIN (((SELECT DateValue(CHECKTIME) AS CHECKDATE, CHECKINOUT.USERID, CHECKINOUT.CHECKTIME AS outTime, CHECKINOUT.CHECKTYPE FROM CHECKINOUT WHERE (((CHECKINOUT.CHECKTYPE)="0"))) AS a INNER JOIN (SELECT DateValue(CHECKTIME) AS CHECKDATE, CHECKINOUT.USERID, CHECKINOUT.CHECKTIME AS inTime, CHECKINOUT.CHECKTYPE FROM CHECKINOUT WHERE (((CHECKINOUT.CHECKTYPE)="1"))) AS b ON (a.USERID=b.USERID) AND (a.CHECKDATE=b.CHECKDATE)) INNER JOIN USERINFO ON a.USERID=USERINFO.USERID) ON DEPARTMENTS.DEPTID=USERINFO.DEFAULTDEPTID
WHERE (((a.outTime)<[b].[intime]))
GROUP BY a.CHECKDATE, DEPARTMENTS.DEPTNAME, a.USERID, USERINFO.Name, b.inTime;

My problem is this:
1. Staff sometimes clock out but do NOT clock back in on return.
2. Staff may forget to clock out but clock in on return from field work.
In such cases, esp where the staff has gone out on two or more field trips in the same day and unmatched records exist, the report will show something like the following (by repeating either TIMEIN or TIMEOUT to match it up with the second record.:
Date: 2010-12-01
USERID TIMEOUT TIMEIN DURATION
1 09:25:45 14:33:55 05:08:10
2 11:15:15 15:11:22 03:56:07
2 11:15:15 16:22:33 00:56:49



1. How can I avoid this?
2. I want a query that I can use to display only unmatched records in a sub-report, i.e. a TIMEIN record without a corresponding TIMEOUT or vice versa. How can I do that? I believe using NOT IN will help but I am too rusty in SQL.

[goodguy]

Weekend00, are you back from holiday?

[weekend00]

I am. I need some time to pick up the posts.

[weekend00]

I think you'd better scan the table to identify the unmatch records.
how would you do with those records? e.g.
1 only I, without O;
2 no I, only O;
3 only 1, without 0;
4 no 1, only 0.

do you want to add the missing records or you just want to put a mark on those orphan records so that you can exclude them from you query?

[goodguy]

OK, let me clarify: Initially, what I need is just the query to obtain the unmatched (orphan) records. I may create a separate report for it, or I may insert it as a subreport under the main report.

[weekend00]

it's not easy to use only a query to obtain the unmatched (orphan) records.
use VBA to scan the table and put a mark at the orphan record will be easier.

[goodguy]

Thanks. How? Please guide.

[goodguy]

Thanks. But how? Can you advise?

[weekend00]

following 4 queries updates checktype to "X*" for orphan entries.

For two "I" entries, update earlier one to "XI"
For two "1" entries, update earlier one to "X1"
For two "O" entries, update later one to "XO"
For two "0" entries, update later one to "X0"

after run these four queries, you can exclude orphan entries from you crosstab query by criteria checktype not like 'X*'

Table: checktimes
Fields: userID, checktime,checktype

Update orphan 0:
UPDATE DISTINCTROW checktimes AS c INNER JOIN (SELECT A.userID, A.CheckTime, A.CheckType, Min(B.CheckTime) AS NEXTCheckTime
FROM CheckTimes AS A INNER JOIN CheckTimes AS B ON A.userID = B.userID
WHERE (((B.CheckTime)>[A].[CHECKTIME]) AND ((B.CheckType)="1" Or (B.CheckType)="0"))
GROUP BY A.userID, A.CheckTime, A.CheckType
HAVING (((A.CheckType)="0"))) AS AB ON (c.userid=ab.userid) AND (c.checktime = ab.nextchecktime) SET c.checktype = "X0"
WHERE c.checktype="0";

Update orphan 1:
UPDATE DISTINCTROW checktimes AS d INNER JOIN (select ab.userid,ab.checktime from checktimes as c inner join
(SELECT A.userID, A.CheckTime, A.CheckType, Min(B.CheckTime) AS NEXTCheckTime
FROM CheckTimes AS A INNER JOIN CheckTimes AS B ON A.userID = B.userID
WHERE (((B.CheckTime)>[A].[CHECKTIME]) AND ((B.CheckType)="1" Or (B.CheckType)="0"))
GROUP BY A.userID, A.CheckTime, A.CheckType
HAVING (((A.CheckType)="1"))) as AB on c.userid=ab.userid and c.checktime = ab.nextchecktime where c.checktype="1") AS abc ON (d.userid=abc.userid) AND (d.checktime=abc.checktime) SET d.checktype = "X1";

Update orphan I:
UPDATE DISTINCTROW checktimes AS d INNER JOIN (select ab.userid,ab.checktime from checktimes as c inner join
(SELECT A.userID, A.CheckTime, A.CheckType, Min(B.CheckTime) AS NEXTCheckTime
FROM CheckTimes AS A INNER JOIN CheckTimes AS B ON A.userID = B.userID
WHERE (((B.CheckTime)>[A].[CHECKTIME]) AND ((B.CheckType)="I" Or (B.CheckType)="O"))
GROUP BY A.userID, A.CheckTime, A.CheckType
HAVING (((A.CheckType)="I"))) as AB on c.userid=ab.userid and c.checktime = ab.nextchecktime where c.checktype="I") AS abc ON (d.checktime=abc.checktime) AND (d.userid=abc.userid) SET d.checktype = "XI";

Update orphan O:
UPDATE DISTINCTROW checktimes AS c INNER JOIN (SELECT A.userID, A.CheckTime, A.CheckType, Min(B.CheckTime) AS NEXTCheckTime
FROM CheckTimes AS A INNER JOIN CheckTimes AS B ON A.userID = B.userID
WHERE (((B.CheckTime)>[A].[CHECKTIME]) AND ((B.CheckType)="I" Or (B.CheckType)="O"))
GROUP BY A.userID, A.CheckTime, A.CheckType
HAVING (((A.CheckType)="O"))) AS AB ON (c.userid=ab.userid) AND (c.checktime = ab.nextchecktime) SET c.checktype = "XO"
WHERE c.checktype="O";

[goodguy]

Weekend00, thank you for your sterling efforts so far, however, I am afraid I am getting wrong results with your query. Since modifying the data in the vendor's app is not an option, I converted your Update query to a Select as below:
SELECT DISTINCTROW C.USERID, C.CHECKTIME, C.CHECKTYPE
FROM CHECKINOUT AS C INNER JOIN (SELECT A.USERID, A.CHECKTIME, A.CHECKTYPE, MIN(B.CHECKTIME) AS NEXTCHECKTIME
FROM CHECKINOUT AS A
INNER JOIN CHECKINOUT AS B
ON A.USERID = B.USERID
WHERE
(
(
(B.CHECKTIME)>[A].[CHECKTIME]
)
AND
(
(B.CHECKTYPE)="1"
Or
(B.CHECKTYPE)="0")
)
GROUP BY A.USERID, A.CHECKTIME,
A.CHECKTYPE HAVING (((A.CHECKTYPE)="0"))) AS AB
ON (C.CHECKTIME = AB.NEXTCHECKTIME) AND (C.USERID = AB.USERID);

and for the records shown below, with orphan records indicated in the last column:

USERID

CHECKTIME

CHECKTYPE

SNO

ORPHAN?

9

02-Dec-10 10:36:31

0

33

Y

35

02-Dec-10 11:13:57

0

35

36

02-Dec-10 12:50:59

1

36

10

02-Dec-10 12:56:21

0

37

36

02-Dec-10 13:20:14

0

39

10

02-Dec-10 13:45:37

1

40

36

02-Dec-10 15:39:34

1

41

18

02-Dec-10 15:39:46

1

42

22

03-Dec-10 09:33:24

0

64

17

03-Dec-10 10:05:00

0

65

17

03-Dec-10 11:05:38

1

66

10

03-Dec-10 12:13:38

0

67

18

03-Dec-10 12:54:46

1

68

36

03-Dec-10 12:55:11

1

69

17

03-Dec-10 13:24:50

0

70

18

03-Dec-10 13:25:23

0

71

10

03-Dec-10 13:50:38

1

72

17

03-Dec-10 14:05:55

1

73

18

03-Dec-10 14:06:04

1

74

35

03-Dec-10 14:06:36

1

75

36

03-Dec-10 15:13:13

1

76

17

03-Dec-10 15:14:39

0

77

17

03-Dec-10 16:06:28

1

79

18

03-Dec-10 16:11:44

0

80

11

03-Dec-10 16:22:04

1

81

7

03-Dec-10 16:24:32

1

82

Y

22

03-Dec-10 16:24:44

1

83

7

04-Dec-10 09:09:06

0

108

Y

11

04-Dec-10 09:10:08

0

109

22

04-Dec-10 11:22:20

0

110

11

04-Dec-10 11:24:43

0

112

17

04-Dec-10 12:32:16

0

114

17

04-Dec-10 12:50:14

1

115

13

06-Dec-10 11:19:32

1

138

11

06-Dec-10 12:09:47

0

139

35

06-Dec-10 13:03:46

0

141

18

06-Dec-10 13:50:00

0

142

17

06-Dec-10 14:42:45

0

144

36

06-Dec-10 14:51:20

1

145

13

06-Dec-10 15:22:58

0

146

11

06-Dec-10 15:27:26

1

147

10

06-Dec-10 15:35:46

1

148

Y

10

06-Dec-10 15:53:41

0

149

Y

17

06-Dec-10 16:10:04

1

151

7

07-Dec-10 08:54:05

0

176

13

07-Dec-10 09:07:47

0

177

11

07-Dec-10 09:08:11

0

178

10

07-Dec-10 10:50:23

0

179

18

07-Dec-10 11:22:35

0

180

34

07-Dec-10 11:22:42

0

181

10

07-Dec-10 12:34:17

1

182

18

07-Dec-10 12:36:05

1

183

18

07-Dec-10 14:05:31

0

185

17

07-Dec-10 14:05:36

0

186

17

07-Dec-10 14:37:47

1

187

17

07-Dec-10 14:47:35

1

188

17

07-Dec-10 15:04:51

0

189

36

07-Dec-10 15:04:56

0

190

18

07-Dec-10 15:30:08

1

191

7

07-Dec-10 15:42:22

1

192

11

07-Dec-10 15:49:41

1

193

10

08-Dec-10 10:01:56

0

223

36

08-Dec-10 10:32:15

0

224

10

08-Dec-10 10:49:31

1

225

10

08-Dec-10 11:29:24

1

226

Y

the query returned the following resultset:

USERID

CHECKTIME

CHECKTYPE

7

07-Dec-10 08:54:05

0

7

07-Dec-10 15:42:22

1

10

02-Dec-10 13:45:37

1

10

03-Dec-10 13:50:38

1

10

07-Dec-10 10:50:23

0

10

07-Dec-10 12:34:17

1

10

08-Dec-10 10:49:31

1

10

08-Dec-10 13:16:51

1

10

08-Dec-10 15:04:30

1

10

10-Dec-10 10:24:41

1

11

04-Dec-10 11:24:43

0

11

06-Dec-10 12:09:47

0

11

06-Dec-10 15:27:26

1

11

07-Dec-10 15:49:41

1

13

07-Dec-10 09:07:47

0

13

08-Dec-10 14:24:23

1

17

03-Dec-10 11:05:38

1

17

03-Dec-10 14:05:55

1

17

03-Dec-10 16:06:28

1

17

04-Dec-10 12:50:14

1

17

06-Dec-10 16:10:04

1

17

07-Dec-10 14:37:47

1

17

08-Dec-10 14:55:18

0

18

03-Dec-10 14:06:04

1

18

06-Dec-10 13:50:00

0

18

07-Dec-10 11:22:35

0

18

07-Dec-10 12:36:05

1

18

07-Dec-10 15:30:08

1

18

10-Dec-10 17:00:22

0

18

13-Dec-10 08:58:16

0

22

03-Dec-10 16:24:44

1

34

08-Dec-10 11:40:21

0

34

08-Dec-10 14:29:54

1

35

03-Dec-10 14:06:36

1

35

10-Dec-10 10:24:16

0

35

10-Dec-10 15:08:36

1

36

02-Dec-10 15:39:34

1

36

08-Dec-10 10:32:15

0

36

11-Dec-10 09:05:51

0

36

11-Dec-10 11:48:12

1

[weekend00]

oh man, your last post is too difficult to look, would you please organize a bit?

[goodguy]

is there a way i can attach a worksheet to my messages?

[weekend00]

I am not sure, you may try the 'table' tag.

[goodguy]

               DEPTNAME   Name   CHECKTIME   CHECKTYPE   ORPHAN       Admin   Sammy   02-Dec-10   10:36:31   0   
      Tech   Ted   02-Dec-10   11:13:57   0   
      Tech   Sidney   02-Dec-10   12:50:59   1   
      Eng   Isaac   02-Dec-10   12:56:21   0   
      Tech   Sidney   02-Dec-10   13:20:14   0   
      Eng   Isaac   02-Dec-10   13:45:37   1   
      Tech   Sidney   02-Dec-10   15:39:34   1   
      CCare   Steve   02-Dec-10   15:39:46   1   
      Tech   Chris   03-Dec-10   09:33:24   0   
      CCare   Sybil   03-Dec-10   10:05:00   0   
      CCare   Sybil   03-Dec-10   11:05:38   1   
      Eng   Isaac   03-Dec-10   12:13:38   0   
      CCare   Steve   03-Dec-10   12:54:46   1   
      Tech   Sidney   03-Dec-10   12:55:11   1   
      CCare   Sybil   03-Dec-10   13:24:50   0   
      CCare   Steve   03-Dec-10   13:25:23   0   
      Eng   Isaac   03-Dec-10   13:50:38   1   
      CCare   Sybil   03-Dec-10   14:05:55   1   
      CCare   Steve   03-Dec-10   14:06:04   1   
      Tech   Ted   03-Dec-10   14:06:36   1   
      Tech   Sidney   03-Dec-10   15:13:13   1   
      CCare   Sybil   03-Dec-10   15:14:39   0   
      CCare   Sybil   03-Dec-10   16:06:28   1   
      CCare   Steve   03-Dec-10   16:11:44   0   
      Eng   Rich   03-Dec-10   16:22:04   1   
      Eng   JOHN   03-Dec-10   16:24:32   1   
      Tech   Chris   03-Dec-10   16:24:44   1   
      Eng   JOHN   04-Dec-10   09:09:06   0   
      Eng   Rich   04-Dec-10   09:10:08   0   
      Tech   Chris   04-Dec-10   11:22:20   0   x       Eng   Rich   04-Dec-10   11:24:43   0   
      CCare   Sybil   04-Dec-10   12:32:16   0   
      CCare   Sybil   04-Dec-10   12:50:14   1   
      Eng   Randy   06-Dec-10   11:19:32   1   
      Eng   Rich   06-Dec-10   12:09:47   0   
      Tech   Ted   06-Dec-10   13:03:46   0   
      CCare   Steve   06-Dec-10   13:50:00   0   
      CCare   Sybil   06-Dec-10   14:42:45   0   
      Tech   Sidney   06-Dec-10   14:51:20   1   
      Eng   Randy   06-Dec-10   15:22:58   0   
      Eng   Rich   06-Dec-10   15:27:26   1   
      Eng   Isaac   06-Dec-10   15:35:46   1   x       Eng   Isaac   06-Dec-10   15:53:41   0   x       CCare   Sybil   06-Dec-10   16:10:04   1   
      Eng   JOHN   07-Dec-10   08:54:05   0   
      Eng   Randy   07-Dec-10   09:07:47   0   
      Eng   Rich   07-Dec-10   09:08:11   0   
      Eng   Isaac   07-Dec-10   10:50:23   0   
      CCare   Steve   07-Dec-10   11:22:35   0   
      Tech   NICO   07-Dec-10   11:22:42   0   
      Eng   Isaac   07-Dec-10   12:34:17   1   
      CCare   Steve   07-Dec-10   12:36:05   1   
      CCare   Steve   07-Dec-10   14:05:31   0   
      CCare   Sybil   07-Dec-10   14:05:36   0   
      CCare   Sybil   07-Dec-10   14:37:47   1   
      CCare   Sybil   07-Dec-10   14:47:35   1   
      CCare   Sybil   07-Dec-10   15:04:51   0   
      Tech   Sidney   07-Dec-10   15:04:56   0   
      CCare   Steve   07-Dec-10   15:30:08   1   
      Eng   JOHN   07-Dec-10   15:42:22   1   
      Eng   Rich   07-Dec-10   15:49:41   1   
      Eng   Isaac   08-Dec-10   10:01:56   0   
      Tech   Sidney   08-Dec-10   10:32:15   0   
      Eng   Isaac   08-Dec-10   10:49:31   1   
      Eng   Isaac   08-Dec-10   11:29:24   1   x       Tech   NICO   08-Dec-10   11:40:21   0   
      Eng   Isaac   08-Dec-10   12:08:21   0   
      Eng   Isaac   08-Dec-10   13:16:51   1   
      Eng   Isaac   08-Dec-10   13:53:50   0   
      Eng   Randy   08-Dec-10   14:24:23   1   
      Tech   NICO   08-Dec-10   14:29:54   1   
      CCare   Sybil   08-Dec-10   14:55:18   0   
      Eng   Isaac   08-Dec-10   15:04:30   1   
      Eng   Isaac   10-Dec-10   09:52:26   0   
      Tech   Ted   10-Dec-10   10:24:16   0   
      Eng   Isaac   10-Dec-10   10:24:41   1   
      CCare   Steve   10-Dec-10   13:29:50   0   
      Tech   NICO   10-Dec-10   15:08:20   1   
      Tech   Ted   10-Dec-10   15:08:36   1   
      CCare   Steve   10-Dec-10   17:00:22   0   
      Tech   Sidney   11-Dec-10   09:05:51   0   
      Eng   Randy   11-Dec-10   11:31:26   0   
      Tech   Sidney   11-Dec-10   11:48:12   1   
      CCare   Steve   13-Dec-10   08:58:16   0   
      Tech   Sidney   13-Dec-10   09:00:06   0   
      CCare   Steve   13-Dec-10   10:16:04   1   
      Eng   JOHN   13-Dec-10   10:21:12   0   
      Tech   Ted   13-Dec-10   10:36:13   1   
      Tech   Sidney   13-Dec-10   10:36:21   0   
      Eng   Isaac   13-Dec-10   12:00:34   0   
      Eng   Isaac   13-Dec-10   13:52:23   1   
      CCare   Sybil   13-Dec-10   14:01:19   0   
      CCare   Sybil   13-Dec-10   14:45:21   1   
      CCare   Sybil   13-Dec-10   15:16:21   0   
      Tech   Chris   13-Dec-10   16:10:59   1   x       Eng   Randy   13-Dec-10   16:12:49   1   
      Eng   JOHN   13-Dec-10   17:06:32   1   
      CCare   Steve   14-Dec-10   09:10:39   0   
      Tech   NICO   14-Dec-10   09:10:57   0   
      Tech   NICO   14-Dec-10   10:33:38   1   
      CCare   Steve   14-Dec-10   10:34:47   1   
      Tech   Chris   14-Dec-10   12:04:05   1   x       Eng   Randy   14-Dec-10   12:04:20   0   
      Eng   Rich   14-Dec-10   12:30:55   0   
      CCare   Sybil   14-Dec-10   12:59:50   0   
      CCare   Sybil   14-Dec-10   14:00:41   1   
      CCare   Sybil   14-Dec-10   15:12:22   0   
      Tech   NICO   15-Dec-10   09:10:12   0   
      Tech   Sidney   15-Dec-10   09:24:55   0   
      Eng   Rich   15-Dec-10   09:35:39   0   
      Tech   Sidney   15-Dec-10   09:59:30   1   
      CCare   Sybil   15-Dec-10   10:39:57   0   
      Tech   NICO   15-Dec-10   11:36:51   1   
      CCare   Steve   15-Dec-10   11:37:07   1   
      Tech   Ted   15-Dec-10   11:53:31   0   
      Tech   Sidney   15-Dec-10   13:30:48   1   
      Eng   Randy   15-Dec-10   14:47:26   1   
      Tech   Chris   15-Dec-10   14:48:34   1   x       Eng   Randy   16-Dec-10   09:06:22   0   
      Eng   Isaac   16-Dec-10   10:33:39   0   
      Tech   Chris   16-Dec-10   10:53:41   1   x       Eng   Isaac   16-Dec-10   11:47:08   1   
      Tech   Sidney   16-Dec-10   12:43:55   0   
      Tech   Ted   16-Dec-10   13:07:00   1   
      Tech   Sidney   16-Dec-10   13:22:03   0   
      Tech   Sidney   16-Dec-10   14:34:51   1   
      Eng   Rich   17-Dec-10   08:19:36   0   
      Eng   Randy   17-Dec-10   09:24:35   0   
      Tech   NICO   17-Dec-10   09:27:17   0   
      CCare   Sybil   17-Dec-10   11:53:24   0   
      Tech   Sidney   17-Dec-10   12:43:44   0   
      Tech   NICO   17-Dec-10   16:48:33   1   
      Eng   Rich   17-Dec-10   17:11:49   0   
      Eng   Randy   18-Dec-10   09:17:20   0   
      Tech   Sidney   18-Dec-10   09:17:25   0   
      Eng   Randy   18-Dec-10   09:55:23   1   
      CCare   Steve   18-Dec-10   10:02:40   0   
      Tech   Sidney   18-Dec-10   10:09:51   1   
      Eng   JOHN   18-Dec-10   10:12:31   0   
      Tech   Chris   18-Dec-10   10:12:34   0   
      Tech   NICO   18-Dec-10   10:16:11   0   
      Tech   Sidney   18-Dec-10   10:16:27   0   
      Eng   Randy   18-Dec-10   10:24:47   0   
      Eng   Rich   18-Dec-10   10:25:05   0   
      Tech   Chris   18-Dec-10   10:28:24   1   
      Tech   Sidney   18-Dec-10   10:54:50   1   
      Tech   NICO   18-Dec-10   11:02:04   1   
      Eng   Isaac   18-Dec-10   11:16:59   0   
      Tech   Ted   18-Dec-10   11:39:53   0   
      Eng   Randy   18-Dec-10   12:50:31   1   
      Tech   Sidney   18-Dec-10   12:56:27   1   
      Eng   Randy   18-Dec-10   12:58:32   0   
      Tech   Chris   20-Dec-10   09:09:01   0   
      Eng   Rich   20-Dec-10   09:44:55   0   
      Eng   Randy   20-Dec-10   09:45:05   0   
      Tech   NICO   20-Dec-10   10:35:11   0   
      Eng   JOHN   20-Dec-10   11:00:21   0   
      CCare   Steve   20-Dec-10   11:06:02   0   
      CCare   Sybil   20-Dec-10   11:06:09   0   
      Tech   NICO   20-Dec-10   11:27:28   1   
      CCare   Steve   20-Dec-10   12:03:48   1   
      Tech   Chris   20-Dec-10   12:12:50   1   
      Eng   Isaac   20-Dec-10   12:23:28   0   
      Tech   BURHAN   20-Dec-10   12:55:24   0   x       CCare   Steve   20-Dec-10   13:05:41   0   
      CCare   Sybil   20-Dec-10   13:05:45   0   
      Eng   Isaac   20-Dec-10   13:34:07   1   
      Tech   NICO   20-Dec-10   14:11:13   0   
      Tech   NICO   20-Dec-10   15:29:32   1   
      Eng   JOHN   20-Dec-10   15:31:42   1   
      Eng   Isaac   20-Dec-10   15:53:45   0   
      CCare   Sybil   20-Dec-10   16:34:12   1   
      Eng   Isaac   20-Dec-10   17:01:36   1   
      Tech   NICO   21-Dec-10   08:22:58   0   
      Eng   JOHN   21-Dec-10   08:54:36   0   
      Tech   Sidney   21-Dec-10   09:14:08   0   
      Eng   Randy   21-Dec-10   09:23:23   0   
      CCare   Sybil   21-Dec-10   09:28:49   0   
      CCare   Sybil   21-Dec-10   09:46:08   1   
      Eng   Isaac   21-Dec-10   09:46:40   0   
      Eng   JOHN   21-Dec-10   09:56:01   1   
      Tech   NICO   21-Dec-10   10:09:39   1   
      Eng   Isaac   21-Dec-10   10:21:13   0   
      Tech   Ted   21-Dec-10   10:23:11   1   
      CCare   Steve   21-Dec-10   11:09:22   0   
      CCare   Sybil   21-Dec-10   12:22:21   0   
      CCare   Steve   21-Dec-10   13:15:00   1   
      Tech   NICO   21-Dec-10   13:42:54   0   
      Eng   Isaac   21-Dec-10   14:55:00   1   
      Tech   NICO   21-Dec-10   15:00:50   1   
      CCare   Sybil   21-Dec-10   15:22:17   0   
      CCare   Steve   21-Dec-10   15:56:24   1   
      Eng   Isaac   22-Dec-10   08:16:13   0   
      Tech   NICO   22-Dec-10   08:38:27   0   
      Tech   NICO   22-Dec-10   08:51:15   1   
      CCare   Sybil   22-Dec-10   09:04:11   0   
      Tech   Sidney   22-Dec-10   09:06:43   0   
      Eng   Isaac   22-Dec-10   09:16:57   1   
      Tech   Sidney   22-Dec-10   09:45:25   1   
      CCare   Sybil   22-Dec-10   09:51:30   1   
      Eng   Isaac   22-Dec-10   10:28:03   0   
      CCare   Steve   22-Dec-10   10:28:58   0   
      CCare   Sybil   22-Dec-10   10:51:21   1   
      Tech   NICO   22-Dec-10   11:01:53   0   
      CCare   Steve   22-Dec-10   11:47:10   1   
      CCare   Sybil   22-Dec-10   12:11:54   0   
      Tech   Sidney   22-Dec-10   12:30:22   0   
      Tech   BURHAN   22-Dec-10   13:21:35   0   
      Tech   Sidney   22-Dec-10   13:44:55   1   
      CCare   Sybil   22-Dec-10   13:49:13   1   
      Eng   Isaac   22-Dec-10   14:23:26   0   
      Tech   BURHAN   22-Dec-10   15:02:24   1   
      Tech   Chris   22-Dec-10   15:20:44   1   x       CCare   Steve   22-Dec-10   15:25:28   1   
      Tech   NICO   22-Dec-10   15:36:45   1   
      Eng   Isaac   22-Dec-10   15:48:48   1   
      Eng   Randy   22-Dec-10   17:58:02   1   
      Eng   Randy   23-Dec-10   08:02:58   0   
      Tech   NICO   23-Dec-10   09:01:55   0   
      Eng   JOHN   23-Dec-10   09:07:34   0   
      Tech   Chris   23-Dec-10   09:07:45   0   
      Eng   Isaac   23-Dec-10   09:18:47   0   
      Tech   NICO   23-Dec-10   09:20:18   1   
      Tech   Sidney   23-Dec-10   09:47:23   0   
      CCare   Steve   23-Dec-10   09:52:27   0   
      Eng   Isaac   23-Dec-10   10:19:32   1   
      CCare   Sybil   23-Dec-10   10:55:42   0   
      CCare   Steve   23-Dec-10   11:13:37   1   
      CCare   Steve   23-Dec-10   11:22:23   0   
      CCare   Sybil   23-Dec-10   11:24:27   1   
      Tech   Sidney   23-Dec-10   11:25:13   1   
      Tech   NICO   23-Dec-10   11:29:19   0   
      Tech   NICO   23-Dec-10   11:38:34   1   
      Tech   Chris   23-Dec-10   11:42:37   1   
      Tech   Ted   23-Dec-10   11:46:42   0   
      Eng   JOHN   23-Dec-10   12:13:10   1   
      CCare   Steve   23-Dec-10   12:16:14   1   
      Tech   NICO   23-Dec-10   12:49:42   0   
      Tech   Ted   23-Dec-10   13:04:05   1   
      CCare   Sybil   23-Dec-10   13:17:38   0   
      Tech   NICO   23-Dec-10   13:23:30   1   
      Tech   BURHAN   23-Dec-10   13:24:59   0   
      Tech   Sidney   23-Dec-10   14:08:03   0   
      Tech   BURHAN   23-Dec-10   14:22:53   1   
      CCare   Steve   23-Dec-10   14:58:10   0   
      Tech   Ted   23-Dec-10   15:07:15   1   
      Tech   Chris   23-Dec-10   15:29:53   1   x       Tech   Sidney   23-Dec-10   15:34:37   0   
      Eng   JOHN   23-Dec-10   15:38:01   1   
      Tech   Sidney   23-Dec-10   16:23:43   1   
      Tech   NICO   23-Dec-10   16:51:25   0   
      Tech   Ted   23-Dec-10   16:51:39   0   
      Tech   NICO   23-Dec-10   17:42:25   1   
      Tech   Ted   23-Dec-10   17:45:48   1   
      Eng   Rich   24-Dec-10   08:28:40   0   
      Eng   Randy   24-Dec-10   08:28:49   0   
      Tech   Ted   24-Dec-10   08:59:06   0   
      Tech   Sidney   24-Dec-10   09:34:56   1   
      Tech   Sidney   24-Dec-10   10:25:37   1   
      Tech   NICO   24-Dec-10   10:29:32   0   
      Tech   NICO   24-Dec-10   11:19:44   1   
      Eng   Isaac   24-Dec-10   11:29:34   0   
      Eng   Randy   24-Dec-10   11:50:20   1   
      Tech   NICO   24-Dec-10   12:08:07   0   
      Tech   Sidney   24-Dec-10   12:29:23   1   
      Tech   Sidney   24-Dec-10   12:58:32   0   
      Tech   Ted   24-Dec-10   13:27:48   1   
      Tech   BURHAN   24-Dec-10   14:15:21   0   
      Tech   Sidney   24-Dec-10   14:19:03   0   
      Eng   Isaac   24-Dec-10   14:21:01   1   
      Tech   BURHAN   24-Dec-10   14:58:35   1   
      Tech   Sidney   24-Dec-10   15:20:26   1   
      Eng   Isaac   24-Dec-10   15:53:55   0   
      Tech   Sidney   24-Dec-10   16:03:54   0   
      CCare   Sybil   24-Dec-10   16:26:12   1   
      Tech   Sidney   24-Dec-10   16:44:56   1   
      Eng   Isaac   24-Dec-10   16:55:24   1   
      Eng   Randy   24-Dec-10   16:57:40   1   
      CCare   Steve   27-Dec-10   08:12:59   0   
      Tech   Sidney   27-Dec-10   08:14:00   0   
      Eng   JOHN   27-Dec-10   09:06:07   0   
      Eng   JOHN   27-Dec-10   09:13:46   1   
      CCare   Sybil   27-Dec-10   09:46:50   0   
      Tech   Sidney   27-Dec-10   10:25:02   1   
      Tech   Sidney   27-Dec-10   10:56:13   1   
      Eng   JOHN   27-Dec-10   10:56:54   0   
      CCare   Sybil   27-Dec-10   11:07:15   1   
      CCare   Steve   27-Dec-10   11:17:30   1   
      Eng   Isaac   27-Dec-10   11:27:25   1   
      CCare   Sybil   27-Dec-10   11:44:11   0   
      Tech   NICO   27-Dec-10   11:51:59   0   
      Tech   Sidney   27-Dec-10   11:52:03   0   
      CCare   Sybil   27-Dec-10   12:12:12   1   
      Tech   NICO   27-Dec-10   12:50:40   1   
      Tech   Sidney   27-Dec-10   12:51:20   1   
      Eng   JOHN   27-Dec-10   13:00:02   1   
      Tech   NICO   27-Dec-10   13:20:58   0   
      CCare   Sybil   27-Dec-10   13:35:51   0   
      CCare   Steve   27-Dec-10   13:36:06   0   
      Tech   BURHAN   27-Dec-10   14:13:34   0   
      CCare   Sybil   27-Dec-10   14:42:16   1   
      CCare   Steve   27-Dec-10   14:42:25   1   
      Tech   Sidney   27-Dec-10   14:49:59   0   
      CCare   Sybil   27-Dec-10   15:07:36   1   
      Eng   Isaac   27-Dec-10   15:08:46   0   
      Tech   BURHAN   27-Dec-10   15:09:37   1   
      Eng   Isaac   27-Dec-10   15:41:33   1   
      Tech   Sidney   27-Dec-10   15:47:59   1   
      Eng   JOHN   27-Dec-10   15:56:43   1   
      Eng   Rich   27-Dec-10   15:57:03   1   
      Tech   Sidney   27-Dec-10   16:20:08   0   
      Tech   Chris   28-Dec-10   08:39:16   0   x       Eng   Rich   28-Dec-10   08:39:21   0   
      Tech   Sidney   28-Dec-10   09:11:09   0   
      Eng   Isaac   28-Dec-10   09:12:02   0   
      Eng   Isaac   28-Dec-10   09:33:54   1

               Dept   Name   Date   Out   In   Time       CCare   Sybil   03-Dec-10   10:05   11:05   01:00       CCare   Sybil   03-Dec-10   13:24   14:05   00:41       CCare   Sybil   03-Dec-10   15:14   16:06   00:51       CCare   Sybil   04-Dec-10   12:32   12:50   00:17       CCare   Sybil   06-Dec-10   14:42   16:10   01:27       CCare   Sybil   07-Dec-10   14:05   14:47   00:41       CCare   Sybil   07-Dec-10   14:05   14:37   00:32       CCare   Sybil   13-Dec-10   14:01   14:45   00:44       CCare   Sybil   14-Dec-10   12:59   14:00   01:00       CCare   Sybil   20-Dec-10   13:05   16:34   03:28       CCare   Sybil   21-Dec-10   09:28   09:46   00:17       CCare   Sybil   22-Dec-10   09:04   10:51   01:47       CCare   Sybil   22-Dec-10   09:04   09:51   00:47       CCare   Sybil   22-Dec-10   12:11   13:49   01:37       CCare   Sybil   23-Dec-10   10:55   11:24   00:28       CCare   Sybil   27-Dec-10   13:35   15:07   01:31       CCare   Sybil   27-Dec-10   13:35   14:42   01:06       CCare   Sybil   27-Dec-10   11:44   12:12   00:28       CCare   Sybil   27-Dec-10   09:46   11:07   01:20       CCare   Steve   03-Dec-10   13:25   14:06   00:40       CCare   Steve   07-Dec-10   11:22   12:36   01:13       CCare   Steve   07-Dec-10   14:05   15:30   01:24       CCare   Steve   13-Dec-10   08:58   10:16   01:17       CCare   Steve   14-Dec-10   09:10   10:34   01:24       CCare   Steve   20-Dec-10   11:06   12:03   00:57       CCare   Steve   21-Dec-10   11:09   13:15   02:05       CCare   Steve   21-Dec-10   11:09   15:56   04:47       CCare   Steve   22-Dec-10   10:28   11:47   01:18       CCare   Steve   22-Dec-10   10:28   15:25   04:56       CCare   Steve   23-Dec-10   11:22   12:16   00:53       CCare   Steve   23-Dec-10   09:52   11:13   01:21       CCare   Steve   27-Dec-10   13:36   14:42   01:06       CCare   Steve   27-Dec-10   08:12   11:17   03:04       Eng   Isaac   02-Dec-10   12:56   13:45   00:49       Eng   Isaac   03-Dec-10   12:13   13:50   01:37       Eng   Isaac   07-Dec-10   10:50   12:34   01:43       Eng   Isaac   08-Dec-10   12:08   13:16   01:08       Eng   Isaac   08-Dec-10   10:01   10:49   00:47       Eng   Isaac   08-Dec-10   10:01   11:29   01:27       Eng   Isaac   08-Dec-10   13:53   15:04   01:10       Eng   Isaac   10-Dec-10   09:52   10:24   00:32       Eng   Isaac   13-Dec-10   12:00   13:52   01:51       Eng   Isaac   16-Dec-10   10:33   11:47   01:13       Eng   Isaac   20-Dec-10   12:23   13:34   01:10       Eng   Isaac   20-Dec-10   15:53   17:01   01:07       Eng   Isaac   21-Dec-10   10:21   14:55   04:33       Eng   Isaac   22-Dec-10   14:23   15:48   01:25       Eng   Isaac   22-Dec-10   08:16   09:16   01:00       Eng   Isaac   23-Dec-10   09:18   10:19   01:00       Eng   Isaac   24-Dec-10   11:29   14:21   02:51       Eng   Isaac   24-Dec-10   15:53   16:55   01:01       Eng   Isaac   27-Dec-10   15:08   15:41   00:32       Eng   Isaac   28-Dec-10   09:12   09:33   00:21       Eng   JOHN   07-Dec-10   08:54   15:42   06:48       Eng   JOHN   13-Dec-10   10:21   17:06   06:45       Eng   JOHN   20-Dec-10   11:00   15:31   04:31       Eng   JOHN   21-Dec-10   08:54   09:56   01:01       Eng   JOHN   23-Dec-10   09:07   12:13   03:05       Eng   JOHN   23-Dec-10   09:07   15:38   06:30       Eng   JOHN   27-Dec-10   09:06   09:13   00:07       Eng   JOHN   27-Dec-10   10:56   13:00   02:03       Eng   JOHN   27-Dec-10   10:56   15:56   04:59       Eng   Randy   18-Dec-10   09:17   09:55   00:38       Eng   Randy   18-Dec-10   10:24   12:50   02:25       Eng   Randy   24-Dec-10   08:28   11:50   03:21       Eng   Randy   24-Dec-10   08:28   16:57   08:28       Eng   Rich   06-Dec-10   12:09   15:27   03:17       Eng   Rich   07-Dec-10   09:08   15:49   06:41       Tech   Ted   10-Dec-10   10:24   15:08   04:44       Tech   Ted   23-Dec-10   11:46   15:07   03:20       Tech   Ted   23-Dec-10   11:46   13:04   01:17       Tech   Ted   23-Dec-10   16:51   17:45   00:54       Tech   Ted   24-Dec-10   08:59   13:27   04:28       Tech   BURHAN   22-Dec-10   13:21   15:02   01:40       Tech   BURHAN   23-Dec-10   13:24   14:22   00:57       Tech   BURHAN   24-Dec-10   14:15   14:58   00:43       Tech   BURHAN   27-Dec-10   14:13   15:09   00:56       Tech   Chris   03-Dec-10   09:33   16:24   06:51       Tech   Chris   18-Dec-10   10:12   10:28   00:15       Tech   Chris   20-Dec-10   09:09   12:12   03:03       Tech   Chris   23-Dec-10   09:07   15:29   06:22       Tech   Chris   23-Dec-10   09:07   11:42   02:34       Tech   NICO   08-Dec-10   11:40   14:29   02:49       Tech   NICO   14-Dec-10   09:10   10:33   01:22       Tech   NICO   15-Dec-10   09:10   11:36   02:26       Tech   NICO   17-Dec-10   09:27   16:48   07:21       Tech   NICO   18-Dec-10   10:16   11:02   00:45       Tech   NICO   20-Dec-10   10:35   11:27   00:52       Tech   NICO   20-Dec-10   14:11   15:29   01:18       Tech   NICO   21-Dec-10   13:42   15:00   01:17       Tech   NICO   21-Dec-10   08:22   10:09   01:46       Tech   NICO   22-Dec-10   11:01   15:36   04:34       Tech   NICO   22-Dec-10   08:38   08:51   00:12       Tech   NICO   23-Dec-10   12:49   13:23   00:33       Tech   NICO   23-Dec-10   11:29   11:38   00:09       Tech   NICO   23-Dec-10   09:01   09:20   00:18       Tech   NICO   23-Dec-10   16:51   17:42   00:51       Tech   NICO   24-Dec-10   10:29   11:19   00:50       Tech   NICO   27-Dec-10   11:51   12:50   00:58       Tech   Sidney   02-Dec-10   13:20   15:39   02:19       Tech   Sidney   11-Dec-10   09:05   11:48   02:42       Tech   Sidney   15-Dec-10   09:24   09:59   00:34       Tech   Sidney   15-Dec-10   09:24   13:30   04:05       Tech   Sidney   16-Dec-10   13:22   14:34   01:12       Tech   Sidney   18-Dec-10   10:16   12:56   02:40       Tech   Sidney   18-Dec-10   09:17   10:09   00:52       Tech   Sidney   18-Dec-10   10:16   10:54   00:38       Tech   Sidney   22-Dec-10   12:30   13:44   01:14       Tech   Sidney   22-Dec-10   09:06   09:45   00:38       Tech   Sidney   23-Dec-10   09:47   11:25   01:37       Tech   Sidney   23-Dec-10   15:34   16:23   00:49       Tech   Sidney   24-Dec-10   16:03   16:44   00:41       Tech   Sidney   24-Dec-10   14:19   15:20   01:01       Tech   Sidney   27-Dec-10   08:14   10:25   02:11       Tech   Sidney   27-Dec-10   08:14   10:56   02:42       Tech   Sidney   27-Dec-10   11:52   12:51   00:59       Tech   Sidney   27-Dec-10   14:49   15:47   00:58

[weekend00]

I copied your data in a table and run my queries, they looked working well, except those with sign in date different from sign out date, you may want them to be orphan but my queries don't mark them because I am not sure if you want them to be orphan or valid pair sign in/out.