UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Archiving Data, Any Versions    
 
   
ordnance1
post May 19 2019, 08:12 AM
Post#1



Posts: 623
Joined: 7-May 11



I am creating a data base to store data from my weather station. Currently I capture a line of data every second (86,400 lines per day) which feeds a live display of the weather. I have no need to store that level of detail for every day. At the end of the day I currently have an event that appends the prior days maximum and minimum value for each field and then deleting the data for the prior day.

What I would like to do now is create an append query that gives me the maximum and minimum values in 10 minute increments. I spent a lot of time googling this without much luck. Anyone have any thoughts on how this might be done, or a point in the right direction?

Attached File  Untitled_picture.png ( 309.65K )Number of downloads: 13
Go to the top of the page
 
ordnance1
post May 19 2019, 11:56 AM
Post#2



Posts: 623
Joined: 7-May 11



Found this online

CODE
SELECT   DATEADD(MINUTE, DATEDIFF(MINUTE, 0, aa.[RecordStamp]) / 10 * 10, 0) AS [date_truncated],
         COUNT(*) AS [records_in_interval],
         AVG(aa.[TempOut]) AS [average_value]
FROM     [dbo].[DataNow] AS aa
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, aa.[RecordStamp]) / 10 * 10, 0)
ORDER BY [date_truncated]
Go to the top of the page
 
Doug Steele
post May 19 2019, 12:03 PM
Post#3


UtterAccess VIP
Posts: 22,165
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Easiest way would likely be to create a 144 row table that contains the start and end times for each of the 10 minute periods in the day. Then, join that table to your data table using inequalities so that it groups your data table into 10 minute groups. Use the Min and Max functions to summarize the data.

Sorry I can't be more explicit, but I couldn't figure out which fields in that table shot you included would need to be used in the calculations. (Having field names like TempOut_Max, TempOut_Min and Win_Max makes me suspect that your database is not properly normalized)

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
ordnance1
post May 21 2019, 05:14 PM
Post#4



Posts: 623
Joined: 7-May 11



Okay I greatly simplified my table eliminating anything that can be calculated in a query. I created the table of times and I created a query, but I am not getting the expected results.

13:49:59 should have a max temp of 56.1 and a low of 55.6
14:09:59 should have a max temp of 56.1 and a low of 57.2
14:29:59 should have a max temp of 57.4 and a low of 57.2

Attached File  Untitled_picture.png ( 309.65K )Number of downloads: 1

Attached File  Untitled_picture.png ( 309.65K )Number of downloads: 1


CODE
SELECT        dbo.Start_End_Times.EndTime, MAX(dbo.DataNow.TempOut) AS TempOut_Max, MIN(dbo.DataNow.TempOut) AS TempOut_Min, AVG(dbo.DataNow.TempOut) AS TempOut_Avg
FROM            dbo.Start_End_Times INNER JOIN
                         dbo.DataNow ON dbo.Start_End_Times.StartTime = dbo.DataNow.RecordTime
GROUP BY dbo.Start_End_Times.EndTime
Go to the top of the page
 
MadPiet
post May 21 2019, 07:13 PM
Post#5



Posts: 3,136
Joined: 27-February 09



