Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Average query based on difference

Posted on 2012-08-15
2
Medium Priority
?
251 Views
Last Modified: 2012-08-20
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
Comment
Question by:Brickwall
2 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38299303
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
 
LVL 18

Accepted Solution

by:
deighton earned 2000 total points
ID: 38299609
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question