Credits and Debits

• Awesome... now I know what the question is! <g>

You're doing FIFO (a queue), but with money. So the oldest debt gets paid first, and if there's any money left, it gets applied to the next bill in another transaction. Now that I know what the problem is called, I found this article by Kathi Kellenberger... "T-SQL Window Function Speed Phreakery: The FIFO Stock Inventory Problem[/url]"

Does that answer the question? <G>

• pietlinden (8/10/2016)

Awesome... now I know what the question is! <g>

You're doing FIFO (a queue), but with money. So the oldest debt gets paid first, and if there's any money left, it gets applied to the next bill in another transaction. Now that I know what the problem is called, I found this article by Kathi Kellenberger... "T-SQL Window Function Speed Phreakery: The FIFO Stock Inventory Problem[/url]"

Does that answer the question? <G>

Thanks for pointing me in this direction. The only issue with this approach is that they only give the final value left which I can get by using sum with Unbound Preceding and just get the value in max rank() . But for my problem I need to see each credit and how much is left from it.

• Okay... Could you post what the solution looks like given the data you originally posted? (Or did you do that already?)

So you would apply the new payment to the oldest charge, and then roll the remaining credit to the next charge until there's no money "left in" the new payment (well, if you think of it like an envelope with some money in it).

• pietlinden (8/10/2016)

Okay... Could you post what the solution looks like given the data you originally posted? (Or did you do that already?)

So you would apply the new payment to the oldest charge, and then roll the remaining credit to the next charge until there's no money "left in" the new payment (well, if you think of it like an envelope with some money in it).

That is correct. The result I would be looking for is

CreditID remBalance LastRedeemed-date

1 0.00 '1/2/16'

2 0.00 '1/5/16'

3 10.00 '1/9/16'

4 50.00 Null

For this data

TransactionIDCustomerTransactionTypeDateAmountCreditID

11Credit 1/1/16-201

21Debit1/2/1620

31Credit 1/3/16-1002

41Debit1/4/1650

51Debit1/5/1650

61Credit 1/6/16-503

71Debit 1/7/1620

81Credit 1/8/16-504

91Debit 1/7/1620

• is this close?

`--code based on ChrisM fine work at http://www.sqlservercentral.com/Forums/Topic1731617-391-1.aspx`

`CREATE TABLE #t1`

`(TransactionID INT,`

` Customer INT,`

` TransactionType VARCHAR(10),`

` [Date] DATE,`

` Amount MONEY,`

` CreditID INT`

`);`

`INSERT INTO #t1 VALUES(1, 1, 'Credit', '1/1/16', -20, 1);`

`INSERT INTO #t1 VALUES(2, 1, 'Debit', '1/2/16', 20, NULL);`

`INSERT INTO #t1 VALUES(3, 1, 'Credit', '1/3/16', -100, 2);`

`INSERT INTO #t1 VALUES(4, 1, 'Debit', '1/4/16', 50, NULL);`

`INSERT INTO #t1 VALUES(5, 1, 'Debit', '1/5/16', 50, NULL);`

`INSERT INTO #t1 VALUES(6, 1, 'Credit', '1/6/16', -50, 3);`

`INSERT INTO #t1 VALUES(7, 1, 'Debit', '1/7/16', 20, NULL);`

`INSERT INTO #t1 VALUES(8, 1, 'Credit', '1/8/16', -50, 4);`

`INSERT INTO #t1 VALUES(9, 1, 'Debit', '1/9/16', 20, NULL);`

`WITH`

`Debits AS (`

`SELECT debitdate = date,`

` DebitID = TransactionID,`

` DebitAmount = Amount,`

` [from] = ISNULL(LAG([to], 1) OVER(ORDER BY TransactionID), 0),`

` [to]`

`FROM`

`(`

` SELECT *,`

` [to] = SUM(Amount) OVER(ORDER BY TransactionID)`

` FROM #T1`

` WHERE TransactionType = 'Debit'`

`) d`

`)`

`,`

`Credits as (`

`SELECT CreditId,`

` PaymentID = TransactionID,`

` CreditAmount = -Amount,`

` [from] = ISNULL(LAG([to], 1) OVER(ORDER BY TransactionID), 0),`

` [to]`

`FROM`

`(`

` SELECT *,`

` [to] = SUM(-Amount) OVER(ORDER BY TransactionID)`

` FROM #T1`

` WHERE TransactionType = 'Credit'`

`) c`

`)`

`, Results as (`

`SELECT c.CreditId,`

` debitdate,`

` Balance = CASE`

` WHEN c.[to] > d.[to]`

` THEN c.[to] - d.[to]`

` WHEN d.[to] IS NULL`

` THEN c.CreditAmount`

` ELSE 0`

` END,`

` ROW_NUMBER() OVER(PARTITION BY c.CreditId ORDER BY DebitId DESC) rn`

`FROM Debits d`

` FULL OUTER JOIN Credits c ON c.[from] < d.[to] AND c.[to] > d.[from]`

`)`

`SELECT CreditId,`

` Balance AS remaining_balnce,`

` debitdate AS last_redeemed_date`

`FROM results`

`WHERE rn = 1`

`ORDER BY creditid;`

`DROP TABLE #t1`

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• J Livingston SQL (8/11/2016)

is this close?

`--code based on ChrisM fine work at http://www.sqlservercentral.com/Forums/Topic1731617-391-1.aspx`

`CREATE TABLE #t1`

`(TransactionID INT,`

` Customer INT,`

` TransactionType VARCHAR(10),`

` [Date] DATE,`

` Amount MONEY,`

` CreditID INT`

`);`

`INSERT INTO #t1 VALUES(1, 1, 'Credit', '1/1/16', -20, 1);`

`INSERT INTO #t1 VALUES(2, 1, 'Debit', '1/2/16', 20, NULL);`

`INSERT INTO #t1 VALUES(3, 1, 'Credit', '1/3/16', -100, 2);`

`INSERT INTO #t1 VALUES(4, 1, 'Debit', '1/4/16', 50, NULL);`

`INSERT INTO #t1 VALUES(5, 1, 'Debit', '1/5/16', 50, NULL);`

`INSERT INTO #t1 VALUES(6, 1, 'Credit', '1/6/16', -50, 3);`

`INSERT INTO #t1 VALUES(7, 1, 'Debit', '1/7/16', 20, NULL);`

`INSERT INTO #t1 VALUES(8, 1, 'Credit', '1/8/16', -50, 4);`

`INSERT INTO #t1 VALUES(9, 1, 'Debit', '1/9/16', 20, NULL);`

`WITH`

`Debits AS (`

`SELECT debitdate = date,`

` DebitID = TransactionID,`

` DebitAmount = Amount,`

` [from] = ISNULL(LAG([to], 1) OVER(ORDER BY TransactionID), 0),`

` [to]`

`FROM`

`(`

` SELECT *,`

` [to] = SUM(Amount) OVER(ORDER BY TransactionID)`

` FROM #T1`

` WHERE TransactionType = 'Debit'`

`) d`

`)`

`,`

`Credits as (`

`SELECT CreditId,`

` PaymentID = TransactionID,`

` CreditAmount = -Amount,`

` [from] = ISNULL(LAG([to], 1) OVER(ORDER BY TransactionID), 0),`

` [to]`

`FROM`

`(`

` SELECT *,`

` [to] = SUM(-Amount) OVER(ORDER BY TransactionID)`

` FROM #T1`

` WHERE TransactionType = 'Credit'`

`) c`

`)`

`, Results as (`

`SELECT c.CreditId,`

` debitdate,`

` Balance = CASE`

` WHEN c.[to] > d.[to]`

` THEN c.[to] - d.[to]`

` WHEN d.[to] IS NULL`

` THEN c.CreditAmount`

` ELSE 0`

` END,`

` ROW_NUMBER() OVER(PARTITION BY c.CreditId ORDER BY DebitId DESC) rn`

`FROM Debits d`

` FULL OUTER JOIN Credits c ON c.[from] < d.[to] AND c.[to] > d.[from]`

`)`

`SELECT CreditId,`

` Balance AS remaining_balnce,`

` debitdate AS last_redeemed_date`

`FROM results`

`WHERE rn = 1`

