Some quick thoughts (not knowing your full situation) is to do a query
looking for a match between say 4:10 and 4:20 (or what ever range would wrap
the first entry without including the last entry from the previous e-mail).
Get the unique id for that record, query the db again for the next 3 entries
based on id. Sum all 4. A couple things to look out for:
Is this the only system (email from one reservoir) updating the table?
If not you will obviously need to match the reservoir id in what you do
above.
You can then sum the data in your query using the sum(field) or by looping
thru the output and adding to a variable.
or
when you process the e-mail, add a field indicating the "batch #" using a
unique id for that batch
or
have the system that e-mails the data add a field that combines a unique
"batch #" field that you can search on like reservoir&date&hour
or
switch to 24hour clock so you can query for all between "16:00 & 17:00"
HTH
Stephen
-----Original Message-----
From: John McCosker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 28, 2001 12:29 PM
To: CF-Talk
Subject: RE: SQL + time stamp
Eric one reading is coming in every 15 minutes of every hour.
Its a C compiled agent that is taking quartly flowrates from a reservoir and
emailing to our database.
I'm getting 4 readings, one every 15 minutes, 4:15 , 4:30 , 4:45 , and 5:00
and so on.
the only thing is they are not coming in quite as accurate like that, at
least not all the time.
More like 4:16 , 4:29 , :4:45 , 4:59. So what I want to do is sum(all
Quaters for every hour).
Any ideas? (hope this is clearer!!)
-----Original Message-----
From: Eric Barr [mailto:[EMAIL PROTECTED]]
Sent: 28 February 2001 16:07
To: CF-Talk
Subject: RE: SQL + time stamp
John,
I'm not quite clear what your getting at .... do you mean that a reading
is taken every 15 minutes and you want to know the sum of the readings
at 00, 15,30, and 45 ? so that the readings from 2:15 are summed with
readings from 1:15 and 3:15 ?
Or are multiple readings being inserted every 15 minutes and you want
the sum of all the entries entered at 2:15 to be in one group and 2:30
in another and 3:15 into still another ?
-eric
------------------------------------------------
Common sense is genius dressed in its working clothes.
-- Ralph Waldo Emerson
Eric Barr
Zeff Design
(p) 212.714.6390
(f) 212.580.7181
-----Original Message-----
From: John McCosker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 28, 2001 8:11 AM
To: CF-Talk
Subject: SQL + time stamp
I know this is an SQL question.
I am getting readings inserting into a database every 15 minutes on the
hour.
They are time stamped like so,
(dt) (columOne)
2001-02-28 12:59:59 38.7
2001-02-28 12:45:01 38.77
2001-02-28 12:30:01 38.7
2001-02-28 12:15:01 37.22
My problem is how would I do a sum value for 'columOne' adding up each
quarter for each hour?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
