Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ Archiving Data

Posted by: ordnance1 May 19 2019, 08:12 AM

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?


Posted by: ordnance1 May 19 2019, 11:56 AM

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]

Posted by: Doug Steele May 19 2019, 12:03 PM

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)

Posted by: ordnance1 May 21 2019, 05:14 PM

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




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

Posted by: MadPiet May 21 2019, 07:13 PM

(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;

Posted by: Doug Steele May 21 2019, 07:15 PM

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?

Posted by: ordnance1 May 22 2019, 11:44 AM

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

Posted by: Doug Steele May 22 2019, 11:55 AM

Does that mean it's now working for you?

Posted by: ordnance1 May 22 2019, 12:15 PM

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

Posted by: Doug Steele May 22 2019, 01:46 PM

thumbup.gif