既然連一個完整的測試數據都不願給出,那你自己想辦法吧,我幫不了。
授人以渔,不授人以鱼。
Record# USERID CHECKTIME 6354 2 2011.08.21 21:12:59 26318 2 2011.10.10 19:34:33 37469 2 2011.11.09 19:07:51 37801 2 2011.11.10 19:02:44 5481 5 2011.08.18 20:17:59 17505 5 2011.09.20 19:48:36 43437 34 2011.11.27 19:05:18 43785 38 2011.11.28 20:32:11 32415 39 2011.10.26 21:16:11 19821 51 2011.09.27 20:38:57 29122 63 2011.10.17 20:07:53 8400 68 2011.08.26 19:04:17 27124 68 2011.10.11 19:05:50 32906 68 2011.10.27 19:43:17 43850 68 2011.11.25 19:01:35 46053 68 2011.12.01 20:00:55 6614 70 2011.08.22 19:19:00 28590 70 2011.10.14 20:05:38 22856 71 2011.10.03 19:02:22 11435 83 2011.09.01 21:29:25 22421 83 2011.10.02 23:07:23 22863 83 2011.10.03 21:03:56 27847 83 2011.10.13 20:58:06 33141 83 2011.10.30 21:01:34 40453 83 2011.11.17 19:25:37 43784 88 2011.11.28 19:43:38 11434 89 2011.09.01 21:29:11 18961 89 2011.09.23 21:27:24 19822 89 2011.09.27 20:42:46 22861 89 2011.10.03 20:35:53 22862 89 2011.10.03 21:03:45 28591 89 2011.10.14 21:30:26 32177 89 2011.10.25 21:04:12 32414 89 2011.10.26 20:37:40 33140 89 2011.10.30 21:01:29 41400 91 2011.11.21 20:13:15 28589 95 2011.10.14 19:11:49 39869 95 2011.11.16 19:47:45 22857 99 2011.10.03 19:02:38 26319 99 2011.10.10 19:46:58 29350 99 2011.10.18 19:33:31 36757 99 2011.11.07 19:04:05 47909 99 2011.12.08 19:15:27 48231 99 2011.12.07 19:08:42 44748 109 2011.11.29 19:01:01 33757 113 2011.10.28 19:11:15 33758 113 2011.10.28 20:46:16 18960 118 2011.09.23 19:30:23 32905 118 2011.10.27 19:01:29 43873 118 2011.11.26 19:04:36 46833 118 2011.12.05 19:38:00 41399 131 2011.11.21 19:14:14 46050 131 2011.12.01 19:19:05 48199 131 2011.12.09 19:09:29 47908 135 2011.12.08 19:00:53 5480 139 2011.08.18 19:37:15 5482 139 2011.08.18 21:36:56 17747 139 2011.09.21 20:24:00 18277 139 2011.09.22 23:06:15 22858 139 2011.10.03 19:04:57 30852 139 2011.10.21 19:00:48 45816 139 2011.12.03 20:28:06 46052 139 2011.12.01 19:23:14 6355 153 2011.08.21 22:01:19 22859 153 2011.10.03 19:25:51 13987 210 2011.09.09 20:36:37 15961 210 2011.09.15 20:16:49 21760 210 2011.09.30 19:19:00 22860 210 2011.10.03 20:32:14 30254 210 2011.10.20 19:27:21 46051 210 2011.12.01 19:19:45 46834 210 2011.12.05 20:47:49 18278 214 2011.09.22 23:06:27 35465 214 2011.11.03 20:39:10
CLEAR ALL CLOSE DATABASES ALL CLEAR SET SAFETY OFF SET EXACT ON SET DATE ANSI && 使用yy/mm/dd格式 SET CENTURY ON && 世纪格式yyyy SET STRICTDATE TO 1 && 严格日期格式 SET HOURS TO 24 cInTime = "19:00" cOutTime = "22:30" CreateTargetDBF() USE TargetDB INDEX TargetDB EXCLUSIVE IN 0 SELECT TargetDB cID = "" SCAN ALL IF cID != TargetDB.id cID = TargetDB.id nCount = 0 ELSE nCount = nCount + 1 ENDIF IF nCount > 0 SKIP -nCount tTime1 = TargetDB.checktime REPLACE TargetDB.time1 WITH "", TargetDB.time2 WITH "", TargetDB.time3 WITH "" SKIP nCount tTime2 = TargetDB.checktime REPLACE TargetDB.time1 WITH "", TargetDB.time2 WITH "" REPLACE TargetDB.time3 WITH GetTimeString(tTime2 - tTime1) ELSE tTime1 = DATETIME(YEAR(TargetDB.checktime), MONTH(TargetDB.checktime), DAY(TargetDB.checktime), VAL(LEFT(cInTime,2)), VAL(RIGHT(cInTime,2))) tTime2 = DATETIME(YEAR(TargetDB.checktime), MONTH(TargetDB.checktime), DAY(TargetDB.checktime), VAL(LEFT(cOutTime,2)), VAL(RIGHT(cOutTime,2))) REPLACE TargetDB.time1 WITH GetTimeString(TargetDB.checktime - tTime1) REPLACE TargetDB.time2 WITH GetTimeString(tTime2 - TargetDB.checktime) ENDIF ENDSCAN BROWSE USE IN TargetDB CLOSE DATABASES ALL CLEAR ALL RETURN PROCEDURE CreateTargetDBF() USE 111 ALIAS SourceDB EXCLUSIVE IN 0 INDEX ON userid TO 111 SELECT SourceDB COPY STRUCTURE TO TargetDB ALTER TABLE TargetDB ADD COLUMN id C(11) ALTER TABLE TargetDB ADD COLUMN time1 C(8) && 打卡时间与标准上班时间之差 ALTER TABLE TargetDB ADD COLUMN time2 C(8) && 标准下班时间与打卡时间之差 ALTER TABLE TargetDB ADD COLUMN time3 C(8) && 有完整上下班打卡记录的时间 SELECT TargetDB INDEX ON id TO TargetDB SELECT SourceDB GOTO TOP SCAN ALL IF SUBSTR(TTOC(SourceDB.checktime), 12, 5) >= cInTime SELECT TargetDB APPEND BLANK REPLACE TargetDB.userid WITH SourceDB.userid REPLACE TargetDB.checktime WITH SourceDB.checktime REPLACE TargetDB.id WITH PADL(SourceDB.userid, 3, '0') + CHRTRAN(LEFT(TTOC(SourceDB.checktime), 10), '.', '') ENDIF SELECT SourceDB ENDSCAN USE IN SourceDB USE IN TargetDB ENDPROC FUNCTION GetTimeString(tnSeconds) LOCAL lcString lcString = "" DO WHILE tnSeconds > 0 lcString = PADL(MOD(tnSeconds, 60), 2, '0') + lcString tnSeconds = INT(tnSeconds / 60) IF tnSeconds > 0 lcString = ":" + lcString ENDIF ENDDO RETURN lcString ENDFUNC