`ORDER BY creditid;`

`DROP TABLE #t1`

Works like a Charm. Thanks a lot 🙂 you mind going over the logic once just for this one piece " FULL OUTER JOIN Credits c ON c.[from] < d.[to] AND c.[to] > d.[from]".

Appreciate you staying with this problem 🙂

• jssashank (8/11/2016)

Works like a Charm. Thanks a lot 🙂 you mind going over the logic once just for this one piece " FULL OUTER JOIN Credits c ON c.[from] < d.[to] AND c.[to] > d.[from]".

Appreciate you staying with this problem 🙂

I could be wrong but it looks like the simple "overlapping date periods" criteria. See the following article for how it works.

http://www.sqlservercentral.com/articles/T-SQL/105968/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• I found a faster solution (at least on this small dataset).

`;`

`WITH totals AS (`

`SELECT *, ABS(Amount) AS Amt, SUM(ABS(Amount)) OVER(PARTITION BY Customer, TransactionType ORDER BY Date ROWS UNBOUNDED PRECEDING ) AS tot`

`FROM #t1`

`)`

`, balances AS (`

`SELECT *, MIN(CASE WHEN TransactionType = 'Debit' THEN Date END) OVER(PARTITION BY Customer ORDER BY tot, TransactionType ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS PaidDate,`

`LAG(TransactionType) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevTransactionType,`

`LAG(Date) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevDate,`

`LAG(tot) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevTot`

`FROM totals`

`)`

`SELECT TransactionID, Customer,`

`CASE`

`WHEN PaidDate IS NOT NULL THEN 0`

`WHEN PrevTransactionType = 'Credit' THEN Amt`

`ELSE tot - PrevTot`

`END AS RemainingBalance,`

`CASE`

`WHEN PaidDate IS NOT NULL THEN PaidDate`

`WHEN PrevTransactionType = 'Debit' THEN PrevDate`

`END AS LastRedeemedDate`

`FROM balances`

`WHERE TransactionType = 'Credit'`

`ORDER BY tot, TransactionType`

If I have the time, I'll run a more complete set.

Drew

J. Drew Allen

• I did a quick comparison of J. Livingston's solution and my solution with 10,000 records. J. Livingston's solution did not account for multiple customers, whereas mine did. I also had problems ensuring that the credits and debits made sense, so I had debits may appear before credits and the amounts may not be realistic.

Here are the results.

`-- J. Livingston's query.`

` SQL Server Execution Times:`

` CPU time = 7405 ms, elapsed time = 7603 ms.`

`-- My query.`

` SQL Server Execution Times:`

` CPU time = 78 ms, elapsed time = 243 ms.`

Drew

J. Drew Allen

• drew.allen (8/11/2016)

I did a quick comparison of J. Livingston's solution and my solution with 10,000 records. J. Livingston's solution did not account for multiple customers, whereas mine did. I also had problems ensuring that the credits and debits made sense, so I had debits may appear before credits and the amounts may not be realistic.

Here are the results.

`-- J. Livingston's query.`

` SQL Server Execution Times:`

` CPU time = 7405 ms, elapsed time = 7603 ms.`

`-- My query.`

` SQL Server Execution Times:`

` CPU time = 78 ms, elapsed time = 243 ms.`

Drew

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• This is actually an improved version of the test harness. I am putting a slight edge on credits, so that they should appear 60% of the time. I also sorted the rows by date before inserting them into the table, since J. Livingston's query used TransactionID as a sort instead of creating one based on date. It's still possible for debits to appear before credits, which will throw off the calculations, but I think it will throw both queries off the same way. (I got the same exact results when I ran the queries.)

My original test harness also randomized the customer, but I hard-coded the customer here, since J. Livingston's query did not take multiple customers into account.

`CREATE TABLE #t1`

`(TransactionID INT IDENTITY,`

`Customer INT,`

`TransactionType VARCHAR(10),`

`[Date] DATE,`

`Amount MONEY,`

`CreditID INT`

`);`

`WITH E(n) AS(`

` SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)`

`)`

`, cteTally(n) AS(`

` SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n`

` FROM E a, E b, E c, E d`

`)`

