MS SQL Server/Need SQL Query


My table looks like this

Termid          lastHeartBeattime      TrxnCount
VITWSHCG-39   2012-12-18 18:29:08.103    10
VITWSHCG-39   2012-12-19 12:30:10.000          13
VITWSHCG-128   2012-12-27 09:13:26.487          17
VITWSHCG-128   2012-12-27 09:15:04.470          17
VITWSHCG-128   2012-12-27 10:02:35.940          22

i need to get the difference of Trxncount based on Termid

Termid          TrxnCount
VITWSHCG-39          3

Ooops. I may have sent you a partial answer. After typing in part of my response, I hit some keys wrong and BLAMMO... I was taken back to the main menu. So, if you received an answer prior to this, ignore it.

Your example answer was for VITWSHCG-39, which only has 2 rows in your sample data. For Termids with more than 2 rows, I'm going to assume you want the difference in TrxnCount values for the earliest and latest rows:
VITWSHCG-128 2012-12-27 09:13:26.487        17 <--THIS
VITWSHCG-128 2012-12-27 09:15:04.470        17
VITWSHCG-128 2012-12-27 10:02:35.940        22 <--and THIS

If that's not correct, let me know.

Also, you did not specify a version. The code below should work for any version, but let me know the version you're using and I might suggest an alternate method.

SELECT Termid,
TrxnCount =
SUM(CASE WHEN MyTable.lastHeartBeattime = MinMax.Time2 THEN TrxnCount ELSE 0 END) -
SUM(CASE WHEN MyTable.lastHeartBeattime = MinMax.Time1 THEN TrxnCount ELSE 0 END)
FROM MyTable
JOIN (SELECT Termid, Time1=MIN(lastHeartBeattime), Time2=MAX(lastHeartBeattime)
     FROM MyTable GROUP BY Termid) MinMax
  ON MinMax.Termid = MyTable.Termid

If it does not work, let me know what errors you get. Also, let me know if you have any questions about it (again, remembering that I might go a different direction once we establish what version you're using)

MS SQL Server

All Answers

Answers by Expert:

Ask Experts


David Vaughn


Performance tuning; T-SQL syntax;


Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Truman State University

©2016 All rights reserved.