# How to write update statement to update last meter with current meter for previous month ?

• ### Question

• Problem

How to write update statement to update last meter for month 3 with current meter for month 2 for same unitcode ?

I have table invoice as following

serial int

invdate datetime

unitcode int

month  int

year   int

CurrentMeter decimal

Lastmeter     decimal

```Serial  invdate   unitcode month year CurrentMeter lastmeter
1       2019/02/25  1250     2   2019   100          90
12      2019/03/25  1250     3   2019   305          100
22      2019/02/25  1900     2   2019   50           30
50      2019/03/25  1900     3   2019   70           50
70      2019/02/25  2050     2   2019   30           10
120     2019/03/25  2050     3   2019   55           30
30      2019/02/25  1000     2   2019   75           25
90      2019/03/25  1000     3   2019   80           75```

sample data to table invoice as above

what actually need is to

Write update statement get value of currentmeter from month 2 and year  2019

and update this value in field lastmeter for month 2 for same unitcode

as sample above i need to write update statement as following

update invoice set lastmeter for month 3 =currentmeter for month 2 where

unitcode=1250

this update statement will apply to 1000 unit code have wrong value

```Wrong Values for unitcode
Serial  invdate   unitcode month year CurrentMeter lastmeter
1       2019/02/25  1250     2   2019   100          90
12      2019/03/25  1250     3   2019   305          305
After write update statement i need result as following
correct or desired result
Serial  invdate   unitcode month year CurrentMeter lastmeter
1       2019/02/25  1250     2   2019   100          90
12      2019/03/25  1250     3   2019   305          100```

Sunday, April 14, 2019 1:24 AM

• Hi engahmedbarbary,

Would you like to update the value of lastmeter for month3 according to the value of CurrentMeter for month2 ? Please try following script.

```If Object_ID('invoice','U') Is Not Null Drop Table invoice
create table invoice
(
serial int,
invdate datetime,
unitcode int,
month  int,
year   int,
CurrentMeter decimal,
Lastmeter     decimal,
)

insert into invoice values
(1  ,'2019/02/25',1250,2,2019,100,90 ),
(12 ,'2019/03/25',1250,3,2019,305,100),
(22 ,'2019/02/25',1900,2,2019,50 ,30 ),
(50 ,'2019/03/25',1900,3,2019,70 ,50 ),
(70 ,'2019/02/25',2050,2,2019,30 ,10 ),
(120,'2019/03/25',2050,3,2019,55 ,30 ),
(30 ,'2019/02/25',1000,2,2019,75 ,25 ),
(90 ,'2019/03/25',1000,3,2019,80 ,75 )

-----SQL Server 2012
update t
set Lastmeter=PrevCurrent
from (
SELECT *,LAG(CurrentMeter,1) OVER (PARTITION BY unitcode ORDER BY [year],[month]) AS PrevCurrent
FROM invoice) t
where month=3

;with cte as
(
select * from invoice where month=2
)
update a
set a.Lastmeter=b.CurrentMeter
from invoice a join cte b on a.unitcode =b.unitcode  and a.month=b.month+1

```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, April 15, 2019 5:47 AM

### All replies

• Sorry ,  cannot test it right now

WITH cte

AS

(

SELECT MIN(CurrentMeter) cm, unitcode

FROM tbl WHERE unitcode =1250

group by unitcode

) UPDATE tbl SET lastmeter=cm FROM tbl JOIN cte ON tbl.unitcode=cte.unitcode

WHERE month=3

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Sunday, April 14, 2019 5:15 AM
• like this (assuming version 2012 or above)

```UPDATE t
SET lastmeter = PrevCurrent
FROM
(
SELECT LAG(CurrentMeter,1) OVER (PARTITION BY unitcode ORDER BY [year],[month]) AS PrevCurrent,
lastmeter
FROM invoice
)t```

if earlier versions, use

```;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY unitcode ORDER BY [year],[month]) AS Seq,unitcode,lastmeter, CurrentMeter
FROM invoice
)

UPDATE c1
SET lastmeter = c2.CurrentMeter
FROM CTE c1
INNER JOIN CTE c2
ON c2.unitcode = c1.unitcode
AND c2.Seq = c1.Seq - 1```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Proposed as answer by Sunday, April 14, 2019 6:24 AM
Sunday, April 14, 2019 5:34 AM
• Hi engahmedbarbary,

Would you like to update the value of lastmeter for month3 according to the value of CurrentMeter for month2 ? Please try following script.

```If Object_ID('invoice','U') Is Not Null Drop Table invoice
create table invoice
(
serial int,
invdate datetime,
unitcode int,
month  int,
year   int,
CurrentMeter decimal,
Lastmeter     decimal,
)

insert into invoice values
(1  ,'2019/02/25',1250,2,2019,100,90 ),
(12 ,'2019/03/25',1250,3,2019,305,100),
(22 ,'2019/02/25',1900,2,2019,50 ,30 ),
(50 ,'2019/03/25',1900,3,2019,70 ,50 ),
(70 ,'2019/02/25',2050,2,2019,30 ,10 ),
(120,'2019/03/25',2050,3,2019,55 ,30 ),
(30 ,'2019/02/25',1000,2,2019,75 ,25 ),
(90 ,'2019/03/25',1000,3,2019,80 ,75 )

-----SQL Server 2012
update t
set Lastmeter=PrevCurrent
from (
SELECT *,LAG(CurrentMeter,1) OVER (PARTITION BY unitcode ORDER BY [year],[month]) AS PrevCurrent
FROM invoice) t
where month=3

;with cte as
(
select * from invoice where month=2
)
update a
set a.Lastmeter=b.CurrentMeter
from invoice a join cte b on a.unitcode =b.unitcode  and a.month=b.month+1

```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, April 15, 2019 5:47 AM