CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Krishna
Krishna

Reputation: 21

Find difference between two consecutive rows from a result in SQL server 2008

I want to fetch the difference in "Data" column between two consecutive rows. For example, need Row2-Row1 ( 1902.4-1899.66) , Row 3-Row 2 and so on. The difference should be stored in a new column.

+----+-------+-----------+-------------------------+----+
| Name      | Data       |meter|       Time             |
+----+-------+-----------+-------------------------+----+
| Boiler-1  |  1899.66   | 1   | 5/16/2019 12:00:00 AM  |
| Boiler-1  |  1902.4    | 1   | 5/16/2019  12:15:00 AM |
| Boiler-1  |  1908.1    | 1   | 5/16/2019  12:15:00 AM |
| Boiler-1  |  1911.7    | 6   | 5/16/2019  12:15:00 AM |
| Boiler-1  |  1926.4    | 6   | 5/16/2019  12:15:00 AM |
| 
+----+-------+-----------+-------------------------     +

Thing is the table structure that I have shown in the question, is actually obtained from two different tables. I mean, the above table is a result of a Select query to get data from two different tables. Goes like "select name, data, unitId, Timestamp from table t1 join table t2....." So is there anyway for me to calculate the difference in "data" column value between consecutive rows, without storing this above shown result into a table?

I use SQL 2008, so Lead/Lag functionality cannot be used.

Upvotes: 0

Views: 3838

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Not claiming that this is best, this is just another option in SQL SERVER < 2012. As from SQL Server 2012 its easy to do the same using LEAD and LAG default option added. Any way, for small and medium data set, you can consider this below script as well :)

Note: This is just an Idea for you.

WITH CTE(Name,Data)
AS
(
    SELECT 'Boiler-1' ,1899.66 UNION ALL
    SELECT 'Boiler-1',1902.4 UNION ALL
    SELECT 'Boiler-1',1908.1 UNION ALL
    SELECT 'Boiler-1',1911.7 UNION ALL
    SELECT 'Boiler-1',1926.4
    --Replace above select statement with your query
)
SELECT A.Name,A.Data,A.Data-ISNULL(B.Data,0) AS [Diff] 
FROM
(
    --Adding ROW_NUMBER Over (SELECT NULL) will keep the natural order
    --of your data and will just add the row number.
    SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))  RN  FROM CTE
)A
LEFT JOIN 
(
    SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))  RN FROM CTE
) B 
--Here the JOINING will take place on curent and next row for using ( = B.RN-1)
ON A.RN = B.RN-1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The equivalent in SQL Server 2008 uses apply -- and it can be expensive:

with t as (
      <your query here>
     )
select t.*,
       (t.data - tprev.data) as diff
from t outer apply
     (select top (1) tprev.*
      from t tprev
      where tprev.name = t.name and
            tprev.boiler = t.boiler and
            tprev.time < t.time
      order by tprev.time desc
     ) tprev;

This assumes that you want the previous row when the name and boiler are the same. You can adjust the correlation clause if you have different groupings in mind.

Upvotes: 1

Related Questions