Ok, so If I'm clear You want to know the sum of the flow for the four
readings taken each hour. So the 4, 4:15,4:30 and 4:45 readings should
be summed. The basic problem is that the timing of these readings is
only semi-accurate resulting in reading times like 3:59, 4:16, 4:29 and
4:45.
Since you didn't mention a database and I'm most comfortable with SQL
Server your solution is going to be SQL Server specific, but the
concepts are the same and the SQL is similar regardless of which DB you
use.
I have found that date processing in SQL always seems to cause
headaches. One of the major concerns driving the direction you take is
how much load is on the system (I'm guessing its not terrible). If you
are running all sorts of datePart functions SQL Server is most likely
going to But, I have a couple ideas on how to resolve your issues:
1) switch from the datetime data type to the smallDateTime. The reason I
suggest this is because the accuracy is only down to the Minute, which
might alleviate the gymnastics you have to go through. A small date time
will allow you to store date and time data between January 1, 1900,
through June 6, 2079.
2) You could add a batch stamp. Meaning that for all the 4 PM readings
a batch number is stamped on the 4 records so that way you just group by
the batch ID. While this isn't a terribly "proper" database technique,
it sure would solve your problem in an easy manor.
3) Run an update on the time stamps included in the database at an
interval that suits you. You could use the SQL Agent or CFEXECUTIVE to
schedule a stored procedure that cleaned the data. In this scenario you
might want to add some sort of flag like "cleanedYN" or whatever so that
the date time cleaning dosen't hog to many resources, date processing is
heavy. The code for this is similar to what I'll write below you just
run and update on fields that haven't been processed. The key here is
that when querying the result set you have to be able to split out the
date and the hour either by using string functions or into their own
columns otherwise your grouping isn't going to work right.
4) Two of the greatest features ever in SQL are CASE statements and
Temporary Tables. You could use a searched case statement something
like the following to do the processing on the fly (sorry if email
messes up the formatting). The SQL is a bit ugly, but hopefully reads
pretty easily.
Some Notes :
- I used a temp table because I always find that trying to use a
searched case statement and group by causes issues because the group by
can only use original column names not the Alias you give it.
- I split out year, month and day into their own columns, but you don't
need to if you put them as a string in the format 'yyyy-mm-dd' then sort
DESC you will get the dates in order starting with the most recent.
- In the result, but you can't use the "originalFlowDateTime" column
because each of the times recorded in your data is distinct as such no
grouping would actually occur if you used "originalFlowDateTime"
- I assume that all this code is wrapped in some stored procedure
DECLARE @highTolerance int
DECLARE @lowTolerance int
SELECT @highTolerance = 48 -- highest reading that counts as a
reading for the current hour
SELECT @lowTolerance = 57 -- lowest minutes that we will
let count as a reading for the following hour
CREATE Table #myTemp (
readingYear int,
readingMonth int,
readingDay int,
readingHour int,
totalFlow int
)
INSERT INTO #myTemp
SELECT datePart ("yyyy", originalFlowDateTime) AS readingYear,
datePart ("mm", originalFlowDateTime) AS
readingMonth,
datePart ("dd", originalFlowDateTime) AS
readingDay,
readingHour CASE
WHEN datePart("mi",
originalFlowDateTime) >= @lowTolerance
THEN
datePart("hh", originalFlowDateTime) + 1
WHEN datePart("mi",
originalFlowDateTime) < @highTolerance AND
datePart("mi", originalFlowDateTime) > 0
THEN
datePart("hh", originalFlowDateTime)
END,
flowVolume
FROM flowReadings
GO
SELECT readingYear,
readingMonth,
readingDay,
readingHour,
sum(totalFlow) as totalFlow
from #myTemp
Group By readingYear, readingMonth, readingDay, readingHour
Well, their it is. I hope this helps.
-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 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
