Solved

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

Posted on 2012-04-09
8
1,300 Views
Last Modified: 2012-04-10
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!
0
Comment
Question by:Rick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37825333
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
 
LVL 42

Assisted Solution

by:dqmq
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

Open in new window

0
 
LVL 13

Author Comment

by:Rick
ID: 37825385
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 42

Expert Comment

by:dqmq
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

by:Rick
ID: 37825396
dqmq,

Thank you for your response.

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

Author Comment

by:Rick
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

by:
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

Open in new window

0
 
LVL 13

Author Closing Comment

by:Rick
ID: 37828087
Thanks guys!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question