How to update a record with data from a previous record

Transact-SQL
1

I'm trying to write a stored procedure that will update records containing missing information using data from records that were added earlier.

CREATE TABLE [dbo].[Volumes](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Timestmp] [datetime] NULL,
[MeterName] varchar NULL,
[Reading] [float] NULL,
[Rate] [float] NULL
) ON [PRIMARY]

insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:05:23.000','Meter1',10)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:05:26.000','Meter2',200)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:05:30.000','Meter3',3000)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:05:33.000','Meter4',40000)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:35:27.000','Meter1',15)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:35:31.000','Meter2',300)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:35:35.000','Meter3',3200)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 21:35:37.000','Meter4',40500)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 22:25:31.000','Meter1',25)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 22:25:35.000','Meter2',450)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 22:25:38.000','Meter3',3400)
insert into Volumes (Timestmp,MeterName,Reading) values ('2017-05-20 22:25:41.000','Meter4',42500)

The goal is to calculate the daily Rate of each row since the previous reading.

set rate = (this.reading - previous.reading) / datediff(ss, this.timestmp, previous.timestmp) * 86400

The problem is that I don't know how to go back and match up the previous reading for each meter. I've tried several methods, mostly involving using Max(ID) to find the record immediately previous to current one. Any help would be greatly appreciated. Thanks.

2

First see if this gets the results you're looking for:

with cte
  as (select id
            ,timestmp
            ,metername
            ,reading
      	    ,row_number() over(partition by metername order by timestmp) as rn
        from dbo.volumes
     )
select v.id
      ,v.timestmp
      ,v.metername
      ,v.reading
      ,(c1.reading-c2.reading)/(datediff(second,c2.timestmp,c1.timestmp)*86400)
  from dbo.volumes as v
       inner join cte as c1
               on c1.id=v.id
       inner join cte as c2
               on c2.metername=c1.metername
              and c2.rn=c1.rn-1
;

If you're satisfied with the results, do the update:

with cte
  as (select id
            ,timestmp
            ,metername
            ,reading
      	    ,row_number() over(partition by metername order by timestmp) as rn
        from dbo.volumes
     )
update v
   set v.rate=(c1.reading-c2.reading)/(datediff(second,c2.timestmp,c1.timestmp)*86400)
  from dbo.volumes as v
       inner join cte as c1
               on c1.id=v.id
       inner join cte as c2
               on c2.metername=c1.metername
              and c2.rn=c1.rn-1
;
2 Likes
3

Very nice! EXACTLY what I was looking for. I've seen WITH before but never used it, and certainly wouldn't have thought about using it here. Thank you so much!

4

On SQL Server 2012 and higher - you now have LEAD/LAG windowing functions which allow you to retrieve previous/next rows without having to rely on joining to a CTE.