|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to calculate a 5 minute running average?datetime (Sample_Date) that stores the time a temperature was read and a float (Sample_Value) that stores the temperature. I'm trying to write an UPDATE statement to calculate the average of the previously 5 minutes worth of readings and store it in another float (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all records with the same value. Update MaterialMonitoringExpanded Set Sample_Avg = ( SELECT avg(sub.Sample_Value) FROM MaterialMonitoringExpanded as sub WHERE (sub.Logging_Index = Logging_Index) AND (sub.Sample_Date >= Sample_Date - .003472) AND (sub.Sample_Date <= Sample_Date) ) WHERE (Logging_Index = 23) The .003472 represents 5 minutes (is there a better way to substract 5 minutes from a datetime?) What am doing wrong with this query? Thanks in advance Jeff. I would use the dateadd function
http://msdn2.microsoft.com/en-us/library/ms186819.aspx Update MaterialMonitoringExpanded Set Sample_Avg = ( SELECT avg(sub.Sample_Value) FROM MaterialMonitoringExpanded as sub WHERE (sub.Logging_Index = Logging_Index) AND (sub.Sample_Date >= DateAdd(Minute,-5,Sample_Date) AND (sub.Sample_Date <= Sample_Date) ) WHERE (Logging_Index = 23) Show quote "Jeff Richardson" <BobcatRidge@newsgroups.nospam> wrote in message news:uAsLcde1GHA.5048@TK2MSFTNGP05.phx.gbl... >I have a table that records temperatures every minute or so. There is a >datetime (Sample_Date) that stores the time a temperature was read and a >float (Sample_Value) that stores the temperature. > > I'm trying to write an UPDATE statement to calculate the average of the > previously 5 minutes worth of readings and store it in another float > (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all > records with the same value. > > Update MaterialMonitoringExpanded > Set Sample_Avg = ( > SELECT avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Logging_Index = Logging_Index) AND > (sub.Sample_Date >= Sample_Date - .003472) AND > (sub.Sample_Date <= Sample_Date) > ) > WHERE (Logging_Index = 23) > > The .003472 represents 5 minutes (is there a better way to substract 5 > minutes from a datetime?) > > What am doing wrong with this query? > > Thanks in advance > Jeff. > Warren,
Thanks for the help with the DateAdd function, much cleaner! Do you have any idea why this query sets all the records with the same value in Sample_Avg? I want it calc the average for the pervious 5 minutes of data, therefore the average should change with time. Thanks Jeff. Show quote "Warren Brunk" <wbr***@techintsolutions.com> wrote in message news:eO%23uHpe1GHA.2196@TK2MSFTNGP06.phx.gbl... >I would use the dateadd function > > http://msdn2.microsoft.com/en-us/library/ms186819.aspx > > Update MaterialMonitoringExpanded > Set Sample_Avg = ( > SELECT avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Logging_Index = Logging_Index) AND > (sub.Sample_Date >= DateAdd(Minute,-5,Sample_Date) > AND > (sub.Sample_Date <= Sample_Date) > ) > WHERE (Logging_Index = 23) > > > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > www.techintsolutions.com > */ > > > "Jeff Richardson" <BobcatRidge@newsgroups.nospam> wrote in message > news:uAsLcde1GHA.5048@TK2MSFTNGP05.phx.gbl... >>I have a table that records temperatures every minute or so. There is a >>datetime (Sample_Date) that stores the time a temperature was read and a >>float (Sample_Value) that stores the temperature. >> >> I'm trying to write an UPDATE statement to calculate the average of the >> previously 5 minutes worth of readings and store it in another float >> (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all >> records with the same value. >> >> Update MaterialMonitoringExpanded >> Set Sample_Avg = ( >> SELECT avg(sub.Sample_Value) >> FROM MaterialMonitoringExpanded as sub >> WHERE (sub.Logging_Index = Logging_Index) AND >> (sub.Sample_Date >= Sample_Date - .003472) AND >> (sub.Sample_Date <= Sample_Date) >> ) >> WHERE (Logging_Index = 23) >> >> The .003472 represents 5 minutes (is there a better way to substract 5 >> minutes from a datetime?) >> >> What am doing wrong with this query? >> >> Thanks in advance >> Jeff. >> > > Does this syntax work better?
Update MaterialMonitoringExpanded Set Sample_Avg = avg(sub.Sample_Value) FROM MaterialMonitoringExpanded as sub WHERE (sub.Sample_Date >= Sample_Date - .003472) AND (sub.Sample_Date <= Sample_Date) Show quote "Jeff Richardson" <BobcatRidge@newsgroups.nospam> wrote in message news:uAsLcde1GHA.5048@TK2MSFTNGP05.phx.gbl... >I have a table that records temperatures every minute or so. There is a >datetime (Sample_Date) that stores the time a temperature was read and a >float (Sample_Value) that stores the temperature. > > I'm trying to write an UPDATE statement to calculate the average of the > previously 5 minutes worth of readings and store it in another float > (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all > records with the same value. > > Update MaterialMonitoringExpanded > Set Sample_Avg = ( > SELECT avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Logging_Index = Logging_Index) AND > (sub.Sample_Date >= Sample_Date - .003472) AND > (sub.Sample_Date <= Sample_Date) > ) > WHERE (Logging_Index = 23) > > The .003472 represents 5 minutes (is there a better way to substract 5 > minutes from a datetime?) > > What am doing wrong with this query? > > Thanks in advance > Jeff. > I get the following syntax error when I try to run the query:
Msg 157, Level 15, State 1, Line 1 An aggregate may not appear in the set list of an UPDATE statement. Show quote "Warren Brunk" <wbr***@techintsolutions.com> wrote in message news:%237GKHre1GHA.4632@TK2MSFTNGP03.phx.gbl... > Does this syntax work better? > > Update MaterialMonitoringExpanded > Set Sample_Avg = avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Sample_Date >= Sample_Date - .003472) AND > (sub.Sample_Date <= Sample_Date) > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > www.techintsolutions.com > */ > > > "Jeff Richardson" <BobcatRidge@newsgroups.nospam> wrote in message > news:uAsLcde1GHA.5048@TK2MSFTNGP05.phx.gbl... >>I have a table that records temperatures every minute or so. There is a >>datetime (Sample_Date) that stores the time a temperature was read and a >>float (Sample_Value) that stores the temperature. >> >> I'm trying to write an UPDATE statement to calculate the average of the >> previously 5 minutes worth of readings and store it in another float >> (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all >> records with the same value. >> >> Update MaterialMonitoringExpanded >> Set Sample_Avg = ( >> SELECT avg(sub.Sample_Value) >> FROM MaterialMonitoringExpanded as sub >> WHERE (sub.Logging_Index = Logging_Index) AND >> (sub.Sample_Date >= Sample_Date - .003472) AND >> (sub.Sample_Date <= Sample_Date) >> ) >> WHERE (Logging_Index = 23) >> >> The .003472 represents 5 minutes (is there a better way to substract 5 >> minutes from a datetime?) >> >> What am doing wrong with this query? >> >> Thanks in advance >> Jeff. >> > > In general, you should use the DATEADD function to work with dates. Do
you need to run an UPDATE statement? Look at the following query: DECLARE @SCRATCH TABLE (mt smalldatetime, val float) INSERT INTO @Scratch SELECT '1:01', 2.33 UNION ALL SELECT '1:02', 1.97 UNION ALL SELECT '1:03', 3.33 UNION ALL SELECT '1:04', 4.6788 UNION ALL SELECT '1:05', 6.15 SELECT mt, val, (SELECT avg(val) FROM @scratch s2 WHERE s2.mt BETWEEN DATEADD(minute, -3, s1.mt) AND DATEADD(minute, -1, s1.mt)) FROM @Scratch s1 Does that help? Stu Jeff Richardson wrote: Show quote > I have a table that records temperatures every minute or so. There is a > datetime (Sample_Date) that stores the time a temperature was read and a > float (Sample_Value) that stores the temperature. > > I'm trying to write an UPDATE statement to calculate the average of the > previously 5 minutes worth of readings and store it in another float > (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all > records with the same value. > > Update MaterialMonitoringExpanded > Set Sample_Avg = ( > SELECT avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Logging_Index = Logging_Index) AND > (sub.Sample_Date >= Sample_Date - .003472) AND > (sub.Sample_Date <= Sample_Date) > ) > WHERE (Logging_Index = 23) > > The .003472 represents 5 minutes (is there a better way to substract 5 > minutes from a datetime?) > > What am doing wrong with this query? > > Thanks in advance > Jeff. Yes, I need to do an UPDATE so that I can do some additional work on the
table. Show quote "Stu" <stuart.ainswo***@gmail.com> wrote in message news:1158011061.513963.51370@b28g2000cwb.googlegroups.com... > In general, you should use the DATEADD function to work with dates. Do > you need to run an UPDATE statement? Look at the following query: > > DECLARE @SCRATCH TABLE (mt smalldatetime, val float) > > INSERT INTO @Scratch > SELECT '1:01', 2.33 > UNION ALL > SELECT '1:02', 1.97 > UNION ALL > SELECT '1:03', 3.33 > UNION ALL > SELECT '1:04', 4.6788 > UNION ALL > SELECT '1:05', 6.15 > > SELECT mt, val, (SELECT avg(val) > FROM @scratch s2 > WHERE s2.mt BETWEEN DATEADD(minute, -3, s1.mt) AND DATEADD(minute, > -1, s1.mt)) > FROM @Scratch s1 > > > Does that help? > Stu > > Jeff Richardson wrote: >> I have a table that records temperatures every minute or so. There is a >> datetime (Sample_Date) that stores the time a temperature was read and a >> float (Sample_Value) that stores the temperature. >> >> I'm trying to write an UPDATE statement to calculate the average of the >> previously 5 minutes worth of readings and store it in another float >> (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all >> records with the same value. >> >> Update MaterialMonitoringExpanded >> Set Sample_Avg = ( >> SELECT avg(sub.Sample_Value) >> FROM MaterialMonitoringExpanded as sub >> WHERE (sub.Logging_Index = Logging_Index) AND >> (sub.Sample_Date >= Sample_Date - .003472) AND >> (sub.Sample_Date <= Sample_Date) >> ) >> WHERE (Logging_Index = 23) >> >> The .003472 represents 5 minutes (is there a better way to substract 5 >> minutes from a datetime?) >> >> What am doing wrong with this query? >> >> Thanks in advance >> Jeff. > Jeff Richardson (BobcatRidge@newsgroups.nospam) writes:
Show quote > I have a table that records temperatures every minute or so. There is a There is no correlation between the subquery and the table being updated.> datetime (Sample_Date) that stores the time a temperature was read and a > float (Sample_Value) that stores the temperature. > > I'm trying to write an UPDATE statement to calculate the average of the > previously 5 minutes worth of readings and store it in another float > (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all > records with the same value. > > Update MaterialMonitoringExpanded > Set Sample_Avg = ( > SELECT avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Logging_Index = Logging_Index) AND > (sub.Sample_Date >= Sample_Date - .003472) AND > (sub.Sample_Date <= Sample_Date) > ) > WHERE (Logging_Index = 23) > > The .003472 represents 5 minutes (is there a better way to substract 5 > minutes from a datetime?) > > What am doing wrong with this query? The prefixless Logging_Index refers to the sub instance of the table, not the outer instance. If you want to want to be strictly ANSI SQL, I think you need to writ: Update MaterialMonitoringExpanded Set Sample_Avg = ( SELECT avg(sub.Sample_Value) FROM MaterialMonitoringExpanded as sub WHERE (sub.Logging_Index = MaterialMonitoringExpanded.Logging_Index) AND (sub.Sample_Date >= MaterialMonitoringExpanded.Sample_Date - .003472) AND (sub.Sample_Date <= MaterialMonitoringExpanded.Sample_Date) ) WHERE (Logging_Index = 23) Personally I would take advantage of that SQL Server permits a FROM clause and define an alias: Update MaterialMonitoringExpanded Set Sample_Avg = ( SELECT avg(sub.Sample_Value) FROM MaterialMonitoringExpanded as sub WHERE (sub.Logging_Index = main.Logging_Index) AND (sub.Sample_Date >= main.Sample_Date - .003472) AND (sub.Sample_Date <= main.Sample_Date) ) WHERE (Logging_Index = 23) FROM MaterialMonitoringExpanded main -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland,
Adding the FROM clause with an alias did the job! Thanks very much to everyone who helped. Jeff. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns983C34A2FF3FYazorman@127.0.0.1... > Jeff Richardson (BobcatRidge@newsgroups.nospam) writes: >> I have a table that records temperatures every minute or so. There is a >> datetime (Sample_Date) that stores the time a temperature was read and a >> float (Sample_Value) that stores the temperature. >> >> I'm trying to write an UPDATE statement to calculate the average of the >> previously 5 minutes worth of readings and store it in another float >> (Sample_Avg). Below is a UPDATE query that I wrote, but it updates all >> records with the same value. >> >> Update MaterialMonitoringExpanded >> Set Sample_Avg = ( >> SELECT avg(sub.Sample_Value) >> FROM MaterialMonitoringExpanded as sub >> WHERE (sub.Logging_Index = Logging_Index) AND >> (sub.Sample_Date >= Sample_Date - .003472) AND >> (sub.Sample_Date <= Sample_Date) >> ) >> WHERE (Logging_Index = 23) >> >> The .003472 represents 5 minutes (is there a better way to substract 5 >> minutes from a datetime?) >> >> What am doing wrong with this query? > > There is no correlation between the subquery and the table being updated. > The prefixless Logging_Index refers to the sub instance of the table, > not the outer instance. > > If you want to want to be strictly ANSI SQL, I think you need to writ: > > Update MaterialMonitoringExpanded > Set Sample_Avg = ( > SELECT avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Logging_Index = > MaterialMonitoringExpanded.Logging_Index) AND > (sub.Sample_Date >= > MaterialMonitoringExpanded.Sample_Date - .003472) > AND > (sub.Sample_Date <= > MaterialMonitoringExpanded.Sample_Date) > ) > WHERE (Logging_Index = 23) > > Personally I would take advantage of that SQL Server permits a FROM > clause and define an alias: > > Update MaterialMonitoringExpanded > Set Sample_Avg = ( > SELECT avg(sub.Sample_Value) > FROM MaterialMonitoringExpanded as sub > WHERE (sub.Logging_Index = main.Logging_Index) AND > (sub.Sample_Date >= main.Sample_Date - .003472) > AND > (sub.Sample_Date <= main.Sample_Date) > ) > WHERE (Logging_Index = 23) > FROM MaterialMonitoringExpanded main > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics