August 10, 2016 at 12:09 am
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>
August 10, 2016 at 1:17 pm
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.
August 10, 2016 at 5:09 pm
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).
August 10, 2016 at 5:32 pm
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
August 11, 2016 at 8:43 am
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
August 11, 2016 at 6:40 pm
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 🙂
August 11, 2016 at 8:50 pm
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
Change is inevitable... Change for the better is not.
August 11, 2016 at 9:10 pm
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
Business Intelligence Analyst
Philadelphia, PA
August 11, 2016 at 10:04 pm
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
Business Intelligence Analyst
Philadelphia, PA
August 12, 2016 at 6:24 am
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
Can you share your test harness please?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 12, 2016 at 8:35 am
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
Business Intelligence Analyst
Philadelphia, PA
August 12, 2016 at 8:44 am
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
August 12, 2016 at 9:03 am
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
Business Intelligence Analyst
Philadelphia, PA
August 12, 2016 at 1:53 pm
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.
August 12, 2016 at 2:07 pm
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)
You must be logged in to reply to this topic. Login to reply