I have a ledger of transactions that need to be applied to one another. Inbound transactions (positive Quantity values) occur first & outbound transactions (negative Quantity values) must be applied to the positive ones in first-in, first-out order. This is the enclosed dataset CREATE TABLE #ledger ( id INT IDENTITY(1,1) PRIMARY KEY, Date Date, Produc - Pastebin.com
Can anyone point me in the right direction?
hi
could you explain how you want it done ?
example
In ideal scenario I'd like to know which ids from outbound quantity are connected to which ids from inbound quantity like this.
Though honestly, any output would be good at this point. Thanks
| inbound_id | outbound_id |
|---|---|
| 5 | 8 |
| 5 | 10 |
| 5 | 12 |
| 5 | 15 |
Probably easiest way that I can think of is to use loop to iterate each inbound transaction and match it with the outbound. For each inbound transaction store the matches in temp table #matches
CREATE TABLE #matches -- Table to hold the matches
(
Outbound_ID int,
Inbound_ID int,
Match_Qty int
)
declare @ID int
select @ID = min(ID) from #ledger where Quantity > 0;
-- Iterate through the Inbound (Quantity > 0) line
while @ID is not null
begin
with inbound as
(
select ID, [Date], ProductID, Qty = Quantity
from #ledger
where ID = @ID
),
outbound as
(
select l.ID, l.Date, l.ProductID,
Qty = -(l.Quantity + isnull(m.Match_Qty, 0)),
CumQty = sum(-Quantity) over (partition by ProductID order by ID)
from #ledger l
left join
(
select Outbound_ID, Match_Qty = sum(Match_Qty)
from #matches
group by Outbound_ID
) m on l.ID = m.Outbound_ID
where l.Quantity + isnull(m.Match_Qty, 0) < 0
)
insert into #matches (Outbound_ID, Inbound_ID, Match_Qty)
select Outbound_ID = o.ID,
Inbound_ID = i.ID,
Match_Qty = m.Match_Qty
from inbound i
inner join outbound o on i.ProductID = o.ProductID
and i.[Date] <= o.[Date]
cross apply
(
select Match_Qty = case when i.Qty >= o.CumQty
then o.Qty
when i.Qty > o.CumQty - o.Qty
then i.Qty - (o.CumQty - o.Qty)
else 0
end
) m
where m.Match_Qty > 0
select @ID = min(ID) from #ledger where Quantity > 0 and ID > @ID
end
select *
from #matches
I hadn't thought to use a loop in SQL, but that makes the most sense. I wasn't getting very far with nested CTEs. Thanks!