Associate An Income Amount to a Specific Outgoing Amount

[Shaunavalon]

I hope you are well. I have been given a very specific set of data and I need to associate outgoing financial amounts to incoming ones on a first-in-first-out basis.

I need to be able to determine in a stack-like fashion which incoming transaction an outgoing transaction was sourced from.

I have tried several approaches using T-SQL and Alteryx but to no avail just yet.

I am hoping that I can receive a few tips on this.

Here is a source table code for the data I have (example of the data):

DROP TABLE IF EXISTS #temptable111CREATE TABLE #temptable111 ( [ID] nvarchar(20), [CUSTOMER] nvarchar(20), [Date] datetime, [Reference] nvarchar(10), [Amount] money )
INSERT INTO #temptable111VALUES
( 'AC51BFB154B5', '3A9F6F2CDCE7', N'2018-10-01T01:11:27', 'Income', 20 ), 
( '88EB621F9DBB', '3A9F6F2CDCE7', N'2018-10-01T01:08:49', 'Income', 15 ), 
( '142E8D547364', '3A9F6F2CDCE7', N'2018-10-01T01:14:42', 'Expense', -5 ), 
( '08053ADEBFBD', '3A9F6F2CDCE7', N'2018-10-01T01:23:39', 'Expense', -5 ), 
( 'B447DA985EAC', '3A9F6F2CDCE7', N'2018-10-01T01:24:16', 'Expense', -5 ), 
( 'CEBFF471FCB4', '3A9F6F2CDCE7', N'2018-10-01T01:49:06', 'Expense', -10 ), 
( '01535104A357', '3A9F6F2CDCE7', N'2018-10-01T02:41:30', 'Expense', -5 ), 
( 'D8017BF08252', '3A9F6F2CDCE7', N'2018-10-01T02:56:38', 'Income', 25 ), 
( '648F777A1B8C', '3A9F6F2CDCE7', N'2018-10-01T02:58:18', 'Expense', -10 ), 
( 'C052D9B03A89', '3A9F6F2CDCE7', N'2018-10-01T03:43:21', 'Expense', -10 ), 
( '2D00A5F373CC', '3A9F6F2CDCE7', N'2018-10-01T04:01:49', 'Expense', -5 ), 
( 'C7A814618633', '3A9F6F2CDCE7', N'2018-10-01T04:47:39', 'Expense', -2.5 ), 
( '9686EA892C1A', '3A9F6F2CDCE7', N'2018-10-01T05:35:48', 'Expense', -2.5 ), 
( '70EC232B54A1', '3A9F6F2CDCE7', N'2018-10-01T05:48:42', 'Income', 25 ), 
( '69799A0E3AEC', '3A9F6F2CDCE7', N'2018-10-01T06:39:52', 'Expense', -10 ), 
( '1D13593F772C', '3A9F6F2CDCE7', N'2018-10-01T06:42:54', 'Expense', -10 ), 
( '8D92A0E946EF', '3A9F6F2CDCE7', N'2018-10-01T06:43:58', 'Income', 10 ), 
( '9DA38BB7E362', '3A9F6F2CDCE7', N'2018-10-01T07:08:03', 'Income', 10 ), 
( '3243D2EC51CB', '3A9F6F2CDCE7', N'2018-10-01T07:10:54', 'Income', 5 ), 
( 'C3F0BE00EC1D', '3A9F6F2CDCE7', N'2018-10-01T07:16:23', 'Income', 25 ), 
( '9F173122FEF4', '3A9F6F2CDCE7', N'2018-10-01T07:21:06', 'Expense', -50 ), 
( 'CABDDDEEC060', '3A9F6F2CDCE7', N'2018-10-01T07:28:59', 'Expense', -5 )

SELECT * FROM #temptable111DROP TABLE IF EXISTS #temptable111

This is what I am trying to do:

DROP TABLE IF EXISTS #temptable222CREATE TABLE #temptable222 ( [ID] nvarchar(20), [CUSTOMER] nvarchar(20), 
[Date] datetime, [Reference] nvarchar(10), [Amount] money, [Balance] money,                 
[RunTot_Spend] money, [RunTot_Purchase] money, [Source Income] 
nvarchar(max), [Source Amount] nvarchar(max) )
INSERT INTO #temptable222VALUES
( 'AC51BFB154B5', '3A9F6F2CDCE7', N'2018-10-01T01:11:27', 'Income', 20, 20, 20, 0, NULL, NULL ), 
( '88EB621F9DBB', '3A9F6F2CDCE7', N'2018-10-01T01:08:49', 'Income', 15, 35, 35, 0, NULL, NULL ), 
( '142E8D547364', '3A9F6F2CDCE7', N'2018-10-01T01:14:42', 'Expense', -5, 30, 35, -5, 'AC51BFB154B5', '5' ), 
( '08053ADEBFBD', '3A9F6F2CDCE7', N'2018-10-01T01:23:39', 'Expense', -5, 25, 35, -10, 'AC51BFB154B5', '5' ), 
( 'B447DA985EAC', '3A9F6F2CDCE7', N'2018-10-01T01:24:16', 'Expense', -5, 20, 35, -15, 'AC51BFB154B5', '5' ), 
( 'CEBFF471FCB4', '3A9F6F2CDCE7', N'2018-10-01T01:49:06', 'Expense', -10, 10, 35, -25, 'AC51BFB154B5,88EB621F9DBB', '5,5' ), 
( '01535104A357', '3A9F6F2CDCE7', N'2018-10-01T02:41:30', 'Expense', -5, 5, 35, -30, '88EB621F9DBB', '5' ), 
( 'D8017BF08252', '3A9F6F2CDCE7', N'2018-10-01T02:56:38', 'Income', 25, 30, 60, -30, NULL, NULL ), 
( '648F777A1B8C', '3A9F6F2CDCE7', N'2018-10-01T02:58:18', 'Expense', -10, 20, 60, -40, '88EB621F9DBB,D8017BF08252', '5,5' ), 
( 'C052D9B03A89', '3A9F6F2CDCE7', N'2018-10-01T03:43:21', 'Expense', -10, 10, 60, -50, 'D8017BF08252', '10' ), 
( '2D00A5F373CC', '3A9F6F2CDCE7', N'2018-10-01T04:01:49', 'Expense', -5, 5, 60, -55, 'D8017BF08252', '5' ), 
( 'C7A814618633', '3A9F6F2CDCE7', N'2018-10-01T04:47:39', 'Expense', -2.5, 2.5, 60, -57.5, 'D8017BF08252', '2.5' ), 
( '9686EA892C1A', '3A9F6F2CDCE7', N'2018-10-01T05:35:48', 'Expense', -2.5, 0, 60, -60, 'D8017BF08252', '2.5' ), 
( '70EC232B54A1', '3A9F6F2CDCE7', N'2018-10-01T05:48:42', 'Income', 25, 25, 85, -60, NULL, NULL ), 
( '69799A0E3AEC', '3A9F6F2CDCE7', N'2018-10-01T06:39:52', 'Expense', -10, 15, 85, -70, '70EC232B54A1', '10' ), 
( '1D13593F772C', '3A9F6F2CDCE7', N'2018-10-01T06:42:54', 'Expense', -10, 5, 85, -80, '70EC232B54A1', '10' ), 
( '8D92A0E946EF', '3A9F6F2CDCE7', N'2018-10-01T06:43:58', 'Income', 10, 15, 95, -80, NULL, NULL ), 
( '9DA38BB7E362', '3A9F6F2CDCE7', N'2018-10-01T07:08:03', 'Income', 10, 25, 105, -80, NULL, NULL ), 
( '3243D2EC51CB', '3A9F6F2CDCE7', N'2018-10-01T07:10:54', 'Income', 5, 30, 110, -80, NULL, NULL ), 
( 'C3F0BE00EC1D', '3A9F6F2CDCE7', N'2018-10-01T07:16:23', 'Income', 25, 55, 135, -80, NULL, NULL ), 
( '9F173122FEF4', '3A9F6F2CDCE7', N'2018-10-01T07:21:06', 'Expense', -50, 5, 135, -130, '70EC232B54A1,8D92A0E946EF,9DA38BB7E362,3243D2EC51CB,C3F0BE00EC1D', '5,10,10,5,20' ), 
( 'CABDDDEEC060', '3A9F6F2CDCE7', N'2018-10-01T07:28:59', 'Expense', -5, 0, 135, -135, 'C3F0BE00EC1D', '5' )

SELECT * FROM #temptable222DROP TABLE IF EXISTS #temptable222

So, you can see that there is a running total of the balance, incomes and expenses. This is easily doable.

What I am really struggling with is linking up the Expenses to their Incomes - as in, which income(s) a specific expense came from, and how much.

I have my example on a comma-delimited single-row method, but it can be multiple rows where there is a row for each source income for a particular expense.

I am hoping that someone can help me with this - I have been racking my brain out for hours.

Thanks so much!!!

https://stackoverflow.com/q/53004629/8054037

[NicoleJohnson · Accepted solution]

This was definitely a brain teaser! 

See attached for a solution that I believe will work for what you are trying to do... feel free to investigate/pull it apart/recreate as necessary. 

Here is a basic overview of what the iterative macro does to assign the Income ID's and Amounts to Expenses:

1. Append all eligible income records to the first expense on the list

2. Determine the income amount that could be used for each record (min of Expense or Income)

3. Create running total of income used

4. Modify running total of income used and per record income used based on capping it at the expense amount

5. Filter for only those income records that ended up getting used

6. Concatenate the Income ID and amounts used for the expense item <-- This will be your output after all records are processed

7. Take the remaining income & expense records, including any remaining amounts from income records that were used, and send them to the iterative macro output to process through the macro again.

Take a look - I added some notes & such throughout the iterative macro when you open it up, so hopefully you can see the logic that I took! I hope this helps you figure out the right solution!!

Cheers,

NJ

[jdunkerley79]

Love @NicoleJohnson approach

Heres another.

- Split the Income and Outcome into two streams

- Create a running total for both

- Next create a union of the running totals

- You can then join income and expenditure back to this sequence

- Finally some multirow formulae to compute each income used for each expenditure

I chose to sort by date rather than true FIFO order, would be an easy adjustment to have that sequence instead.

[Shaunavalon]

This iterative macro works very well. I like it a lot.

It begins to run into problems when given more than 10 or so different customers though. It seems to start taking forever to re-iterate for all the rows as when it is given a few thousand rows it slows down significantly.

[Shaunavalon]

Ascending date order for the incomes would be sensible - if it refers to the newest unspent income then it will work great.

This seems to go buggy when I add more data though (multiple customers).

It works very well on a single customer, but goes all weird when given many customers.

I appended 100 customers' worth of data here.

[Shaunavalon]

I ran this into a batch macro with an iterative setting and now it seems to give beautiful results even when running it on thousands of customers. It's amazing!