CODEWITHSUNDEEP
sql-server
user2786306
user2786306

Reputation: 221

Updating Previous records

Please note that I am having 2 tables:

I want the trnamt from table salary to get updated into tbl_emi in the rec record, but the amount should not exceed the emi amount for each record,for example in this case the amount 2000 which is in the salary table should be added 1000 to the first record and another 1000 to the second record to the tbl_emi table

               tbl_emi
EMI      DUEDT      REC     Acno
1000    4/30/2014   0       123
1000    5/30/2014   0       123 
1000    6/30/2014   0       123 

                  slary

             Acno      Trnamt
             123         2000

Upvotes: 0

Views: 69

Answers (3)

Tomas
Tomas

Reputation: 166

this script should work on all cases, and should be quite efficient even on larger data sets. You can query intermediate temporary tables to see what I'm doing. Table #accumulated_amount_with_salary on each row has all the data it needs to calculate the distribution amount.

Edit: since i assumed REC is always 0 at the beginning, my query was not working correctly. I've updated the query by recalculating the EMI (substracting REC amount)

--drop table #accumulated_amount;
select a.emi-a.rec as emi, a.duedt, a.rec, a.acno, coalesce(sum(b.emi-b.rec),0) as emi_accumulated
into #accumulated_amount
from tbl_emi a
    left join tbl_emi b on a.acno = b.acno and b.duedt < a.duedt
group by a.emi, a.duedt, a.rec, a.acno;

--drop table #accumulated_amount_with_salary;
select a.*, s.trnamt as salary_amt
into #accumulated_amount_with_salary
from #accumulated_amount a
    inner join slary s on a.acno = s.acno;

update #accumulated_amount_with_salary
set rec = rec + case
    when salary_amt < emi_accumulated then 0
    when (salary_amt - emi_accumulated) < emi then salary_amt - emi_accumulated
    else emi
end

update t
set rec = a.rec
from tbl_emi t
    inner join #accumulated_amount_with_salary a on t.acno = a.acno and t.duedt = a.duedt;

Upvotes: 1

adrianm
adrianm

Reputation: 14736

Is AcNo + DUEDT unique for each row? Otherwise you need som kind of key

WITH RowRanges As (
    SELECT DUEDT
          ,AcNo
          ,SUM(EMI) OVER (PARTITION BY AcNo ORDER BY DUEDT ROWS UNBOUNDED PRECEDING) - EMI AS MinValue
          ,SUM(EMI) OVER (PARTITION BY AcNo ORDER BY DUEDT ROWS UNBOUNDED PRECEDING) AS MaxValue
    FROM tbl_emi
)
UPDATE tbl_emi
SET REC = CASE WHEN Trnamt < MaxValue THEN TrnAmt - MinValue
               ELSE MaxValue - MinValue
          END
FROM tbl_emi 
     INNER JOIN RowRanges
         ON RowRanges.DUEDT = tbl_emi.DUEDT
            AND RowRanges.AcNo = tbl_emi.AcNo
     INNER JOIN slary
         ON slary.AcNo = RowRanges.AcNo
WHERE RowRanges.MinValue < slary.Trnamt

Upvotes: 1

Rahul
Rahul

Reputation: 77934

You can try something like this by joining both the table on acno and updating rec with other table trnamt value. In case statement, it will check if trnamt > emi then only half of it will be assigned else full will be assigned

UPDATE
    im
SET
    rec = CASE WHEN gm.trnamt > im.emi THEN (gm.trnamt - im.emi)
          ELSE gm.trnamt END 
FROM
    tbl_emi im
    JOIN
    salary gm ON im.acno=gm.acno

Upvotes: 1

Related Questions