Calculating avarage difference between rows | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Calculating avarage difference between rows

Hello. I need help with the following. I have a table with the following columns id LNm Size EntryDt
———– —————————— ———— ———————–
1 master 14.7500 2005-05-28 18:00:00.280
12 master 15.7500 2006-05-29 18:00:00.280
72 master 16.7500 2006-05-30 18:00:00.490
2 servent 14.7500 2005-05-28 18:00:00.280
13 servent 15.7500 2006-05-29 18:00:00.280
73 servent 16.7500 2006-05-30 18:00:00.490
I want to calculate the difference in size between days for each LNm and then average that value. Please help
ÃžrÃ¶stur JÃ³nasson

something like this? select t1.LNm, avg(t2.Size – t1.Size)
from tbl t1
join tbl t2
on t2.EntryDt = (select min(t3.EntryDt) from tbl where t3.LNm = t1.LNm and t3.EntryDt > t1.EntryDt)
and t2.LNm = t1.LNm
group by t1.LNm
That was just about it. Thank you ever so much
If Nigel’s query doesn’t help you and if you expect the result to be 2/3 = 0.6666 instead of 2/2 = 1, then probably something like this will also work:
CREATE TABLE #t
(
ID INT
, LNm VARCHAR(10)
, Size DECIMAL(6,4)
, EntryDt DATETIME
)
INSERT INTO #t
SELECT 1, ‘master’, 14.7500, ‘2005-05-28 18:00:00.280’
UNION ALL SELECT 12, ‘master’, 15.7500, ‘2006-05-29 18:00:00.280’
UNION ALL SELECT 72, ‘master’, 16.7500, ‘2006-05-30 18:00:00.490’
UNION ALL SELECT 2, ‘servert’, 14.7500, ‘2005-05-28 18:00:00.280’
UNION ALL SELECT 13, ‘servert’, 15.7500, ‘2006-05-29 18:00:00.280’
UNION ALL SELECT 73, ‘servert’, 16.7500, ‘2006-05-30 18:00:00.490’ SELECT x.LNm, AVG(Average)
FROM
(SELECT a.LNm
, ISNULL(a.Size –
(SELECT TOP 1 b.Size
FROM#t b
WHERE b.EntryDt < a.EntryDt
ORDER BY b.EntryDt DESC ),0) AS Average
FROM #t a)x
GROUP BY LNm select t1.LNm, avg(t2.Size – t1.Size)
from #t t1
join #t t2
on t2.EntryDt = (select min(t3.EntryDt) from #t t3 where t3.LNm = t1.LNm and t3.EntryDt > t1.EntryDt)
and t2.LNm = t1.LNm
group by t1.LNm DROP TABLE #t
LNm
———- —————————————-
master .666666
servert .666666 (2 row(s) affected) LNm
———- —————————————-
master 1.000000
servert 1.000000 (2 row(s) affected)

Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Oops sorry, when I opened this thread there wasn’t your reply there. Now, just ignore my query or change it to. <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT x.LNm, AVG(Average)<br /> FROM<br /> (SELECT a.LNm<br /> , a.Size – <br /> (SELECT TOP 1 b.Size <br /> FROM#t b<br /> WHERE b.EntryDt &lt; a.EntryDt<br /> ORDER BY b.EntryDt DESC ) AS Average<br /> FROM #t a)x<br /> GROUP BY LNm<br /></font id="code"></pre id="code"><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
]]>