(no, don't archive the data, just index on date).

This seems to work. Basically, grab the date portion of the datetime. (or store date and time separately)

CODE
use tempdb;

GO
CREATE TABLE temps (
    recorddatetime    datetime not null,
    tempout decimal(4,1) not null
CONSTRAINT pkTemps PRIMARY KEY (recorddatetime)
);
GO

INSERT INTO temps (recorddatetime, tempout)
VALUES ('5/30/2019 13:30', 55.8),('5/30/2019 13:35', 56.0), ('5/30/2019 13:40',57.0);

/* 5 minute intervals */
SELECT brax.thedate
        ,brax.Bracket
        ,MIN(brax.tempout) AS MinTemp
        ,MAX(brax.tempout) AS MaxTemp
FROM
(SELECT recorddatetime
    , thedate
    , minssincemidnight
    , minsSinceMidnight / (24 * 12) AS Bracket  /* 5 minute intervals 24 hours, 60/12=5 mins) */
    , tempout
FROM
(SELECT recorddatetime
    , convert(date,recorddatetime) AS TheDate
    , datediff(minute, convert(date,recorddatetime), recorddatetime ) AS MinsSinceMidnight
    , t.tempout
FROM temps t) x) brax
GROUP BY brax.TheDate
    , brax.Bracket;
Go to the top of the page
 
Doug Steele
post May 21 2019, 07:15 PM
Post#6


UtterAccess VIP
Posts: 22,165
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Assuming DataNow.RecordTime is time only, try

CODE
SELECT dbo.Start_End_Times.EndTime, MAX(dbo.DataNow.TempOut) AS TempOut_Max, MIN(dbo.DataNow.TempOut) AS TempOut_Min, AVG(dbo.DataNow.TempOut) AS TempOut_Avg
FROM dbo.Start_End_Times INNER JOIN
  dbo.DataNow
  ON dbo.Start_End_Times.StartTime >= dbo.DataNow.RecordTime
  AND dbo.Start_End_Times.EndTime <= dbo.DataNow.RecordTime
GROUP BY dbo.Start_End_Times.EndTime

If that still doesn't work, can you provide a database with a sample of the two tables?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
ordnance1
post May 22 2019, 11:44 AM
Post#7



Posts: 623
Joined: 7-May 11



Thank you for your help. Here is the final code:

CODE
SELECT         dbo.StartEnd_Times.EndTime, MAX(dbo.DataNow.TempOut) AS Expr1
FROM            dbo.DataNow INNER JOIN
                         dbo.StartEnd_Times ON dbo.DataNow.RecordTime <= dbo.StartEnd_Times.EndTime
                         AND dbo.DataNow.RecordTime >= dbo.StartEnd_Times.StartTime
GROUP BY dbo.StartEnd_Times.EndTime
ORDER BY dbo.StartEnd_Times.EndTime
Go to the top of the page
 
Doug Steele
post May 22 2019, 11:55 AM
Post#8


UtterAccess VIP
Posts: 22,165
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Does that mean it's now working for you?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
ordnance1
post May 22 2019, 12:15 PM
Post#9



Posts: 623
Joined: 7-May 11



Absolutely.

Would never have gotten there without your help!

Thank you!

CODE
SELECT        TOP (100) PERCENT dbo.DataNow.RecordDate, dbo.StartEnd_Times.EndTime, MAX(dbo.DataNow.TempOut) AS TempOut_Max, MIN(dbo.DataNow.TempOut) AS TempOut_Min, AVG(dbo.DataNow.TempOut) AS TempOut_Avg,
                         MAX(dbo.DataNow.Wind) AS Wind_Max, MIN(dbo.DataNow.Wind) AS Wind_Min, AVG(dbo.DataNow.Wind) AS Wind_Avg,
                         MAX(dbo.DataNow.WindChill) AS WindChill_Max, MIN(dbo.DataNow.WindChill) AS WindChill_Min, AVG(dbo.DataNow.WindChill) AS WindChill_Avg,
                         MAX(dbo.DataNow.DewPoint) AS DewPoint_Max, MIN(dbo.DataNow.DewPoint) AS DewPoint_Min, AVG(dbo.DataNow.DewPoint) AS DewPoint_Avg,
                         MAX(dbo.DataNow.Barometer) AS Barometer_Max, MIN(dbo.DataNow.Barometer) AS Barometer_Min, AVG(dbo.DataNow.Barometer) AS Barometer_Avg,
                         MAX(dbo.DataNow.Humidity) AS Humidity_Max, MIN(dbo.DataNow.Humidity) AS Humidity_Min, AVG(dbo.DataNow.Humidity) AS Humidity_Avg,
                         MAX(dbo.DataNow.UV) AS UV_Max, MIN(dbo.DataNow.UV) AS UV_Min, AVG(dbo.DataNow.UV) AS UV_Avg,
                         SUM(dbo.DataNow.Rain) AS Rain_Sum,
                         MAX(dbo.DataNow.Rain_Rate_Max) AS Rain_Rate_Max_Max, Min(dbo.DataNow.Rain_Rate_Max) AS Rain_Rate_Max_Min, Avg(dbo.DataNow.Rain_Rate_Max) AS Rain_Rate_Max_Avg
FROM            dbo.DataNow INNER JOIN
                         dbo.StartEnd_Times ON dbo.DataNow.RecordTime <= dbo.StartEnd_Times.EndTime AND dbo.DataNow.RecordTime >= dbo.StartEnd_Times.StartTime
GROUP BY dbo.StartEnd_Times.EndTime, dbo.DataNow.RecordDate
ORDER BY dbo.StartEnd_Times.EndTime
Go to the top of the page
 
Doug Steele
post May 22 2019, 01:46 PM
Post#10


UtterAccess VIP
Posts: 22,165
Joined: 8-January 07
From: St. Catharines, ON (Canada)


thumbup.gif

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 09:11 AM