Solved

# Average query based on difference

Posted on 2012-08-15
247 Views
I have data in a table like this

Date  |   Serial Number  |   Reading
5-1-2012| 12345|12345
5-2-2012|12345|12348

So i figured out how to get the diference between the 2 values as  3 for 5-2.

and i can run this now for all on one serial number but now i want average how much it changes with all 250 items.  So i have 250 unique seril nubers.  and the readings are all different numbers,  but the differences are relatively close.  I want to average the change between values.  ANy one know how to do this?
0
Question by:Brickwall

LVL 12

Expert Comment

This gives you average of reading for each serial number:
``````SELECT [Serial Number]
FROM YourTable
GROUP BY [Serial Number]
``````

this query gives you max difference of the smalles reading and biggest reading for each serial number:

``````SELECT [Serial Number]
FROM YourTable
GROUP BY [Serial Number]
``````
0

LVL 18

Accepted Solution

First query, by way of illustration will give you the date and serial number, then the change since yesterday for each serial number [table is called Readings and has fields date,serialnumber and reading]

``````with CTE AS (SELECT *, row_number() OVER (PARTITION BY SerialNumber ORDER BY DATE) RN FROM Readings)
from CTE C1 JOIN CTE C2 ON C1.SerialNumber = C2.SerialNumber
AND C1.RN = C2.RN + 1
``````

to get the average increase for all serial numbers on a day

``````with CTE AS (SELECT *, row_number() OVER (PARTITION BY SerialNumber ORDER BY DATE) RN FROM Readings)
from CTE C1 JOIN CTE C2 ON C1.SerialNumber = C2.SerialNumber
AND C1.RN = C2.RN + 1
GROUP BY c1.DATE
ORDER BY c1.date
``````
0

## Featured Post

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties