January 24, 2011 at 9:01 am
I have a table called Checktimes which has the following records:
UserID CheckTime CheckType
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
Checktypes 'I' and 'O' indicate normal clocking in and out respectively.
Checktypes '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:
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 (((SELE CT 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)<.[intime]))
GROUP BY a.CHECKDATE, DEPARTMENTS.DEPTNAME, a.USERID, USERINFO.Name, b.inTime;
The query uses joins to retrieve columns from two other tables:
Date: 2010-12-01
DEPT USER TIMEOUT TIMEIN DURATION
SALES JOHN 09:25:45 14:33:55 05:08:10
ADMIN MARY 11:15:15 15:11:22 03:56:07
ADMIN MARY 15:35:44 16:22:33 00:56:49
My problem is, not all records for field jobs have corresponding contra entries. Staff sometimes leave on field jobs without clocking out but clock in when they return and vice versa.
This is what happens when I have such orphan records:
DEPTNAME Name CHECKTYPE DATE CHECKTIME
Dept 1 BILL 0 23-12-2010 11:46:42 //Out on Job 1.
Dept 1 BILL 1 23-12-2010 13:04:05 //In from Job 1.
Dept 1 BILL 1 23-12-2010 15:07:15 //In from Job 2 (no matching out record).
Dept 1 BILL 0 23-12-2010 16:51:39 //Out on Job 3.
Dept 1 BILL 1 23-12-2010 17:45:48 //In from Job 3.
CustCare AMY 0 03-03-2009 11:12:24 //Out on Job 1
CustCare AMY 1 03-03-2009 10:44:47 //In from Job 1.
CustCare AMY 1 03-03-2009 11:32:32 //In from Job 2 (no matching out record).
CustCare AMY 0 04-03-2009 09:00:39 //Out on Job 1 (no matching in record).
CustCare AMY 0 04-03-2009 09:35:03 //Out on Job 2.
CustCare AMY 1 04-03-2009 11:11:52 //In from Job 2.
CustCare AMY 0 04-03-2009 15:18:41 //Out on Job 3.
CustCare AMY 1 04-03-2009 15:57:51 //In from Job 3.
This is what I get from the query:
DEPTNAME Name CHECKTYPE DATE TIME OUT TIMEIN
Dept 1 BILL 0 23-12-2010 11:46:42 13:04:05 Job 1 OK
Dept 1 BILL 0 23-12-2010 11:46:42 15:07:15 Job 2 gets TimeOut from Job 1
Dept 1 BILL 0 23-12-2010 16:51:39 17:45:48 Job 3 OK
Cust AMY 0 03-03-2009 11:12:24 10:44:47 Job 1 OK
CustCare AMY 1 03-03-2009 11:12:24 11:32:32 Job 2 gets TimeOut from Job 1
CustCare AMY 0 04-03-2009 09:35:03 11:11:52 Job 2 OK, Job 1 omitted.
CustCare AMY 0 04-03-2009 15:18:41 15:57:51 Job 3 OK.
1. I need to fix the query to skip orphaned records for the main report.
2. I need another query to return ONLY orphaned records.
I suspect I may need to use the NOT IN operator for Query #2, but I can't quite figure out how.
All help will be greatly appreciated.
January 25, 2011 at 1:19 pm
I took a stab at what you were looking for. I'm not sure what you are expecting for results in the first query, but to get the orphans, you can use the query below. I used a CTE to calc the data. What were you expecting the results to look like for the first query?
create Table #CheckTimes (UserID int, CheckTime datetime, CheckType char(1))
insert into #CheckTimes
select 1,'2010-12-01 08:12:11','I' union all
select 2,'2010-12-01 08:33:25','I' union all
select 1,'2010-12-01 09:25:45','0' union all
select 2,'2010-12-01 11:15:15','0' union all
select 1,'2010-12-01 14:33:55','1' union all
select 2,'2010-12-01 15:11:22','1' union all
select 2,'2010-12-01 15:35:44','0' union all
select 2,'2010-12-01 16:22:33','1' union all
select 1,'2010-12-01 17:44:01','O'
;with cte as
(select UserID, DATEADD(dd, DATEDIFF(dd,0,CHECKTIME), 0) AS CHECKDATE, CheckTime, CheckType,
ROW_NUMBER() over (PARTITION by UserID order by UserID, CheckTime) RowNum
from #CheckTimes)
select * from (
select CurrVal.UserID, CurrVal.CheckTime, CurrVal.CheckType,
NextVal.CheckTime NextValCheckTime, NextVal.CheckType NextValCheckType,
Case when CurrVal.CheckType = '1' and NextVal.CheckType = '0'
then 1
when CurrVal.CheckType = 'I' and NextVal.CheckType = 'O'
then 1
when CurrVal.CheckType = '0' and NextVal.CheckType = '1'
then 1
when CurrVal.CheckType = 'O' and NextVal.CheckType = 'I'
then 1
when CurrVal.CheckType in ('O','0') and NextVal.CheckType is null
then 1
else 0
end MatchType
from cte CurrVal
left outer join cte NextVal
on CurrVal.UserID = NextVal.UserID
and CurrVal.RowNum = NextVal.RowNum - 1
) v
where MatchType = 0
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 25, 2011 at 3:35 pm
Mike, thanks.
CTEs apparently do not work in Access, I suspect.
The data gets mangled when I paste it here, and I can't attach it in Excel.
One last try:
DEPTNAME Name CHECKTIME CHECKTYPE
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
[highlight=#ffff11]Tech Chris 04-Dec-10 11:22:20 0[/highlight]
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
[highlight=#ffff11]Eng Isaac 06-Dec-10 15:35:46 1[/highlight]
Eng Isaac 06-Dec-10 15:53:41 0
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
[highlight=#ffff11]Eng Isaac 08-Dec-10 11:29:24 1[/highlight]
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
Output:
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
[highlight=#ffff11]CCare Sybil 07-Dec-10 14:05 14:37 00:32[/highlight]
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
[highlight=#ffff11]CCare Sybil 22-Dec-10 09:04 09:51 00:47[/highlight]
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
[highlight=#ffff11]CCare Sybil 27-Dec-10 13:35 14:42 01:06[/highlight]
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
[highlight=#ffff11]CCare Steve 21-Dec-10 11:09 15:56 04:47[/highlight]
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
[highlight=#ffff11]Eng Isaac 08-Dec-10 10:01 11:29 01:27[/highlight]
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
I hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply