July 26, 2015 at 1:28 pm
I have a transaction table with account number, sequence, date, amount, and running balance. The running balance field isn't part of the actual source data (all other fields are).
The business requirement take all accounts that currently have a credit balance and age them based on the date of the first transaction that created the credit balance state.
This is very easy when there is a single transaction that creates the credit, like this example:
AcctIDTranSeqTranDateAmountBalance
3104/24/14$216.00 $216.00
3205/19/14($83.18)$132.82
3305/19/14($109.90)$22.92
3405/19/14($1.70)$21.22
3503/17/15($21.22)$0.00
3605/21/15($21.22)($21.22)
The scenario I can't figure out is how to pick the first date when there are multiple transactions that create a credit balance, like this:
AcctIDTranSeqTranDateAmountBalance
2108/08/14$369.00 $369.00
2209/18/14($195.34)$173.66
2310/16/14($173.66)$0.00
2405/12/15($112.75)($112.75)
2506/08/15($173.66)($286.41)
This scenario can be complicated by the fact that accounts move back & forth between debit/credit, like this:
AcctIDTranSeqTranDateAmountBalance
1104/03/2014$223.00 $223.00
1204/03/2014$129.00 $352.00
1307/08/2014($90.00)$262.00
1407/08/2014($73.00)$189.00
1507/08/2014($129.00)$60.00
1608/07/2014($58.80)$1.20
1708/07/2014($69.09)($67.89)
1811/26/2014$67.89 --
1912/30/2014($67.89)($67.89)
11012/31/2014$67.89 --
11105/29/2015($67.89)($67.89)
The desired output is AcctID, CreditDate where "CreditDate" is the first date in the account's most recent instance as a credit balance:
AcctID CreditDate
15/29/15
25/12/15
35/21/15
The task is currently being done in vb by looping through the transactions for each account and storing balances/dates in variables.
I'm looking for a way to do this in SQL that will accommodate all 3 scenarios without using cursors and am hitting a wall.
In practice the table may have a few hundred thousand records for 20k or more accounts.
Any assistance is greatly appreciated!
July 26, 2015 at 2:10 pm
Sure, it can be done. Could you please post DDL and INSERT statements for your sample data?
Check the following article or the one in my signature to understand how to post it. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
That way, we won't spend time creating the data.
July 26, 2015 at 6:06 pm
I did the CREATE TABLE and INSERT part...
CREATE TABLE accounts(
AcctID INT,
TranSeq INT,
TranDate DATE,
Amount MONEY
CONSTRAINT pkAccounts PRIMARY KEY (AcctID,TranSeq));
GO
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount)
VALUES (1, 1,'04/03/2014',223.00),
(1,2,'04/03/2014',129.00),
(1,3,'07/08/2014',-90.00),
(1,4,'07/08/2014',-73.00),
(1,5,'07/08/2014',-129.00),
(1,6,'08/07/2014',-58.80),
(1,7,'08/07/2014',-69.09),
(1,8,'11/26/2014',67.89),
(1,9,'12/30/2014',-67.89),
(1,10,'12/31/2014',-67.89),
(1,11,'05/29/2015',-67.89);
Was about to answer and then I realized it was for 2005... and you can't use WINDOW functions with it.
July 26, 2015 at 6:41 pm
Window functions are available in 2005, I'm not sure why do you say they're not.
You missed the most important column in the sample data that you posted (and you included money data type :crazy:).
I'm posting 2 possible options which basically depend on the desired output.
CREATE TABLE accounts(
AcctID INT,
TranSeq INT,
TranDate DATE,
Amount DECIMAL(18,4),
Balance DECIMAL(18,4)
CONSTRAINT pkAccounts PRIMARY KEY (AcctID,TranSeq));
GO
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance)
VALUES
(3,1,'04/24/2014',216.00, 216.00 ),
(3,2,'05/19/2014',-83.18,132.82 ),
(3,3,'05/19/2014',-109.90,22.92 ),
(3,4,'05/19/2014',-1.70,21.22 ),
(3,5,'03/17/2015',-21.22,0.00 ),
(3,6,'05/21/2015',-21.22,-21.22 ),
(2,1,'08/08/2014',369.00, 369.00 ),
(2,2,'09/18/2014',-195.34,173.66 ),
(2,3,'10/16/2014',-173.66,0.00 ),
(2,4,'05/12/2015',-112.75,-112.75),
(2,5,'06/08/2015',-173.66,-286.41),
(1,1,'04/03/2014',223.00, 223.00 ),
(1,2,'04/03/2014',129.00, 352.00 ),
(1,3,'07/08/2014',-90.00,262.00 ),
(1,4,'07/08/2014',-73.00,189.00 ),
(1,5,'07/08/2014',-129.00,60.00 ),
(1,6,'08/07/2014',-58.80,1.20 ),
(1,7,'08/07/2014',-69.09,-67.89 ),
(1,8,'11/26/2014',67.89 ,0 ),
(1,9,'12/30/2014',-67.89,-67.89 ),
(1,10,'12/31/2014',67.89 ,0 ),
(1,11,'05/29/2015',-67.89,-67.89 );
WITH MaxPositives AS(
SELECT AcctID,
MAX(TranDate) MaxTranDate
FROM accounts
WHERE Balance >= 0
GROUP BY AcctID
)
SELECT AcctID,
MIN(TranDate) CreditDate
FROM accounts a
WHERE EXISTS( SELECT *
FROM MaxPositives m
WHERE a.AcctID = m.AcctID
AND a.TranDate > m.MaxTranDate)
GROUP BY AcctId
ORDER BY AcctId;
WITH MaxPositives AS(
SELECT AcctID,
MAX(TranDate) MaxTranDate
FROM accounts
WHERE Balance >= 0
GROUP BY AcctID
),
RowNums AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY AcctID ORDER BY TranDate) rn
FROM accounts a
WHERE EXISTS( SELECT *
FROM MaxPositives m
WHERE a.AcctID = m.AcctID
AND a.TranDate > m.MaxTranDate)
)
SELECT AcctID, TranSeq,tranDate,Amount, Balance
FROM RowNums
WHERE rn = 1
ORDER BY AcctId;
GO
DROP TABLE accounts
July 26, 2015 at 6:45 pm
I left the balance part out because it's (sort of) a derived column. You could use a windowing function to do a running total to get it. Just partition by the AccountNumber.
July 26, 2015 at 6:51 pm
pietlinden (7/26/2015)
I left the balance part out because it's (sort of) a derived column. You could use a windowing function to do a running total to get it. Just partition by the AccountNumber.
Yes, but that option wasn't available until 2012. Even then, that would imply a lot of calculations made each time you need the balance.
July 26, 2015 at 8:21 pm
Thank you both for your responses, and I apologize for the incomplete post (my first one). Reading the post from your signature I succeeded in violating just about everything.
As you mentioned, I'm handicapped by SQL2005. I'm pretty sure that means I can't use the "VALUES" constructor, and the "Running Balance" column is created with a correlated subquery
The DML, INSERT, and query that gets me to the point of the question:
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
GO
CREATE TABLE #tmp
(
AcctID int
,TranSeq int
,TranDate datetime
,TranAmt decimal(9,2)
,Primary Key(AcctId, TranSeq)
)
INSERT INTO
#tmp
(
AcctID
,TranSeq
,TranDate
,TranAmt
)
SELECT 1,1,'20140403',223.00 UNION ALL
SELECT 1,2,'20140403',129.00 UNION ALL
SELECT 1,3,'20140708',-90.00 UNION ALL
SELECT 1,4,'20140708',-73.00 UNION ALL
SELECT 1,5,'20140708',-129.00 UNION ALL
SELECT 1,6,'20140807',-58.80 UNION ALL
SELECT 1,7,'20140807',-69.09 UNION ALL
SELECT 1,8,'20141126',67.89 UNION ALL
SELECT 1,9,'20141230',-67.89 UNION ALL
SELECT 1,10,'20141231',67.89 UNION ALL
SELECT 1,11,'20150529',-67.89 UNION ALL
SELECT 2,1,'20140808',369.00 UNION ALL
SELECT 2,2,'20140918',-195.34 UNION ALL
SELECT 2,3,'20141016',-173.66 UNION ALL
SELECT 2,4,'20150512',-112.75 UNION ALL
SELECT 2,5,'20150608',-176.66 UNION ALL
SELECT 3,1,'20140424',216.00 UNION ALL
SELECT 3,2,'20140519',-83.18 UNION ALL
SELECT 3,3,'20140519',-109.90 UNION ALL
SELECT 3,4,'20140519',-1.70 UNION ALL
SELECT 3,5,'20150317',-21.22 UNION ALL
SELECT 3,6,'20150521',-21.22
select
t1.AcctID
,t1.TranSeq
,t1.TranDate
,t1.TranAmt
,t2.RB
from
#tmp t1
cross apply
(
select
0 + SUM(tranamt) as RB
from
#tmp
where
AcctID = t1.AcctID
and TranSeq <= t1.TranSeq
) t2
order by
t1.AcctID
I'm still sorting through the answers 2 responses back.
I appreciate your assistance!
July 26, 2015 at 8:39 pm
I'm sorry, I misread the lack of the Balance column. Right now, you´re using a triangular join which can be slower than a well written cursor. There's a technique called Quirky Update that is very fast and might help you if you have the correct conditions. Read this article and understand it before using it in production: http://www.sqlservercentral.com/articles/T-SQL/68467/
Be sure to follow the rules and the solution might come easily.
CREATE TABLE accounts(
AcctID INT,
TranSeq INT,
TranDate DATE,
Amount DECIMAL(18,4),
Balance DECIMAL(18,4)
CONSTRAINT pkAccounts PRIMARY KEY (AcctID,TranSeq));
GO
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(3,1,'04/24/2014',216.00, NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(3,2,'05/19/2014',-83.18,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(3,3,'05/19/2014',-109.90,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(3,4,'05/19/2014',-1.70,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(3,5,'03/17/2015',-21.22,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(3,6,'05/21/2015',-21.22,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(2,1,'08/08/2014',369.00, NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(2,2,'09/18/2014',-195.34,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(2,3,'10/16/2014',-173.66,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(2,4,'05/12/2015',-112.75,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(2,5,'06/08/2015',-173.66,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,1,'04/03/2014',223.00, NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,2,'04/03/2014',129.00, NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,3,'07/08/2014',-90.00,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,4,'07/08/2014',-73.00,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,5,'07/08/2014',-129.00,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,6,'08/07/2014',-58.80,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,7,'08/07/2014',-69.09,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,8,'11/26/2014',67.89 ,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,9,'12/30/2014',-67.89,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,10,'12/31/2014',67.89 ,NULL);
INSERT INTO accounts(AcctID, TranSeq,tranDate,Amount, Balance) VALUES(1,11,'05/29/2015',-67.89,NULL);
DECLARE @AcctID int,
@TranSeq int,
@Balance decimal(18, 4) = 0
--Quirky Update
UPDATE a
SET @Balance = Balance = Amount + CASE WHEN AcctID = @AcctID THEN @Balance ELSE 0 END,
@AcctID = AcctID
FROM accounts a WITH (TABLOCKX)
OPTION (MAXDOP 1);
SELECT * FROM accounts;
--Start of last credit
WITH MaxPositives AS(
SELECT AcctID,
MAX(TranDate) MaxTranDate
FROM accounts
WHERE Balance >= 0
GROUP BY AcctID
)
SELECT AcctID,
MIN(TranDate) CreditDate
FROM accounts a
WHERE EXISTS( SELECT *
FROM MaxPositives m
WHERE a.AcctID = m.AcctID
AND a.TranDate > m.MaxTranDate)
GROUP BY AcctId
ORDER BY AcctId;
WITH MaxPositives AS(
SELECT AcctID,
MAX(TranDate) MaxTranDate
FROM accounts
WHERE Balance >= 0
GROUP BY AcctID
),
RowNums AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY AcctID ORDER BY TranDate) rn
FROM accounts a
WHERE EXISTS( SELECT *
FROM MaxPositives m
WHERE a.AcctID = m.AcctID
AND a.TranDate > m.MaxTranDate)
)
SELECT AcctID, TranSeq,tranDate,Amount, Balance
FROM RowNums
WHERE rn = 1
ORDER BY AcctId;
GO
DROP TABLE accounts
August 6, 2015 at 8:59 pm
Thank you again for the assistance and for the information regarding the triangular join. Only took me a week to read Jeff's post, the re-post, and the discussions about it between work & other duties. Learned a lot in the process and appreciate the information!
August 7, 2015 at 6:55 am
ArcticEd32 (8/6/2015)
Thank you again for the assistance and for the information regarding the triangular join. Only took me a week to read Jeff's post, the re-post, and the discussions about it between work & other duties. Learned a lot in the process and appreciate the information!
That's great to know. Thank you for the feedback and be sure to ask for any other questions that you have. 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply