CODEWITHSUNDEEP
sqlsql-servert-sql
Dina
Dina

Reputation: 25

Update a column in a SQL table using the value of the same Column from the previous row

I have a table with the following layout and values,

Table Initial Layout I want the data for 'PrevMonthly Amount' column to be updated based on the following rule,

For the combination of REFNO & KEY,

Final expected result would be,

Table Result

I tried using the LAG function in SQL but I was not getting the expected the result as shown above. 

SELECT REFNO, KEY, SEQNO,  
LAG(DATE,1) OVER (PARTITION BY REFNO, KEY ORDER BY REFNO, KEY, SEQNO) as DATE_Prev  
INTO #TEMP_TABLE  
FROM MainTable  

UPDATE A SET  
A.PrevMonthlyAmount = CASE WHEN A.DATE <> B.DATE_Prev  
THEN A.PrevAmount ELSE A.PrevMonthlyAmount END              

FROM MainTable A  
JOIN #Temp_Table B  
ON A.REFNO = B.REFNO And A.KEY = B.KEY And A.SEQNO = B.SEQNO

UPDATE A SET  
A.PrevMonthlyAmount = CASE WHEN A.DATE = B.DATE_PREV  
THEN (SELECT PrevMonthlyAmount From STAGE_LARGE_BANDED Where SEQNO = A.SEQNO - 1 And REFNO = A.REFNO And KEY = A.KEY AND)  
ELSE A.PrevMonthlyAmount END                         

FROM MainTable A  
JOIN #Temp_Table B  
ON A.REFNO = B.REFNO And A.KEY = B.KEY And A.SEQNO =  B.SEQNO

Data set I have indicated in the table layout is just a sample and am working on table which would have close to few million rows in a real time scenario.

Result which I got for my query is as follows,

enter image description here

Create Table & INSERT Queries are as follows, 

    CREATE TABLE [dbo].[MAINTABLE](
        [REFNO] [int] NULL,
        [KEY]   [Int] NULL, 
        [SEQNO] [int] NULL, 
        [DATE]  [int] NULL, 
        [AMOUNT] [int] NULL,
        [PrevAMOUNT] [int] NULL,
        [PrevMonthlyAmount] [int] NULL,
     ) 

     INSERT INTO MAINTABLE Values(1000000,2,1,201801,100,0,0)
     INSERT INTO MAINTABLE Values(1000000,2,2,201801,200,100,0)
     INSERT INTO MAINTABLE Values(1000000,2,3,201802,300,200,0)
     INSERT INTO MAINTABLE Values(1000000,2,4,201802,400,300,0)
     INSERT INTO MAINTABLE Values(1000000,2,5,201802,420,400,0)
     INSERT INTO MAINTABLE Values(1000000,2,6,201803,450,400,0)

     INSERT INTO MAINTABLE Values(2000000,1,1,201801,150,0,0)
     INSERT INTO MAINTABLE Values(2000000,1,2,201801,250,150,0)
     INSERT INTO MAINTABLE Values(2000000,1,3,201801,350,250,0)

     INSERT INTO MAINTABLE Values(3000000,1,1,201801,175,0,0)
     INSERT INTO MAINTABLE Values(3000000,1,2,201802,275,175,0)
     INSERT INTO MAINTABLE Values(3000000,1,3,201803,375,275,0)

Upvotes: 0

Views: 62

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Hmmm . . . the previous monthly amount appears to be the first value for the month. So, I think this works for your data:

select t.*,
       first_value(prevamount) over (partition by refno, key, date order by seqno) as prevmonthly_amount
from t;

Here is a db<>fiddle.

Upvotes: 1

Dina
Dina

Reputation: 25

Thanks very much for all your time and inputs.

I tried few things around solving my question and found a resolution !!!

But I definitely believe there could be some better solution than this anyways...:):)

Please see below, 

CREATE TABLE [dbo].[MAINTABLE](
    [REFNO] [int] NULL,
    [KEY] [int] NULL,
    [SEQNO] [int] NULL,
    [DATE] [int] NULL,
    [AMOUNT] [int] NULL,
    [PrevAMOUNT] [int] NULL,
    [PrevMonthlyAmount] [int] NULL
) ON [PRIMARY]


 Truncate Table MAINTABLE

 INSERT INTO MAINTABLE Values(1000000,2,1,201801,100,0,0)
 INSERT INTO MAINTABLE Values(1000000,2,2,201801,200,100,0)
 INSERT INTO MAINTABLE Values(1000000,2,3,201802,300,200,0)
 INSERT INTO MAINTABLE Values(1000000,2,4,201802,400,300,0)
 INSERT INTO MAINTABLE Values(1000000,2,5,201802,420,400,0)
 INSERT INTO MAINTABLE Values(1000000,2,6,201803,450,420,0)

 INSERT INTO MAINTABLE Values(2000000,1,1,201801,150,0,0)
 INSERT INTO MAINTABLE Values(2000000,1,2,201801,250,150,0)
 INSERT INTO MAINTABLE Values(2000000,1,3,201801,350,250,0)

 INSERT INTO MAINTABLE Values(3000000,1,1,201801,175,0,0)
 INSERT INTO MAINTABLE Values(3000000,1,2,201802,275,175,0)
 INSERT INTO MAINTABLE Values(3000000,1,3,201803,375,275,0)

SELECT REFNO, [KEY], SEQNO,  
LAG(DATE,1) OVER (PARTITION BY REFNO, [KEY] ORDER BY REFNO, [KEY], SEQNO) as DATE_Prev  
INTO #TEMP_TABLE  
FROM MainTable  

UPDATE A SET  
A.PrevMonthlyAmount = 
CASE WHEN A.DATE <> B.DATE_Prev  
    THEN A.PrevAmount 
    ELSE A.PrevMonthlyAmount
 END              

FROM MainTable A  
JOIN #Temp_Table B  
ON A.REFNO = B.REFNO And A.[KEY] = B.[KEY] And A.SEQNO = B.SEQNO

SELECT A.* 
INTO #TEMP_TABLE2
FROM MAINTABLE A 
JOIN (SELECT REFNO, [KEY], [DATE], MIN(SEQNO) as FirstTransId From MAINTABLE
        GROUP BY REFNO, [KEY], [DATE]) B
ON A.REFNO = B.REFNO And A.[KEY] = B.[KEY] And A.[DATE] = B.[DATE] And A.SEQNO = B.FirstTransId


UPDATE A SET  
A.PrevMonthlyAmount = B.PrevMonthlyAmount

FROM MainTable A  
JOIN #TEMP_TABLE2 B  
ON A.REFNO = B.REFNO And A.[KEY] = B.[KEY] And A.[DATE] =  B.[DATE]  
Where A.SEQNO <> B.SEQNO

Select * from MAINTABLE

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can apply :

select t.*, t1.prevmonthly_amount
from table t outer apply
    (select top (1) t1.amount as prevmonthly_amount
     from table t1
     where t.refno = t1.refno and t.key = t1.key and
           t1.date < t.date
     order by  t1.seqno desc
    ) t1;

Upvotes: 2

Related Questions