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:
DEPTNAMENameCHECKTYPEDATECHECKTIME
Dept 1 BILL023-12-201011:46:42 //Out on Job 1.
Dept 1 BILL123-12-201013:04:05 //In from Job 1.
Dept 1 BILL123-12-201015:07:15 //In from Job 2 (no matching out record).
Dept 1BILL023-12-201016:51:39 //Out on Job 3.
Dept 1 BILL123-12-201017:45:48 //In from Job 3.
CustCareAMY003-03-200911:12:24 //Out on Job 1
CustCareAMY103-03-200910:44:47 //In from Job 1.
CustCareAMY103-03-200911:32:32 //In from Job 2 (no matching out record).
CustCareAMY004-03-200909:00:39 //Out on Job 1 (no matching in record).
CustCareAMY004-03-200909:35:03 //Out on Job 2.
CustCareAMY104-03-200911:11:52 //In from Job 2.
CustCareAMY004-03-200915:18:41 //Out on Job 3.
CustCareAMY104-03-200915:57:51 //In from Job 3.
This is what I get from the query:
DEPTNAMENameCHECKTYPEDATE TIME OUT TIMEIN
Dept 1 BILL0 23-12-2010 11:46:42 13:04:05 Job 1 OK
Dept 1 BILL0 23-12-2010 11:46:42 15:07:15 Job 2 gets TimeOut from Job 1
Dept 1BILL0 23-12-2010 16:51:39 17:45:48 Job 3 OK
Cust AMY0 03-03-2009 11:12:24 10:44:47 Job 1 OK
CustCareAMY1 03-03-2009 11:12:24 11:32:32 Job 2 gets TimeOut from Job 1
CustCareAMY0 04-03-2009 09:35:03 11:11:52 Job 2 OK, Job 1 omitted.
CustCareAMY0 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:
DEPTNAMENameCHECKTIMECHECKTYPE
AdminSammy02-Dec-10 10:36:310
TechTed02-Dec-10 11:13:570
TechSidney02-Dec-10 12:50:591
EngIsaac02-Dec-10 12:56:210
TechSidney02-Dec-10 13:20:140
EngIsaac02-Dec-10 13:45:371
TechSidney02-Dec-10 15:39:341
CCareSteve02-Dec-10 15:39:461
TechChris03-Dec-10 09:33:240
CCareSybil03-Dec-10 10:05:000
CCareSybil03-Dec-10 11:05:381
EngIsaac03-Dec-10 12:13:380
CCareSteve03-Dec-10 12:54:461
TechSidney03-Dec-10 12:55:111
CCareSybil03-Dec-10 13:24:500
CCareSteve03-Dec-10 13:25:230
EngIsaac03-Dec-10 13:50:381
CCareSybil03-Dec-10 14:05:551
CCareSteve03-Dec-10 14:06:041
TechTed03-Dec-10 14:06:361
TechSidney03-Dec-10 15:13:131
CCareSybil03-Dec-10 15:14:390
CCareSybil03-Dec-10 16:06:281
CCareSteve03-Dec-10 16:11:440
EngRich03-Dec-10 16:22:041
EngJOHN03-Dec-10 16:24:321
TechChris03-Dec-10 16:24:441
EngJOHN04-Dec-10 09:09:060
EngRich04-Dec-10 09:10:080
[highlight=#ffff11]TechChris04-Dec-10 11:22:200[/highlight]
EngRich04-Dec-10 11:24:430
CCareSybil04-Dec-10 12:32:160
CCareSybil04-Dec-10 12:50:141
EngRandy06-Dec-10 11:19:321
EngRich06-Dec-10 12:09:470
TechTed06-Dec-10 13:03:460
CCareSteve06-Dec-10 13:50:000
CCareSybil06-Dec-10 14:42:450
TechSidney06-Dec-10 14:51:201
EngRandy06-Dec-10 15:22:580
EngRich06-Dec-10 15:27:261
[highlight=#ffff11]EngIsaac06-Dec-10 15:35:461[/highlight]
EngIsaac06-Dec-10 15:53:410
CCareSybil06-Dec-10 16:10:041
EngJOHN07-Dec-10 08:54:050
EngRandy07-Dec-10 09:07:470
EngRich07-Dec-10 09:08:110
EngIsaac07-Dec-10 10:50:230
CCareSteve07-Dec-10 11:22:350
TechNICO07-Dec-10 11:22:420
EngIsaac07-Dec-10 12:34:171
CCareSteve07-Dec-10 12:36:051
CCareSteve07-Dec-10 14:05:310
CCareSybil07-Dec-10 14:05:360
CCareSybil07-Dec-10 14:37:471
CCareSybil07-Dec-10 14:47:351
CCareSybil07-Dec-10 15:04:510
TechSidney07-Dec-10 15:04:560
CCareSteve07-Dec-10 15:30:081
EngJOHN07-Dec-10 15:42:221
EngRich07-Dec-10 15:49:411
EngIsaac08-Dec-10 10:01:560
TechSidney08-Dec-10 10:32:150
EngIsaac08-Dec-10 10:49:311
[highlight=#ffff11]EngIsaac08-Dec-10 11:29:241[/highlight]
TechNICO08-Dec-10 11:40:210
EngIsaac08-Dec-10 12:08:210
EngIsaac08-Dec-10 13:16:511
EngIsaac08-Dec-10 13:53:500
EngRandy08-Dec-10 14:24:231
TechNICO08-Dec-10 14:29:541
CCareSybil08-Dec-10 14:55:180
EngIsaac08-Dec-10 15:04:301
EngIsaac10-Dec-10 09:52:260
TechTed10-Dec-10 10:24:160
EngIsaac10-Dec-10 10:24:411
CCareSteve10-Dec-10 13:29:500
TechNICO10-Dec-10 15:08:201
TechTed10-Dec-10 15:08:361
CCareSteve10-Dec-10 17:00:220
TechSidney11-Dec-10 09:05:510
EngRandy11-Dec-10 11:31:260
TechSidney11-Dec-10 11:48:121
CCareSteve13-Dec-10 08:58:160
TechSidney13-Dec-10 09:00:060
CCareSteve13-Dec-10 10:16:041
EngJOHN13-Dec-10 10:21:120
TechTed13-Dec-10 10:36:131
TechSidney13-Dec-10 10:36:210
EngIsaac13-Dec-10 12:00:340
EngIsaac13-Dec-10 13:52:231
CCareSybil13-Dec-10 14:01:190
CCareSybil13-Dec-10 14:45:211
Output:
DeptNameDateOutInTime
CCareSybil03-Dec-1010:0511:0501:00
CCareSybil03-Dec-1013:2414:0500:41
CCareSybil03-Dec-1015:1416:0600:51
CCareSybil04-Dec-1012:3212:5000:17
CCareSybil06-Dec-1014:4216:1001:27
CCareSybil07-Dec-1014:0514:4700:41
[highlight=#ffff11]CCareSybil07-Dec-1014:0514:3700:32[/highlight]
CCareSybil13-Dec-1014:0114:4500:44
CCareSybil14-Dec-1012:5914:0001:00
CCareSybil20-Dec-1013:0516:3403:28
CCareSybil21-Dec-1009:2809:4600:17
CCareSybil22-Dec-1009:0410:5101:47
[highlight=#ffff11]CCareSybil22-Dec-1009:0409:5100:47[/highlight]
CCareSybil22-Dec-1012:1113:4901:37
CCareSybil23-Dec-1010:5511:2400:28
CCareSybil27-Dec-1013:3515:0701:31
[highlight=#ffff11]CCareSybil27-Dec-1013:3514:4201:06[/highlight]
CCareSybil27-Dec-1011:4412:1200:28
CCareSybil27-Dec-1009:4611:0701:20
CCareSteve03-Dec-1013:2514:0600:40
CCareSteve07-Dec-1011:2212:3601:13
CCareSteve07-Dec-1014:0515:3001:24
CCareSteve13-Dec-1008:5810:1601:17
CCareSteve14-Dec-1009:1010:3401:24
CCareSteve20-Dec-1011:0612:0300:57
CCareSteve21-Dec-1011:0913:1502:05
[highlight=#ffff11]CCareSteve21-Dec-1011:0915:5604:47[/highlight]
CCareSteve22-Dec-1010:2811:4701:18
CCareSteve22-Dec-1010:2815:2504:56
CCareSteve23-Dec-1011:2212:1600:53
CCareSteve23-Dec-1009:5211:1301:21
CCareSteve27-Dec-1013:3614:4201:06
CCareSteve27-Dec-1008:1211:1703:04
EngIsaac02-Dec-1012:5613:4500:49
EngIsaac03-Dec-1012:1313:5001:37
EngIsaac07-Dec-1010:5012:3401:43
EngIsaac08-Dec-1012:0813:1601:08
EngIsaac08-Dec-1010:0110:4900:47
[highlight=#ffff11]EngIsaac08-Dec-1010:0111:2901:27[/highlight]
EngIsaac08-Dec-1013:5315:0401:10
EngIsaac10-Dec-1009:5210:2400:32
EngIsaac13-Dec-1012:0013:5201:51
EngIsaac16-Dec-1010:3311:4701: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