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

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)

Open in new window


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

Open in new window


Thank you!
LVL 13
RickAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
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

Open in new window

0
 
wdosanjosCommented:
Please try:
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

Open in new window

0
 
dqmqConnect With a Mentor Commented:
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

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RickAuthor Commented:
wdosanjos,

Thank your for your response.

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
 
dqmqCommented:
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
 
RickAuthor Commented:
dqmq,

Thank you for your response.

It seems like your solution is going to work for me.
Let me check...
0
 
RickAuthor Commented:
@37825395:

>> your tables lack the keys necessary for entity integrity

    Sorry, I was in a bit of a hurry... : )
0
 
RickAuthor Commented:
Thanks guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.