`INSERT #t1(Customer, TransactionType, [Date], Amount, CreditID)`

`SELECT TOP 10000 1,`

`tt.TransactionType,`

`tt.TransactionDate,`

`s.multiplier * 300 * RAND(CHECKSUM(NEWID())),`

`CASE WHEN tt.TransactionType = 'Credit' THEN ROW_NUMBER() OVER(PARTITION BY tt.TransactionType ORDER BY tt.TransactionDate) END`

`FROM cteTally c`

`CROSS APPLY ( VALUES(CASE WHEN RAND(CHECKSUM(NEWID())) < .6 THEN 'Credit' ELSE 'Debit' END, DATEADD(DAY, CEILING(RAND(CHECKSUM(NEWID())) * DATEDIFF(DAY, '2000', '2020')), '2000') ) ) tt(TransactionType, TransactionDate)`

`INNER JOIN ( VALUES('Credit', -1), ('Debit', 1) ) s(TransactionType, multiplier)`

`ON tt.TransactionType = s.TransactionType`

`ORDER BY tt.TransactionDate`

`SET STATISTICS IO, TIME ON`

`;`

Here are the results from the last test.

`-- J. Livingston's`

` SQL Server Execution Times:`

` CPU time = 5309 ms, elapsed time = 5259 ms.`

`-- Drew Allen's`

` SQL Server Execution Times:`

` CPU time = 110 ms, elapsed time = 182 ms.`

Drew

J. Drew Allen

• drew.allen (8/12/2016)

This is actually an improved version of the test harness. I am putting a slight edge on credits, so that they should appear 60% of the time. I also sorted the rows by date before inserting them into the table, since J. Livingston's query used TransactionID as a sort instead of creating one based on date. It's still possible for debits to appear before credits, which will throw off the calculations, but I think it will throw both queries off the same way. (I got the same exact results when I ran the queries.)

My original test harness also randomized the customer, but I hard-coded the customer here, since J. Livingston's query did not take multiple customers into account.

Drew I will change my code for multiple customers and use dates as a sort.....so if you would please post your test harness accordingly it would be very much appreciated.

I dont think my code will beat yours, but in my simple tests the difference is no where as large as you get....so I am a little concerened if I have missed something.

many thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• J Livingston SQL (8/12/2016)

drew.allen (8/12/2016)

This is actually an improved version of the test harness. I am putting a slight edge on credits, so that they should appear 60% of the time. I also sorted the rows by date before inserting them into the table, since J. Livingston's query used TransactionID as a sort instead of creating one based on date. It's still possible for debits to appear before credits, which will throw off the calculations, but I think it will throw both queries off the same way. (I got the same exact results when I ran the queries.)

My original test harness also randomized the customer, but I hard-coded the customer here, since J. Livingston's query did not take multiple customers into account.

Drew I will change my code for multiple customers and use dates as a sort.....so if you would please post your test harness accordingly it would be very much appreciated.

I dont think my code will beat yours, but in my simple tests the difference is no where as large as you get....so I am a little concerened if I have missed something.

many thanks

Instead of hard-coding the 1 use the following:

`CEILING(RAND(CHECKSUM(NEWID())) * 20)`

You want to keep the multiplier relatively small, or you increase the chances that you won't have both credits and debits for each customer.

Drew

J. Drew Allen

• drew.allen (8/11/2016)

I found a faster solution (at least on this small dataset).

`;`

`WITH totals AS (`

`SELECT *, ABS(Amount) AS Amt, SUM(ABS(Amount)) OVER(PARTITION BY Customer, TransactionType ORDER BY Date ROWS UNBOUNDED PRECEDING ) AS tot`

`FROM #t1`

`)`

`, balances AS (`

`SELECT *, MIN(CASE WHEN TransactionType = 'Debit' THEN Date END) OVER(PARTITION BY Customer ORDER BY tot, TransactionType ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS PaidDate,`

`LAG(TransactionType) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevTransactionType,`

`LAG(Date) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevDate,`

`LAG(tot) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevTot`

`FROM totals`

`)`

`SELECT TransactionID, Customer,`

`CASE`

`WHEN PaidDate IS NOT NULL THEN 0`

