[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Deduct quantities from a balance but closing out oldest balance first in SQL Server 2008 SSIS

Posted on 2014-04-28
Medium Priority
439 Views
Hi all.

We have something called "Blanket Orders" which is when a client agrees to buy a certain amount of a product throughout the year. Sometimes we may get another blanket order for the same customer and for the same product:

BlanketOrder1, BlanketOrderDate = 03/01/2014, CustomerA, Product1, Quantity = 500
BlanketOrder2, BlanketOrderDate = 03/02/2014, CustomerA, Product1, Quantity = 200
BlanketOrder3, BlanketOrderDate = 04/01/2014, CustomerB, Product2, Quantity = 300

Now, the next step is to deduct quantities from the blanket order when we invoice that specific customer and product number that matches the blanket order. So if we invoiced Customer A on 03/02/2014 for Product1 a total of 100 then the SSIS should enter a new line in the Transactions table:

03/01/2014, Transaction = Blanket Order Qty, Qty = 500, BlanketOrder1
03/02/2014, Transaction = Invoice, Qty = 100, BlanketOrder1

So the new balance is 400 for BlanketOrder1, as you can see, the balance of BlanketOrder2 is not touched because BlanketOrder1 is older and it's for the same customer and product number.

Let's say the Balance for BlanketOrder1 reaches 50, and now a new invoice comes in on 03/25/2014 for 150. Then the SSIS should enter a new row in the Transactions table for BlanketOrder1 for 50 (therefore making the balance 0), and applying the remaining invoiced quantity (in this case 100) to BlanketOrder2.

03/25/2014, Transaction= Partial Invoice, Qty = 50, BlanketOrder1 (balance is now 0)
03/25/2014, Transaction = Partial Invoice, Qty = 100, BlanketOrder2 (balance is now 100)

If there is no second blanket order then the SSIS would apply the whole invoiced quantity (150) to BlanketOrder1:

03/25/2014, Transaction = Invoice, Qty = 150, BlanketOrder1 (balance now has an overage of 100).

Any idea how I can do this with a SSIS? The key is that we have to get each invoice for that customer and product number, one by one, and decide where the quantity is applied, if there is only one blanket order then apply the entire quantity to that one blanket order, but if there are more than one (for the same customer and product) then first fulfill/deduct from the oldest blanket order and then proceed to the next blanket order and so on. And sometimes it may have to split quantities for an invoice in order to make the first blanket order = 0 and then applying the rest to the other blanket orders if there are any, if not then apply the entire quantity total to the only blanket order. Would some kind of script be needed with a for loop instead of a SQL task? Any help would be greatly appreciated.

0
Question by:printmedia

LVL 66

Expert Comment

ID: 40027178
>Any idea how I can do this with a SSIS?
How exactly is this an SSIS issue?  Sounds like you have a wompload of accounting business logic, that is best handled within the database, and not by the ETL tool.
0

Author Comment

ID: 40027214
I wanted to know if there was a way to do this using SSIS. We cannot alter the accounting software database or add any of our own logic which is why we have to do it outside of it. All we can do is export the data from the accounting database.
0

LVL 35

Accepted Solution

David Todd earned 2000 total points
ID: 40028968
Hi,

If all else fails (which it often does), call a procedure from SSIS, and code what you want in the procedure.

HTH
David
0

## Featured Post

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
###### Suggested Courses
Course of the Month17 days, 20 hours left to enroll