December 9, 2010 at 5:41 am
Hi,
I have a data table with parameter values stored against identifiers and timestamps and I have a parameter table where I have defined all the parameters that are in use as follows . . .
tblData has fields :
LogTime (= data timestamp),
PID (= parameter identifier),
Value (= parameter value at timestamp),
Period (= denotes whether data is shiftly, daily, weekly, monthly)
tblPID has fields :
PID (prim key),
Name,
Property,
Units,
AggregationType (tells me how to aggregate the data for this PID = A for average, S for Sum, L for last and N for None)
I want to retrieve aggregated DAILY data from this table. For example, if there are multiple shift values on a particular day for a tonnage measurement, I want to SUM those values to give me a daily total. If however there are multiple shift values of a quality parameter such as "Gold Content", then one would want to AVERAGE the shift values to give a DAILY average value. The complication comes when there is a COUNTER value stored in a parameter which I will explain below.
The query I have written so far to give me what I want is shown below :
SELECT
CASE P.AggregationType
WHEN 'A' THEN AVG(D.Value)
WHEN 'S' THEN SUM(D.Value)
WHEN 'L' THEN MAX(D.Value) --Actually want some kind of "LAST" function here
WHEN 'N' THEN null
END AS Result
FROM tblData D LEFT JOIN tblPID P ON D.PID = P.PID
WHERE D.[LogTime] >= '2010-04-16 07:00'
AND D.[LogTime] < '2010-04-17 07:00'
AND D.PID = 1048
GROUP BY P.AggregationType
Obviously the desired PID and the LogTime range are updated dynamically each time I run this query.
The above is OK for AVG and SUM type aggregations. The problem comes with parameters that store counter (or cumulative) values, e.g. a mass totaliser on a conveyor belt will continue incrementing as time progresses, but eventually the value must rollover at some maximum value to begin at zero again. Say the maximum allowable value of one of these counters was 99,999. As the day progressed on a day when the counter was close to rolling over, the values might be something like 99,723 at the end of morning shift, 99,976 at the end of afternoon shift and 139 at the end of night shift. In this case, my query will return the MAX value of 99,976 for the day, when I want it to return the last value of 139 for this parameter.
I need to try and order the values for a particular PID by LogTime and then get the last value within the specified time period. Any ideas please. (I hope it's not too simple and embarassing an answer, but I am a newbie !)
Thanks,
Adrian
December 9, 2010 at 6:38 am
Something like this should work provided you are using SQL 2005 or later.
;WITH RelevantData as (
SELECT D.LogTime, D.PID, D.Value, P.AggregationType
FROM tblData D LEFT JOIN tblPID P ON D.PID = P.PID
WHERE D.[LogTime] >= '2010-04-16 07:00'
AND D.[LogTime] < '2010-04-17 07:00'
AND D.PID = 1048 )
SELECT
CASE AggregationType
WHEN 'A' THEN AVG(Value)
WHEN 'S' THEN SUM(Value)
WHEN 'L' THEN (select top 1 Value from R order by LogTime desc)
ELSE null
END as Result
FROM RelevantData R GROUP BY AggregationType
Tom
December 10, 2010 at 12:42 am
Hi Tom, thanks very much for your reply.
It makes perfect sense and I see what you are trying to do however I cannot get it to run.
I get an error : "Invalid object name 'R'."
Edit : OK - tried to retract this posting, but I couldn't !
If I remove the alias R and use the full name "RelevantData", it works fine thanks.
December 13, 2010 at 7:04 am
Is there any reason why the above query would not return a recordset when using ADO through Excel VBA to open it ? I'm using an ODBC driver for SQL Server in my connection string.
Perhaps I'm now on the wrong forum for this question ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply