Solved

# SQL - How distribute x amount [of money] between multiple rows

Posted on 2012-04-09
1,126 Views
Hello everyone,

This is a Microsoft SQL Server 2005 question:

Suppose I have the following 2 tables:

``````create table tblPurchaseOrders (
[PoNumber] varchar(20),
[PODate] datetime,
[POAmount] money,
[AllocatedAmount] money
)

create table tblAllocatedAmounts (
[PoNumber] varchar(20),
[AllocatedAmount] money
)

insert into tblPurchaseOrders values ('ABC', '2012-04-01 11:30:00', 1000, 0)
insert into tblPurchaseOrders values ('ABC', '2012-04-03 13:30:00', 500, 0)
insert into tblPurchaseOrders values ('DEF', '2012-04-02 12:00:00', 2000, 0)
insert into tblPurchaseOrders values ('GHI', '2012-04-03 12:15:00', 300, 0)

insert into tblAllocatedAmounts values ('ABC', 500)
insert into tblAllocatedAmounts values ('DEF', 1000)
insert into tblAllocatedAmounts values ('GHI', 300)
insert into tblAllocatedAmounts values ('ABC', 500)
insert into tblAllocatedAmounts values ('ABC', 100)
``````

How do I distribute the sum of tblAllocatedAmounts.AllocatedAmount to tblPurchaseOrders.AllocatedAmount based on PO Number and PO Date?

This is the desired output:

``````select * from tblPurchaseOrders order by ponumber, PODate asc

[PoNumber]    [PODate]                     [POAmount]  [AllocatedAmount]
ABC          2012-04-01 11:30:00.000        1000.00        1000.00
ABC          2012-04-03 13:30:00.000        500.00          100.00
DEF          2012-04-02 12:00:00.000        2000.00        1000.00
GHI          2012-04-03 12:15:00.000        300.00          300.00
``````

Thank you!
0
Question by:Rick
• 4
• 2
• 2

LVL 23

Expert Comment

ID: 37825333
``````select PoNumber, PODate, POAmount,
case
when AllocatedAmount >= AccAmount then AccAmount
when AllocatedAmount < AccAmount then POAmount - AccAmount + AllocatedAmount
else 0
end as AllocatedAmount
from (
select o.PoNumber, o.PODate, o.POAmount, a.AllocatedAmount,
(select sum(i.poamount) from tblPurchaseOrders i where i.ponumber = o.ponumber and i.id <= o.id) AccAmount
from tblPurchaseOrders o
join (select PONumber, Sum(AllocatedAmount) AllocatedAmount from tblAllocatedAmounts group by PONumber) a on a.PONumber = o.PONumber) t
order by ponumber, PODate asc
``````
0

LVL 42

Assisted Solution

dqmq earned 250 total points
ID: 37825367
Here's my go at it:

``````;with CTE as
(select poNumber, sum(AllocatedAmount) Allocated
from tblAllocatedAmounts group by poNumber)
update tblPurchaseOrders
set AllocatedAmount =
case
when
cte.allocated -
isnull((select sum(POAmount) from tblPurchaseOrders p
where p.PoNumber = PO.PoNumber
and p.PODate < PO.PODate),0)
>= PO.POAmount
then PO.POAmount
else
cte.allocated -
isnull((select sum(POAmount) from tblPurchaseOrders p
where p.PoNumber = PO.PoNumber
and p.PODate < PO.PODate),0)
end
from tblPurchaseOrders PO inner join CTE on CTE.PoNumber = PO.PONumber
``````
0

LVL 13

Author Comment

ID: 37825385
wdosanjos,

I removed the "and i.id <= o.id" on line 9 because there there is no column named "id".
This is giving me:

[PoNumber]    [PODate]                     [POAmount]  [AllocatedAmount]
ABC          2012-04-01 11:30:00.000        1000.00        600.00   <==== Should be 1,000 here ...
ABC          2012-04-03 13:30:00.000        500.00          100.00
DEF          2012-04-02 12:00:00.000        2000.00        1000.00
GHI          2012-04-03 12:15:00.000        300.00          300.00
0

LVL 42

Expert Comment

ID: 37825395
p.s.  Sometimes we get questions that are a bit contrived for the sake of simplification or privacy.  I hope that is the case here, because your tables lack the keys necessary for entity integrity.

Isn't it out of the ordinary to have the same PO on two different dates?  I should also point out that the logic fails if you get two rows for the same PO on the same date.  Note, I believe wdosanjos's introduction of an ID column is an attempt to circumvent some of these issues.
0

LVL 13

Author Comment

ID: 37825396
dqmq,

It seems like your solution is going to work for me.
Let me check...
0

LVL 13

Author Comment

ID: 37825414
@37825395:

>> your tables lack the keys necessary for entity integrity

Sorry, I was in a bit of a hurry... : )
0

LVL 23

Accepted Solution

wdosanjos earned 250 total points
ID: 37827671
Here is the corrected query. Sorry, I was experimenting a couple things and I forgot to clean up the query.
``````select PoNumber, PODate, POAmount,
case
when AllocatedAmount >= AccAmount then AccAmount
when AllocatedAmount < AccAmount then POAmount - AccAmount + AllocatedAmount
else 0
end as AllocatedAmount
from (
select o.PoNumber, o.PODate, o.POAmount, a.AllocatedAmount,
(select sum(i.poamount) from tblPurchaseOrders i where i.ponumber = o.ponumber and i.PODate <= o.PODate) AccAmount
from tblPurchaseOrders o
join (select PONumber, Sum(AllocatedAmount) AllocatedAmount from tblAllocatedAmounts group by PONumber) a on a.PONumber = o.PONumber) t
``````
0

LVL 13

Author Closing Comment

ID: 37828087
Thanks guys!
0

## Featured Post

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.