i have screenshot of excel below: 
below scenario:
- total of stock 20, total of order=33
- fifo based on order type and
- datetime stock must shared fifo order qty=1 qty=1
i got solution site query below:
declare @tblorder table (dealercode nvarchar(50), partcode nvarchar(50), orderqty int, ordertype nvarchar(50), orderdt datetime ) insert @tblorder ( dealercode, partcode, orderqty, ordertype, orderdt) values ('d1','a',1,'hotline','2015-04-23'), ('d2','a',5,'urgent','2015-04-24'), ('d3','a',3,'normal','2015-04-25'), ('d4','a',13,'normal','2015-04-26'), ('d5','a',9,'normal','2015-04-27'), ('d6','a',2,'normal','2015-04-28'); declare @tblstock table (partcode nvarchar(50), stockqty int) insert @tblstock ( partcode, stockqty) values ('a',20); ;with ordertemp ( select ord.dealercode, ord.partcode, ord.orderqty, ord.ordertype, row_number() on (order case ord.ordertype when 'hotline' 1 when 'urgent' 2 else 3 end, ord.partcode, ord.orderdt) 'stockpriority', sto.stockqty 'initialstock',ord.orderdt @tblorder ord left outer join @tblstock sto on ord.partcode = sto.partcode) select orders.dealercode, orders.partcode, case when backlog.priorqty > orders.initialstock 0 else orders.initialstock - backlog.priorqty end 'stock', orders.orderqty, case when backlog.priorqty + orders.orderqty < orders.initialstock orders.orderqty when backlog.priorqty > orders.initialstock 0 else orders.initialstock - backlog.priorqty end 'allocated', orders.ordertype,orderdt ordertemp orders inner join ( select a.stockpriority, a.partcode, isnull(sum(b.orderqty),0) 'priorqty' ordertemp left outer join ordertemp b on a.partcode = b.partcode , a.stockpriority > b.stockpriority group a.stockpriority, a.partcode ) backlog on orders.stockpriority = backlog.stockpriority order orders.stockpriority the result:
dealercode partcode stock orderqty allocated ordertype orderdt d1 20 1 1 hotline 4/23/2015 0:00 d2 19 5 5 urgent 4/24/2015 0:00 d3 14 3 3 normal 4/25/2015 0:00 d4 11 13 11 normal 4/26/2015 0:00 d5 0 9 0 normal 4/27/2015 0:00 d6 0 2 0 normal 4/28/2015 0:00 this result correct prefer have stock share orders this:
dealercode partcode stock orderqty allocated ordertype orderdt d1 20 1 1 hotline 4/23/2015 0:00 d2 19 5 5 urgent 4/24/2015 0:00 d3 14 3 3 normal 4/25/2015 0:00 d4 11 13 5 normal 4/26/2015 0:00 d5 6 9 4 normal 4/27/2015 0:00 d6 2 2 2 normal 4/28/2015 0:00 i think should use loop in query share stock qty=1 orders parts, next until stock=0. have no idea code this...pls me out :)
you need specify how going allocate existing stock outstanding orders. can sort of see trying do, since haven't specified don't know - , until specify don't think do.
what happens if there order 10? or 1? or 100?
once can write out rule allocating stock orders should able code it. if can't write out rule, can't code it.
