Retrieving data per time interval help

• georgeg 69122

  Old Hand

  Points: 312

  More actions

  February 17, 2016 at 10:51 am

  #311446

  Hi

  I have a database table that records pH values from once per second.

  I want to retrieve the data per minute and group the data so that I know how many minutes per pH value over the course of the last 24 hours.

  I Tried using the following code

  Select DateAdd(minute, DateDiff(minute, 0, DateTime), 0) AS [DAY_MINUTE], Count(*) AS [TOTAL_MINUTE], TagName, Round(Value, 2) as pH

  From INSQL.Runtime.dbo.History Where TagName = 'AT7075.PV'

  Group By DateAdd(minute, DateDiff(minute, 0, DateTime), 0), TagName, Value

  order by DateAdd(minute, DateDiff(minute, 0, DateTime), 0) desc

  but it only gives me the data for the last hour, and the pH values aren't grouped as I hoped.

  example of retrieved data

  DAY_MINUTE

  2016-02-17 08:37:00.0001AT7075.PV7.01

  2016-02-17 08:37:00.0002AT7075.PV7.01

  2016-02-17 08:37:00.0001AT7075.PV7.04

  2016-02-17 08:37:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.02

  2016-02-17 08:36:00.0001AT7075.PV7.02

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.04

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0002AT7075.PV7.05

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0002AT7075.PV7.05

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0001AT7075.PV7.05

  2016-02-17 08:36:00.0005AT7075.PV7.05

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  February 17, 2016 at 10:56 am

  #1859334

  can you please provide us with usable sample data (that covers all expected scenarios) and expected output?

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  February 17, 2016 at 11:45 am

  #1859347

  Since you have the "value" column in the group by clause, you will get a separate row returned for every value that is measured in each one-,minute interval.

  For more extensive help, please post the table layout in the form of CREATE TABLE statements, a few rows of sample data, chosen to make sure that the problem shows up, as INSERT statements, and the expected results.

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 17, 2016 at 1:04 pm

  #1859361

  There's nothing in your query that limits the results to one hour, unless the TagName has something to do with it.

  Since you're rounding your Value column, you should use the rounded value in the GROUP BY.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2