1

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 #temptable111
CREATE TABLE #temptable111 ( [ID] nvarchar(20), [CUSTOMER] nvarchar(20), [Date] datetime, [Reference] nvarchar(10), [Amount] money )
INSERT INTO #temptable111
VALUES
( '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 #temptable111
DROP TABLE IF EXISTS #temptable111

This is what I am trying to do:

DROP TABLE IF EXISTS #temptable222
CREATE 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 #temptable222
VALUES
( '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 #temptable222
DROP 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!!!

Larnu
  • 61,056
  • 10
  • 27
  • 50
Shaun Avalon
  • 13
  • 1
  • 4
  • So are you looking to just create a delimited list? [STRING_AGG (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017). Otherwise, there are 100's (maybe 1000's) of examples on Stack Overflow of using `FOR XML PATH` and `STUFF` to generate a delimited list in SQL Server. – Larnu Oct 26 '18 at 08:36
  • No, I am trying to calculate the source income for an expense in a table of incomes and expenses. Whether it is comma delimited or not is not an issue - it can be in multiple rows or comma delimited. – Shaun Avalon Oct 26 '18 at 08:41
  • 1
    What's the linking logic meant to be? You've forgotten to specify one. Many different options are possible – Damien_The_Unbeliever Oct 26 '18 at 08:41
  • first-in-first out - so, in the example, the first to rows are incomes, then one expense. the expense is associated to the first income amount as it has a positive balance. once the first one is spent then it rolls down to the second one, and so on. – Shaun Avalon Oct 26 '18 at 08:46
  • Which version of SQL-Server? Do you ever expect a *negative* Income or a *positive* Expense? – Shnugo Oct 26 '18 at 08:56
  • there will never be a negative income or a positive expense.Microsoft SQL Server 2016 – Shaun Avalon Oct 26 '18 at 09:04
  • Use the R tool in Alteryx, (or if version 2018.3 the Python tool would work too). Reason: if you "stack" several incomes, followed by several expenses, it will take some sort of nested looping in order to know when to switch from one income "in the stack" to the next (and the next, and the next) while processing a single large expense. With the looping capabilities of Python or R, this is pretty easily coded. With raw Alteryx or T-SQL, not so easy. – johnjps111 Oct 26 '18 at 11:49
  • 1
    "never by negative income or positive expense" - famous last words. Real life is very messy and people do things like this ALL THE TIME. Unless there is a constraint on the table (if using one), it will happen eventually. Don't just assume - handle it in some fashion (ignore those rows, throw an error, etc.). – SMor Oct 26 '18 at 12:10
  • It's great that you posted sample data as DDL+DML, not so great you did the same for expected output, though... – Zohar Peled Oct 28 '18 at 08:37

0 Answers0