Где-то на sql.ru находил тему про то, как сделать FIFO чисто декларативным способом. Ссылку на топик почему-то не сохранил, но финальный запрос есть.
set nocount on
if object_id('tempdb..#Supplies') is not null drop table #Supplies
create table #Supplies (id int identity, dat smalldatetime, amount smallmoney)
insert #Supplies (dat, amount) values ('2002-09-01', 1000)
insert #Supplies (dat, amount) values ('2002-09-03', 800)
insert #Supplies (dat, amount) values ('2002-09-05', 1400)
insert #Supplies (dat, amount) values ('2002-09-05', 700)
if object_id('tempdb..#Payments') is not null drop table #Payments
create table #Payments (id int identity, dat smalldatetime, amount smallmoney)
--insert #Payments (dat, amount) values ('2002-08-05', 1000)
--insert #Payments (dat, amount) values ('2002-08-05', 300)
insert #Payments (dat, amount) values ('2002-09-01', 600)
--insert #Payments (dat, amount) values ('2002-09-02', 900)
insert #Payments (dat, amount) values ('2002-09-02', 1700)
insert #Payments (dat, amount) values ('2002-09-04', 1300)
insert #Payments (dat, amount) values ('2002-09-05', 100)
if object_id('tempdb..#t1') is not null drop table #t1
select
a.id,
a.sum1,
(a.tot_sum1 - a.sum1) as tot_sum1,
b.dat
into #t1
from
(
select
a.id,
max(a.amount) as sum1,
sum(isnull(b.amount, 0.00)) + max(a.amount) as tot_sum1
from
#supplies a
left outer join #supplies b on b.id < a.id
group by a.id
) as a
inner join #supplies b on b.id = a.id
if object_id('tempdb..#t2') is not null drop table #t2
select a.id, a.sum2, (a.tot_sum2 - a.sum2) as tot_sum2, b.dat into #t2 from
(select a.id, max(a.amount) as sum2, sum(isnull(b.amount, 0.00))+max(a.amount) as tot_sum2
from #payments a
left outer join #payments b on b.id < a.id
group by a.id
) as a
inner join #payments b on b.id = a.id
select *
from
(
select
a.id as id_1,
a.sum1,
a.tot_sum1,
b.id as id_2,
b.sum2,
b.tot_sum2,
case
when a.tot_sum1 > b.tot_sum2 then
case
when (b.sum2 - (a.tot_sum1 - b.tot_sum2)) > a.sum1 then a.sum1
when (b.sum2 - (a.tot_sum1 - b.tot_sum2)) <= a.sum1 then b.sum2 - (a.tot_sum1 - b.tot_sum2)
end
when a.tot_sum1 < b.tot_sum2 then
case
when ((b.tot_sum2 + b.sum2) - a.tot_sum1) >= a.sum1 then (a.sum1 - (b.tot_sum2 - a.tot_sum1))
when ((b.tot_sum2 + b.sum2) - a.tot_sum1) < a.sum1 then b.sum2
end
else
case
when a.sum1 > b.sum2 then b.sum2
when a.sum1 <= b.sum2 then a.sum1
end
end x
from
#t1 a
cross join #t2 b
) as a
where
x > 0
order by
a.id_1, a.id_2
select * from #t1
--select * from #t2
select * from #Supplies
select * from #Payments
Это конечно демо-пример, и под реальные цели его надо переделывать. Но идея интересная. Сам бы я до такого ни в жисть бы не додумался
А обычно мы считаем такие остатки уже на клиенте, например в момент вывода в отчёт.