Time Difference

[danka]

Hello:

I need to delete from file records that are less then 20 minutes apart
leaving the first from records that are less then 20 minutes apart for the
same card and date. Other entries remain unchanged. Records are not sorted
in any particular way.

For example for Card 1234 I need to remove 2nd and 3rd record for 01/01/2005
because they are less then 20 minutes apart and we want to ingonre those
entries. How would I calculate the time difference using the above criteria?
Your help would be much appreciated.


CardId Date Time

1234 01/01/2005 12:21:14 PM
1234 01/01/2005 12:21:17 PM
1234 01/01/2005 12:26:31 PM
1234 01/01/2005 15:26:31 PM
1234 01/02/2005 14:00:00 am
3567 01/01/2005 10:00:00 AM
3567 01/02/2005 12:00:00 AM
3567 01/02/2005 15:00:00 pm

 

[[MVP] S.Clark]

This is probably best served in a code module, as you can't easily perform
the iterative and complex nature in a single query.

I don't know if you're familiar with VBA, so I'll spare wrting the code
until I know it will be of use.

 

[John Spencer]

UNTESTED SQL, which I think will identify the records to be deleted.

SELECT CardID, DateTime
FROM TheTable
WHERE EXISTS
(SELECT *
FROM TheTable as Tmp
WHERE Tmp.CardID = TheTable.CardID
AND Tmp.DateTime > DateAdd("n",-20,TheTable.DateTime)
AND Tmp.DateTime<TheTableDateTime)

There is a problem with the above.
With the following dataset Line 2,3, and 4 would get deleted.
Note that once you delete lines 2 and 3 then you might not want to delete
line 4, since the gap is now 21 minutes and some seconds between the line 1
and line 4.
1234 01/01/2005 12:21:14 PM
1234 01/01/2005 12:21:17 PM
1234 01/01/2005 12:26:31 PM
1234 01/01/2005 12:42:30 PM
1234 01/01/2005 15:26:31 PM
1234 01/02/2005 14:00:00 am
3567 01/01/2005 10:00:00 AM
3567 01/02/2005 12:00:00 AM
3567 01/02/2005 15:00:00 pm

Post back with information on how you would want this situation handled. I
may be able to come up with a way to handle the situation in a query.

 

[danka]

Hello:

the forth record should stay to be consistent. I will test the query to see
the result. The most typical and real life data is as follow:

Card id
266 05/12/2005 12:28:22 pm
05/12/2005 12:28:52 pm
05/12/2005 10:05:03 pm
05/12/2005 10:05:06 pm

In this case record 2 and 3 need to be selected. This is the most typical
case.

Sometims there are 3 records:

266 05/12/2005 5:01:58 am
05/12/2005 1:29:55 pm
05/12/2005 1:27:31 pm

In this case record two is to be selected for deletion.

There is last and very rare but possible case like this:

266 05/12/2005 8:53:23 am
266 05/12/2005 8:53:25 am
266 05/12/2005 9:27:42 am
266 05/12/2005 9:27:44 am
266 05/12/2005 4:08:27 am

In this case record 2 and 4 need to be selected for deletion.

This looks like really tough!

I tried some queries, but cannot get all the situations covered...

Appreciate your help very much.

 

[danka]

Hello:

Yes, I can work with VBA code, I was thinking and ready to try something.

 

[John Spencer]

Then you are probably going to need to run the query a couple of times.
Delete the first candidate record of each set and then repeat as many times
as needed.

The following may be too complex for Access and it will probably be slow. I
would make sure I had indexes on both CardID and DateTime. Running the
following query should find the earliest record in each CardID group that
needs to be deleted. Then you delete those record and run the query again.

First Rule: Make a backup of your data before you delete any records. If
the delete query is in error, you will not be able to undo the delete.

SELECT CardID, DateTime
FROM TheTable as A
WHERE A.DateTime =
(SELECT Min(DateTime)
FROM TheTable
WHERE TheTable.CardID = A.CardID)
AND EXISTS
(SELECT *
FROM TheTable as Tmp
WHERE Tmp.CardID = TheTable.CardID
AND Tmp.DateTime > DateAdd("n",-20,TheTable.DateTime)
AND Tmp.DateTime<TheTableDateTime))

A variation of the above which may or may not work
SELECT CardID, DateTime
FROM TheTable as A
WHERE A.DateTime in
(SELECT Top 1 (DateTime)
FROM TheTable
WHERE TheTable.CardID = A.CardID)
AND EXISTS
(SELECT *
FROM TheTable as Tmp
WHERE Tmp.CardID = TheTable.CardID
AND Tmp.DateTime > DateAdd("n",-20,TheTable.DateTime)
AND Tmp.DateTime<TheTableDateTime)
ORDER BY TheTable.DateTime)

 

[[MVP] S.Clark]

Open the recordset into a variable, and loop through. For each record,
locate any that are within 20 mins and delete.

dim rs as recordset
dim dtmCurrTime as date

set rs = currentdb.openrecordset("tablename")
with rs
do while not .eof
currentdb.execute "Delete * from tablename where... " 'Within 20
mins
.movenext
loop
end with

I didn't finish the SQL string, but you would use DateDiff and the variable
dtmCurrTime to establish any record within 20 mins.

As the table is having records deleted on the fly, and the recordset is
based on that table, you may need to add validations to ensure that the
record that you move to really has data. (Not real sure how it will react)

 

[danka]

Hello John:

I appreciate your help very much. I believe, I sent a reply on Friday but
it did not go through for some strange reason.
Anyway, I tried several queries as you suggested with mixed results, I
probaly used incorrect statements. Based on the previous sample data I run
run this query (multiple is my table name):

SELECT *
FROM Multiple AS tmp INNER JOIN multiple ON tmp.CardId = multiple.CardId
WHERE (((tmp.CardId)=[multiple].[cardid]) AND
((tmp.DateTime)>DateAdd('n',-20,[Multiple].[datetime]) And
(tmp.DateTime)<[multiple].[datetime]))
ORDER BY tmp.CardId, tmp.DateTime;

With this result: (it almost right but returns the later time and not the
earlier time in each date which is 20 min apart, eg:
266 12-May-05 8:53:23 AM (should be 8:53:25)
266 12-May-05 9:27:42 AM (should be 9:27:44)
266 12-May-05 12:28:22 PM (should be 12:28:52)
266 12-May-05 1:27:31 PM
266 13-May-05 4:00:00 PM
266 13-May-05 10:00:00 PM

Does it make any sense? If I could get the latest and not the earliers date
from the dates, I could delete them and I would be home.

Is there a way to modify this query?

Thanks.

 

[John Spencer]

If your query is returning the records you want to keep, you can use it to
build a new table to replace the existing table Multiple.

OR you can try using it in an unmatched query to identify (and delete) the
records you need to dispose of

DELETE DistinctRow M.*
FROM Multiple As M LEFT JOIN TheQuery as Q
ON M.CardID = Q.CardID AND
M.DateTime = Q.DateTime
WHERE Q.DateTime is Null

Hello John:

I appreciate your help very much. I believe, I sent a reply on Friday but
it did not go through for some strange reason.
Anyway, I tried several queries as you suggested with mixed results, I
probaly used incorrect statements. Based on the previous sample data I
run
run this query (multiple is my table name):

SELECT *
FROM Multiple AS tmp INNER JOIN multiple ON tmp.CardId = multiple.CardId
WHERE (((tmp.CardId)=[multiple].[cardid]) AND
((tmp.DateTime)>DateAdd('n',-20,[Multiple].[datetime]) And
(tmp.DateTime)<[multiple].[datetime]))
ORDER BY tmp.CardId, tmp.DateTime;

With this result: (it almost right but returns the later time and not the
earlier time in each date which is 20 min apart, eg:
266 12-May-05 8:53:23 AM (should be 8:53:25)
266 12-May-05 9:27:42 AM (should be 9:27:44)
266 12-May-05 12:28:22 PM (should be 12:28:52)
266 12-May-05 1:27:31 PM
266 13-May-05 4:00:00 PM
266 13-May-05 10:00:00 PM

Does it make any sense? If I could get the latest and not the earliers
date
from the dates, I could delete them and I would be home.

Is there a way to modify this query?

Thanks.
--
danka

Then you are probably going to need to run the query a couple of times.
Delete the first candidate record of each set and then repeat as many
times
as needed.

The following may be too complex for Access and it will probably be slow.
I
would make sure I had indexes on both CardID and DateTime. Running the
following query should find the earliest record in each CardID group that
needs to be deleted. Then you delete those record and run the query
again.

First Rule: Make a backup of your data before you delete any records. If
the delete query is in error, you will not be able to undo the delete.

SELECT CardID, DateTime
FROM TheTable as A
WHERE A.DateTime =
(SELECT Min(DateTime)
FROM TheTable
WHERE TheTable.CardID = A.CardID)
AND EXISTS
(SELECT *
FROM TheTable as Tmp
WHERE Tmp.CardID = TheTable.CardID
AND Tmp.DateTime > DateAdd("n",-20,TheTable.DateTime)
AND Tmp.DateTime<TheTableDateTime))

A variation of the above which may or may not work
SELECT CardID, DateTime
FROM TheTable as A
WHERE A.DateTime in
(SELECT Top 1 (DateTime)
FROM TheTable
WHERE TheTable.CardID = A.CardID)
AND EXISTS
(SELECT *
FROM TheTable as Tmp
WHERE Tmp.CardID = TheTable.CardID
AND Tmp.DateTime > DateAdd("n",-20,TheTable.DateTime)
AND Tmp.DateTime<TheTableDateTime)
ORDER BY TheTable.DateTime)

 

[danka]

Hello:

It took me a while to come back to this as other priorities took precedence.
Based on your suggestions I came with the following code. It seems to be
working ok although I am sure it can be written in a more efficient way.
Possibly, you may have some suggestions. I tested it with various sets of
data. Files read will have various card Ids, dates and times. This routine
calculates time difference for the same card id and deletes records more then
10 minutes/600 apart. It ignores change of date (I would need another
loop?), as the date changes for the same card the difference will be always
more then 10 minutes so the record would not be delete.
SAMPLE DATA
CardId date1 Time1 LastName FirstName Notes
1 12/9/2005 12:00:00 PM Gibbons Lee 1
1 12/9/2005 12:09:00 PM Gibbons Lee D
1 12/9/2005 12:15:00 PM Gibbons Lee 1
1 12/9/2005 12:35:00 PM Gibbons Lee 1
1 12/9/2005 12:37:00 PM Gibbons Lee D
1 12/9/2005 12:39:00 PM Gibbons Lee D
1 12/9/2005 1:39:00 PM Gibbons Lee 1
1 12/10/2005 12:00:00 PM Gibbons Lee 1
1 12/10/2005 12:02:00 PM Gibbons Lee D
1 12/10/2005 12:04:00 PM Gibbons Lee D
1 12/10/2005 12:09:00 PM Gibbons Lee D
1 12/10/2005 12:15:00 PM Gibbons Lee 1
1 12/10/2005 12:39:00 PM Gibbons Lee 1
1 12/10/2005 1:40:00 PM Gibbons Lee 1
2 12/10/2005 1:40:00 PM new user 1
2 12/10/2005 1:42:00 PM new D
2 1/1/2006 10:00:00 PM new user 1
2 1/1/2006 10:02:00 PM new user D
2 1/2/2006 12:00:00 PM new user 1

Private Sub MulipleVba_Click()
Dim dbCurr As Database
Dim rs As Recordset
Dim FIRSTDATE
Dim NEXTDATE
Dim FIRSTTIME
Dim NEXTTIME
Dim CARDCURRENT
Dim CARDNEXT
Dim strSQL As String
Dim TimeDiff
Dim CombineFIRST ' date and time
Dim CombineNEXT

Set dbCurr = Currentdb()
strSQL = "SELECT Atrpt7.CardId, Atrpt7.date1, Atrpt7.Time1 FROM Atrpt7
ORDER BY Atrpt7.CardId, Atrpt7.date1, Atrpt7.Time1;"
Set rs = dbCurr.OpenRecordset(strSQL)

With rs
On Error GoTo ErrorHandler
' assign valuse to FIRSTDATE, FIRSTTIME, NEXTDATE, NEXTTIME and
CARDCURRENT
.MoveFirst ' start at the BOF
CARDCURRENT = !CardId
FIRSTDATE = !Date1
FIRSTTIME = !Time1
.MoveNext
NEXTDATE = !Date1
NEXTTIME = !Time1
CombineNEXT = NEXTDATE & " " & NEXTTIME 'combine date and time
CombineFIRST = FIRSTDATE & " " & FIRSTTIME


Do While Not .EOF

Do While CARDCURRENT = !CardId ' if card current is not equal
current card id then exit loop and reset CARDCURRENT

TimeDiff = DateDiff("s", CombineFIRST, CombineNEXT)

If TimeDiff <= 600 Then ' if difference is less then 10 minutes
MsgBox "Less then 600 delete record" & TimeDiff / 60
.Delete
.MoveNext
CARDNEXT = !CardId
'
NEXTDATE = !Date1 ' set nextdate to current date
NEXTTIME = !Time1 ' set nexttime to current time
CombineNEXT = NEXTDATE & " " & NEXTTIME
MsgBox "what is cardcurrent and card id if not equal exit loop"
& CARDCURRENT & !CardId

Else
MsgBox "More then 600 - record stays" & TimeDiff / 60
.MoveNext ' move to next record
FIRSTDATE = NEXTDATE
FIRSTTIME = NEXTTIME ' set time - next becomes first
CombineFIRST = FIRSTDATE & " " & FIRSTTIME
NEXTDATE = !Date1 ' assign next record to have 2 dates to
compare
NEXTTIME = !Time1
CombineNEXT = NEXTDATE & " " & NEXTTIME
End If

Loop
CARDCURRENT = !CardId 'assign card id to CARDCURRENT
FIRSTDATE = !Date1 '
FIRSTTIME = !Time1
.MoveNext ' move to next record
NEXTDATE = !Date1 ' assign next record to have 2 dates to compare
NEXTTIME = !Time1
CombineNEXT = NEXTDATE & " " & NEXTTIME 'combine into one
CombineFIRST = FIRSTDATE & " " & FIRSTTIME
Loop ' loop to new card
End With

ExitNoRecords:
Exit Sub

ErrorHandler:
' Display error information.
'MsgBox "Error number is " & Err.Number & ": " & Err.Description
If Err.Number = 3021 Then 'no current record - end of file and cannot
move to compare
' Resume with statement following occurrence of error.
Resume ExitNoRecords
End If

End Sub