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!!!