drop table #calc
drop table #t3
drop table #t4
go
create table #calc
(
	 type_data varchar(50) not null -- тип данных заказ / остатки
	,id_client int null -- клиент
	,stock_exp_date float null -- срок годности товара на остатках  например: 0.55 - осталось 55% срок годности
	,stock_kg float null -- кол-во на остатках
	,order_exp_date float null -- срок годности ограничения, ниже данного показателя нельзя отгружать
	,order_kg float null  -- кол-во заказанного
)
insert into #calc
select 'stock',		null,		0.55,	107,	null,	null	union all
select 'stock',		null,		0.78,	218,	null,	null	union all		
select 'stock',		null,		0.95,	162,	null,	null	union all			
select 'stock',		null,		0.55,	265,	null,	null	union all	
select 'orders',	1,		null,	null,	0.7,    75.95	union all
select 'orders',	2,		null,	null,	0.75,   2.45	union all
select 'orders',	3,		null,	null,	0.8,    159.25	union all
select 'orders',	4,		null,	null,	0.8,    931		union all
select 'orders',	4,		null,	null,	0.6,    2.45	union all	
select 'orders',	5,		null,	null,	0.7,    75.95	union all	
select 'orders',	6,		null,	null,	0.75,   100.45	union all	
select 'orders',	6,		null,	null,	0.8,    1229.9	union all	
select 'orders',	7,		null,	null,	0.8,    134.75	



select *
into #t3		
from #calc
where #calc.type_data='stock'

select * from #t3

select t1.id_client,
	   t1.order_exp_date,
min(t2.stock_exp_date) stock_exp_date,	
	   convert(float,0) stock_remain_after_order,   
	   t1.order_kg,


identity(int) id
into #t4
from 
#calc T1,
#calc T2

where T1.order_exp_date is not null
  and T1.order_exp_date<=T2.stock_exp_date
  group by t1.id_client,
	   t1.order_exp_date,
	   t1.order_kg


declare @id int , @id_max int

select @id=1, @id_max=MAX(id)
from #t4

while (@id<=@id_max)
begin

update #t4
set stock_remain_after_order=#t3.stock_kg - #t4.order_kg
from #t3,
#t4
where #t3.stock_exp_date=#t4.stock_exp_date and #t4.id=@id

update #t3
set stock_kg=#t4.stock_remain_after_order
from #t3,
#t4
where #t3.stock_exp_date=#t4.stock_exp_date and #t4.id=@id


set @id=@id+1
end

select * from #t4