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?
BrickwallAsked:
Who is Participating?
 
deightonprogCommented:
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)
select C1.DAte, C1.SerialNumber, C1.Reading - C2.Reading AS IncreaseSinceYesterday
         from CTE C1 JOIN CTE C2 ON C1.SerialNumber = C2.SerialNumber 
                                    AND C1.RN = C2.RN + 1

Open in new window


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)
select C1.DAte, AVG(1.0*C1.Reading - C2.Reading) AS AvIncreaseSinceYesterday
         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

Open in new window

0
 
Habib PourfardSoftware DeveloperCommented:
This gives you average of reading for each serial number:
SELECT [Serial Number]
	 , avg(Reading)
FROM YourTable
GROUP BY [Serial Number]

Open in new window


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

SELECT [Serial Number]
	 , Max(Reading) - Min(Reading)
FROM YourTable
GROUP BY [Serial Number]

Open in new window

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.

All Courses

From novice to tech pro — start learning today.