Блог

Пожалуйста, обратите внимание, что у меня есть 2 таблицы:

Я хочу, чтобы trnamt из таблицы salary обновлялся в tbl_emi в записи rec, но сумма не должна превышать сумму emi для каждой записи, например, в этом случае к сумме 1000, которая находится в таблице зарплат, следует добавить 500 к первой записи и еще 500 ко второй записи, чтобытаблица tbl_emi

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

              slary
         Acno      Trnamt
         123         1000
  

Я использовал приведенный ниже запрос, но он по-прежнему добавляет поле rec в tbl_emi tbale trnamt в таблице зарплат, хотя оно не должно превышать сумму emi.

выберите query1: Для создания временной таблицы для накопленного emi

 select a.emi, a.duedt, a.rec, a.acno, coalesce(sum(b.emi),0) as emi_accumulated
into #accumulated_amount5
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;
  

Запрос 2: для создания временной таблицы для суммы заработной платы

 select a.*, s.RECAMT as salary_amt
into #accumulated_amount_with_salary5
from #accumulated_amount5 a
inner join Tbl_Fin s on a.acno = s.CARDNO;
  

Обновить запрос1: для обновления суммы rec

 update #accumulated_amount_with_salary5
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
  

Запрос на обновление 2

 update t
set lastrecdate=(CAST(GETDATE() AS DATE)), rec = a.rec
from tbl_emi t
    inner join #accumulated_amount_with_salary5 a on t.acno = a.acno and t.duedt = a.duedt
    --Finance
  

Предполагая, что у вас есть функция кумулятивной суммы, это относительно просто путем вычисления кумулятивного emi значения и сравнения с trnamt :

 with toupdate as (
      select e.*, sum(emi) over (partition by acno order by duedt) as cumemi
      from tbl_emi e
     )
update toupdate
    set rec = (case when trnamt > cumemi then emi
                    else trnamt - cumemi   emi
               end)
    from toupdate join
         slary
         on toupdate.acno = slary.acno and
            toupdate.cumemi - emi < trnamt;
  

Даже без кумулятивной суммы вы все равно можете сделать то же самое, используя коррелированные подзапросы.

Редактировать:

В SQL Server 2008 вы можете выразить совокупную сумму как:

 with toupdate as (
      select e.*,
             (select sum(e2.emi)
              from tbl_emi e2
              where e2.acno = e.acno and
                    e2.duedt <= e.duedt
             )  as cumemi
      from tbl_emi e
     )
update toupdate
    set rec = (case when trnamt > cumemi then emi
                    else trnamt - cumemi   emi
               end)
    from toupdate join
         slary
         on toupdate.acno = slary.acno and
            toupdate.cumemi - emi < trnamt;
  

вы можете использовать триггер after insert для таблицы зарплат, который будет обновлять таблицу tbl_emi после каждой вставки в таблицу зарплат, для справки о том, как создавать триггеры в sql-сервере, см. http://www.codeproject.com/Articles/25600/Triggers-SQL-Server