# Average query based on difference

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?
###### Who is Participating?

progCommented:
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

Software DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.