Unanswered: Stuck on filtering multiple like records
I have a FoxPro error table, it holds error log entries. Whenever any of our other FoxPro applications crash, they write the error info to this table. If any of those applications throw multiple errors before completely dying, then multiple errors get logged to that error table. For departmental metrics, we need to just log 1 event per crash. So, rather than rewrite over 100 application's event handling, right now we just want to grab a week's worth of the errors, copy them to a separate table, and then scrub that new table so it only keeps a solitary record. So basically if the user, computer, error number, and date&time (within 60 seconds) is the same, delete any additional records that exist. The Date and Time is stored exactly in the format 01/01/2014 01:05:55 PM. Here is what I have so far:
*!* METRICS.PRG This application needs to be scheduled to run every Sunday night at midnight to provide
*!* accurate metrics on VFP errors.
SET CENTURY ON
STORE DATE( ) TO gdDayNumEnd
gdDayNumStart = (gdDayNumEnd -7)
SELECT * FROM errors_tbl WHERE date > gdDayNumStart and date < gdDayNumEnd
COPY TO weekly
This gives me my week's worth of records out of the errors_tbl table and stores them to weekly.dbf, however I am stuck on performing the filtering part, especially since the time part is unique and I haven't figured out how to strip off the seconds from the timestamp, or perform some other similar measure. Any suggestions would be appreciated.
I haven't figured out how to strip off the seconds from the timestamp
If it were me, I would not eliminate the Seconds (since the first error could occur before the min changes and subsequent ones after that)
Example: first error occurs at 01/01/2014 01:04:59 PM
next error occurs at 01/01/2014 01:05:00 PM
But instead I would actually use them to find the first error occurrence by converting the Hours, Min, & Seconds to a single Integer value from midnight ((24Hour * 3600) + (Min * 60) + (Seconds)).
From there I would programatically look for the first occurrence.
But if you want to eliminate the Seconds....
You might first want to break the DateTime value into 2 parts - Date & Time
To do that you can use the VFP Functions - TTOD() and TTOC() <-- see VFP Help file.
Note that the VFP TTOC() function has an optional parameter included.
The VFP Help file says the following:
Parameter value 2 ==> Specifies that TTOC( ) return a Character string consisting of only the time portion of the DateTime expression. The settings of SET SECONDS and SET DATE specify if the seconds portion of the time is included in the character string.