`WHEN PrevTransactionType = 'Credit' THEN Amt`

`ELSE tot - PrevTot`

`END AS RemainingBalance,`

`CASE`

`WHEN PaidDate IS NOT NULL THEN PaidDate`

`WHEN PrevTransactionType = 'Debit' THEN PrevDate`

`END AS LastRedeemedDate`

`FROM balances`

`WHERE TransactionType = 'Credit'`

`ORDER BY tot, TransactionType`

If I have the time, I'll run a more complete set.

Drew

I tested this code. Looks like there is a bug. It shows the date the next credit is issued instead of date it was last used. Can you please check.

• jssashank (8/11/2016)

J Livingston SQL (8/11/2016)

is this close?

`--code based on ChrisM fine work at http://www.sqlservercentral.com/Forums/Topic1731617-391-1.aspx`

`CREATE TABLE #t1`

`(TransactionID INT,`

` Customer INT,`

` TransactionType VARCHAR(10),`

` [Date] DATE,`

` Amount MONEY,`

` CreditID INT`

`);`

`INSERT INTO #t1 VALUES(1, 1, 'Credit', '1/1/16', -20, 1);`

`INSERT INTO #t1 VALUES(2, 1, 'Debit', '1/2/16', 20, NULL);`

`INSERT INTO #t1 VALUES(3, 1, 'Credit', '1/3/16', -100, 2);`

`INSERT INTO #t1 VALUES(4, 1, 'Debit', '1/4/16', 50, NULL);`

`INSERT INTO #t1 VALUES(5, 1, 'Debit', '1/5/16', 50, NULL);`

`INSERT INTO #t1 VALUES(6, 1, 'Credit', '1/6/16', -50, 3);`

`INSERT INTO #t1 VALUES(7, 1, 'Debit', '1/7/16', 20, NULL);`

`INSERT INTO #t1 VALUES(8, 1, 'Credit', '1/8/16', -50, 4);`

`INSERT INTO #t1 VALUES(9, 1, 'Debit', '1/9/16', 20, NULL);`

`WITH`

`Debits AS (`

`SELECT debitdate = date,`

` DebitID = TransactionID,`

` DebitAmount = Amount,`

` [from] = ISNULL(LAG([to], 1) OVER(ORDER BY TransactionID), 0),`

` [to]`

`FROM`

`(`

` SELECT *,`

` [to] = SUM(Amount) OVER(ORDER BY TransactionID)`

` FROM #T1`

` WHERE TransactionType = 'Debit'`

`) d`

`)`

`,`

`Credits as (`

`SELECT CreditId,`

` PaymentID = TransactionID,`

` CreditAmount = -Amount,`

` [from] = ISNULL(LAG([to], 1) OVER(ORDER BY TransactionID), 0),`

` [to]`

`FROM`

`(`

` SELECT *,`

` [to] = SUM(-Amount) OVER(ORDER BY TransactionID)`

` FROM #T1`

` WHERE TransactionType = 'Credit'`

`) c`

`)`

`, Results as (`

`SELECT c.CreditId,`

` debitdate,`

` Balance = CASE`

` WHEN c.[to] > d.[to]`

` THEN c.[to] - d.[to]`

` WHEN d.[to] IS NULL`

` THEN c.CreditAmount`

` ELSE 0`

` END,`

` ROW_NUMBER() OVER(PARTITION BY c.CreditId ORDER BY DebitId DESC) rn`

`FROM Debits d`

` FULL OUTER JOIN Credits c ON c.[from] < d.[to] AND c.[to] > d.[from]`

`)`

`SELECT CreditId,`

` Balance AS remaining_balnce,`

` debitdate AS last_redeemed_date`

`FROM results`

`WHERE rn = 1`

`ORDER BY creditid;`

`DROP TABLE #t1`

Works like a Charm. Thanks a lot 🙂 you mind going over the logic once just for this one piece " FULL OUTER JOIN Credits c ON c.[from] < d.[to] AND c.[to] > d.[from]".

Appreciate you staying with this problem 🙂

I just added partition by customer_id and added customer_id's to all the selects and it works for multiple customer.

Viewing 15 posts - 16 through 30 (of 52 total)