Average Values over 10 minute intervals in a CrossTab query 1

[MattGreer]

I'm receiving data from a control system that's coming in three columns, DateTime (time stamp), InstrumentID (the transmitter that's sending the data), and Fluid (the reading from the instrument). I need to create a table that I can use to create a chart to compare trends.

Here's the query that I was able to generate with the helper, but it's averaged over an entire day. I need the table to have the average values over 10 minutes. I don't know if this will make a difference or not, but the DateTime for each InstrumentID data point doesn't necessarily match up.

I was just trying to get it to work for one transmitter first. It take a little while for the query to go through the mountain of data I have.

By accident, I managed to figure out how to get hourly values by changing the Format statement. Not sure where to go from there, however.

Here's the SQL I have so far. Your help is much appreciated!

TRANSFORM Avg([All Data].Fluid) AS AvgOfFluid
SELECT Format([DateTime],"mm/dd/yyyy hh") AS [Date]
FROM [All Data]
WHERE (((Format([DateTime],"Short Date"))>=#10/12/2013# And (Format([DateTime],"Short Date"))<#10/14/2013#) AND (([All Data].TransmitterID)="Transmitter1"))
GROUP BY Format([DateTime],"mm/dd/yyyy hh")
PIVOT [All Data].TransmitterID;

Thanks!!


Matt

 

[PHV]

Replace this (2 times)
Format([DateTime],"mm/dd/yyyy hh")
with this:
Left(Format([DateTime],"mm/dd/yyyy hh:nn"),15) & "0"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MattGreer]

I must be doing something wrong. I'm getting nine rows with the same stamp, and then a 10-minute jump and then nine more rows with the same time stamp.

TRANSFORM Avg([All Data].Fluid) AS AvgOfFluid
SELECT Left(Format([DateTime],"mm/dd/yyyy hh:nn"),15) & "0" AS [Date]
FROM [All Data]
WHERE (((Left(Format([DateTime],"mm/dd/yyyy hh:nn"),15) & "0")>=#10/22/2013#) AND ((Left(Format([DateTime],"mm/dd/yyyy hh:nn"),15) & "0")<#10/23/2013#) AND (([All Data].TransmitterID)="Transmitter1"))
GROUP BY Format([DateTime],"mm/dd/yyyy hh:nn")
PIVOT [All Data].TransmitterID;

I get this:

Date	Transmitter1
10/22/2013 00:00	98.04
10/22/2013 00:00	98.4
10/22/2013 00:00	98.85
10/22/2013 00:00	98.48
10/22/2013 00:00	97.3333333333333
10/22/2013 00:00	97.86
10/22/2013 00:00	97.5
10/22/2013 00:00	96.925
10/22/2013 00:00	96.575
10/22/2013 00:10	98.1
10/22/2013 00:10	97.68
10/22/2013 00:10	98.22
10/22/2013 00:10	98.04
10/22/2013 00:10	98.4
10/22/2013 00:10	98.1
10/22/2013 00:10	96.6
10/22/2013 00:10	97.32
10/22/2013 00:10	96.66
10/22/2013 00:20	96.1

What am I doing wrong?



Thanks!!


Matt

 

[PHV]

TRANSFORM Avg(Fluid) AS AvgOfFluid
SELECT Left(Format([DateTime],"mm/dd/yyyy hh:nn"),15) & "0" AS [Date]
FROM [All Data]
WHERE [DateTime]>=#10/22/2013# AND [DateTime]<#10/23/2013#) AND TransmitterID)="Transmitter1"
GROUP BY Left(Format([DateTime],"mm/dd/yyyy hh:nn"),15) & "0"
PIVOT TransmitterID;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MattGreer]

Wow, holy cow. Man, I cannot thank you enough.

I was trying to find more information last night on using Average but my Google-fu was lacking, I guess.

If I could impose on you a bit more to understand how this works, the power of this apparently comes from the Format statements? I understand that hh:nn gives you minute averages, and hh would just give you hourly averages. I'm assuming that the averages are controlled by the GROUP BY statement and the SELECT statement gives you the 10 minute time stamps in the first column. Is my understanding correct?

Would you be able to link to a resource that can give more information on the syntax of that format statement? I ask because if someone comes along and wants 30 minute averages, or 5 minute averages, I'd like to be able to provide that for them.

Last question, how would I modify this SQL syntax to Update a table with this information? I ask because this data is ending up in a PivotChart in Excel, and folks would be able to change the date ranges and so forth.

Again, you have helped me immensely already. I am so grateful!

Thanks!!


Matt

 

[MattGreer]

OK, I think I'm figuring this out a bit. The format statement takes the 15 left most characters and then adds a zero, thus making a 10 minute mark, so to speak. In order to do quarter hours, I would need to somehow manipulate the format statement to give me quarter hour numbers, 00, 15, 30, 45. Now... how to do that... Kinda complicated! Use some sort of ROUNDUP function or something?

Haven't looked at the Update query part yet but will after the interval is resolved.

Thanks!!


Matt