0

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
user2786306
  • 221
  • 2
  • 6
  • 12

3 Answers3

1

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
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • I can see that after I execute your command the amount 2000 was distributed on the three records each of 1000 what will make it total of 3000, while it was not supposed to exceed 2000, that means it should update only first two records. – user2786306 Jun 03 '14 at 11:40
1

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;
Tomas
  • 166
  • 9
  • Your query is quite helpful but if the first REC amount is 1000, and the trnamt in the Salary table is 0 the query will return back the first rec in tbl_emi tbale into zero, but it should keep it 1000. – user2786306 Jun 04 '14 at 08:57
  • I've updated my answer. Also, I'm assuming that REC value cannot be higher than EMI value for each row, is that correct? – Tomas Jun 04 '14 at 09:51
  • Correct,the 1st REC value should stay 1000, if the trnamt in the salary table is 0. – user2786306 Jun 04 '14 at 10:14
1

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
adrianm
  • 14,468
  • 5
  • 55
  • 102