none
How to write update statement to update last meter with current meter for previous month ? RRS feed

  • 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

Answers

  • 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
    
    


    Hope it can help you.

     

     

    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
    Answerer
  • 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


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Lokesh Vij 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
    
    


    Hope it can help you.

     

     

    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