August 12, 2016 at 2:09 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
This was faster for sure. But the issue (as I reported earlier) is it gives out the wrong date for date last redeemed. I think if that is fixed this code is good too. Thanks a lot for helping me out 🙂
August 12, 2016 at 2:09 pm
jssashank (8/12/2016)
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.
Can you please post your expected output. You may also need to post additional sample data to show the issue.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2016 at 2:48 pm
drew.allen (8/12/2016)
jssashank (8/12/2016)
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.
Can you please post your expected output. You may also need to post additional sample data to show the issue.
Drew
Sure thing.
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 )
For the data above
THE Final result would be something like
CreditID remaining Balance LastRedeemed date
1 0.00 '1/2/16'
2 0.00 '1/5/16'
3 10.00 '1/9/16'
4 50.00 Null
*Sorry I dont know how to format as a table here.
August 12, 2016 at 2:53 pm
Okay. I think I figured out where the problem is. I also realized that I didn't need one of the clauses in one of the CASE statements, so I've removed that. It probably won't make a significant difference in the speed, but it will make it ever so slightly easier to understand.
;
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,
MAX(CASE WHEN TransactionType = 'Debit' THEN Date END) OVER(PARTITION BY Customer ORDER BY tot, TransactionType ROWS UNBOUNDED PRECEDING) AS LastPaid,
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
ELSE tot - PrevTot
END AS RemainingBalance,
CASE
WHEN PaidDate IS NOT NULL THEN PaidDate
WHEN PrevTransactionType = 'Debit' THEN LastPaid
END AS LastRedeemedDate
FROM balances
WHERE TransactionType = 'Credit'
ORDER BY Customer, Date
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2016 at 5:00 pm
drew.allen (8/12/2016)
Okay. I think I figured out where the problem is. I also realized that I didn't need one of the clauses in one of the CASE statements, so I've removed that. It probably won't make a significant difference in the speed, but it will make it ever so slightly easier to understand.
;
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,
MAX(CASE WHEN TransactionType = 'Debit' THEN Date END) OVER(PARTITION BY Customer ORDER BY tot, TransactionType ROWS UNBOUNDED PRECEDING) AS LastPaid,
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
ELSE tot - PrevTot
END AS RemainingBalance,
CASE
WHEN PaidDate IS NOT NULL THEN PaidDate
WHEN PrevTransactionType = 'Debit' THEN LastPaid
END AS LastRedeemedDate
FROM balances
WHERE TransactionType = 'Credit'
ORDER BY Customer, Date
Drew
Still the same issue. Give me the date the next credit was issued. Not the last redeemed date.
:ermm: Did you get the result as I pasted for all the creditID's ?
August 12, 2016 at 10:36 pm
Except for the fact that I was selecting the Transaction ID rather than the credit ID, my query produces the exact results that you say you are expecting. Perhaps you mistyped something when you translated it to your system.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 15, 2016 at 11:16 am
drew.allen (8/12/2016)
Except for the fact that I was selecting the Transaction ID rather than the credit ID, my query produces the exact results that you say you are expecting. Perhaps you mistyped something when you translated it to your system.Drew
You are correct. It is working. Woot 🙂
Do you mind explaining your logic please. It will help me a lot.
August 15, 2016 at 11:56 am
jssashank (8/15/2016)
drew.allen (8/12/2016)
Except for the fact that I was selecting the Transaction ID rather than the credit ID, my query produces the exact results that you say you are expecting. Perhaps you mistyped something when you translated it to your system.Drew
You are correct. It is working. Woot 🙂
Do you mind explaining your logic please. It will help me a lot.
I am planning to write this up, but I wanted to make sure that it was working correctly first. It's going to take awhile to write this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 15, 2016 at 8:29 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 spoke too soon. Looks like a bug when the first transaction is a debit. It works perfectly when the first transaction is a credit.
Ex: The code fails for this:
CREATE TABLE #t1
(TransactionID INT,
Customer INT,
TransactionType VARCHAR(10),
[Date] DATE,
Amount MONEY,
CreditID INT
);
INSERT INTO #t1 VALUES(0, 1, 'Debit', '12/31/15', 50, Null);
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);
August 15, 2016 at 8:34 pm
The solution is so close. It almost feels like its one where clause that is missing.
August 15, 2016 at 8:37 pm
drew.allen (8/12/2016)
Except for the fact that I was selecting the Transaction ID rather than the credit ID, my query produces the exact results that you say you are expecting. Perhaps you mistyped something when you translated it to your system.Drew
I think your code is not producing the expected results when the first transaction is a debit. The code works perfectly when the first transaction is a Credit.
Please try with this to see what I mean
CREATE TABLE #t1
(TransactionID INT,
Customer INT,
TransactionType VARCHAR(10),
[Date] DATE,
Amount MONEY,
CreditID INT
);
INSERT INTO #t1 VALUES(0, 1, 'Debit', '12/31/15', 50, Null);
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);
I just added this condition in all your CTE's and then it worked fine.
-- Create a table with first credit date
select Customer, (date) into #first from #t1
where CreditID = 1;
-- apply this to all the CTE's since the code wors perfectly when the first transaction is a Credit.
FROM totals t join #first f
on f.customer = t.customer
and t.date >= f.date
August 16, 2016 at 8:47 am
Code that you get on the Internet should never be considered a complete solution. It's impossible to cover every single scenario in a small sample of data. That's why it's always important to understand how something works before deploying it to production, because you need to be able to support it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 16, 2016 at 8:54 am
drew.allen (8/16/2016)
Code that you get on the Internet should never be considered a complete solution. It's impossible to cover every single scenario in a small sample of data. That's why it's always important to understand how something works before deploying it to production, because you need to be able to support it.Drew
Yes sir. That was just an FYI. 🙂
August 16, 2016 at 9:40 am
can I please ask you to explain why you wish to exclude rows where the first row is a debit?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 16, 2016 at 11:25 am
J Livingston SQL (8/16/2016)
can I please ask you to explain why you wish to exclude rows where the first row is a debit?
Sure. My intention for adding that is to make the data set similar to the test data set (for which the code works) . So every set of transactions for a customer begins with a credit . I am still trying out few test cases to see if that theory works.This is a temporary fix until I figure out how the code works 🙂
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply