April 20, 2017 at 12:43 pm
I have a scenario where I need to get the balance from the previous records prior to account being closed. When the account is closed, the balance is zero out. Therefore, I need to get the last balance from the previous record.
I have a sample data. As you can see, account with status of "Closed" has zero balance. Every record has process date, my main query only looks for "Closed" account. If I do have "Closed" account, I want to go back to previous with status of "Open" so I can get the balance.
Sample table and data:
CREATE TABLE #Temp(
[ProcessDate] [int] NULL,
[ProcessDateShort] [Date] NULL,
[AccountNumber] [varchar](20) NULL,
[AccountStatus] [varchar](20) NULL,
[SumOpenShareBalance] [Money] NULL,
)
INSERT INTO #Temp
VALUES
(20141130, '11/30/2014','0000002561', 'Open', '16885.78'),
(20150228, '02/28/2015', '0000002561', 'Closed', '0.00'),
(20141231, '12/31/2014', '0000002561', 'Closed', '0.00'),
(20150131, '01/31/2015', '0000002561', 'Closed', '0.00')
SELECT
ProcessDate
,ProcessDateShort
,AccountNumber
,AccountStatus
,SumOpenShareBalance
FROM #Temp
WHERE ProcessDate = 20150131 AND AccountStatus = 'Closed'
My ideal result should be:
ProcessDate ProcessDateShort AccountNumber AccountStatus SumOpenShareBalance
20150131 2015-01-31 0000002561 Closed 16885.78
April 20, 2017 at 1:10 pm
Personally,I'd probably go with a solution like this:SELECT T.ProcessDate, T.ProcessDateShort,
T.AccountNumber, T.AccountStatus,
T2.SumOpenShareBalance
FROM #Temp T
CROSS APPLY (SELECT TOP 1 *
FROM #Temp ca
WHERE ca.AccountNumber = T.AccountNumber
AND ca.AccountStatus = 'Open'
ORDER BY T.ProcessDateShort DESC) T2
WHERE T.ProcessDateShort= '20150131'
AND T.AccountStatus = 'Closed';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 20, 2017 at 1:10 pm
you could add an OUTER APPLY to find the prior OPEN record:
SELECT c.ProcessDate, c.ProcessDateShort, c.AccountNumber, c.AccountStatus, l.SumOpenShareBalance
FROM #Temp c
OUTER APPLY (SELECT TOP 1 o.SumOpenShareBalance FROM #Temp o WHERE o.AccountNumber = c.AccountNumber AND o.AccountStatus = 'Open' ORDER BY ProcessDate DESC) l
WHERE c.ProcessDate = 20150131
AND c.AccountStatus = 'Closed'
edit: you beat me to it!
April 20, 2017 at 1:14 pm
Thom A - Thursday, April 20, 2017 1:10 PMPersonally,I'd probably go with a solution like this:SELECT T.ProcessDate, T.ProcessDateShort,
T.AccountNumber, T.AccountStatus,
T2.SumOpenShareBalance
FROM #Temp T
CROSS APPLY (SELECT TOP 1 *
FROM #Temp ca
WHERE ca.AccountNumber = T.AccountNumber
AND ca.AccountStatus = 'Open'
ORDER BY T.ProcessDateShort DESC) T2
WHERE T.ProcessDateShort= '20150131'
AND T.AccountStatus = 'Closed';
Thank you for your quick response.
April 20, 2017 at 1:14 pm
Chris Harshman - Thursday, April 20, 2017 1:10 PMyou could add an OUTER APPLY to find the prior OPEN record:
SELECT c.ProcessDate, c.ProcessDateShort, c.AccountNumber, c.AccountStatus, l.SumOpenShareBalance
FROM #Temp c
OUTER APPLY (SELECT TOP 1 o.SumOpenShareBalance FROM #Temp o WHERE o.AccountNumber = c.AccountNumber AND o.AccountStatus = 'Open' ORDER BY ProcessDate DESC) l
WHERE c.ProcessDate = 20150131
AND c.AccountStatus = 'Closed'edit: you beat me to it!
Thank you for your quick response.
April 20, 2017 at 3:49 pm
The previous solutions require two table scans. Here is a solution that requires only one table scan. It does require that the appropriate index (AccountNumber, ProcessDateShort DESC) be in place to prevent the very expensive sort for the MAX() determination.
;
WITH CTE AS
(
SELECT *,
MAX(
CASE
WHEN AccountStatus = 'Open'
THEN CAST(ProcessDateShort AS BINARY(5)) + CAST(SumOpenShareBalance AS BINARY(5))
END
) OVER(PARTITION BY AccountNumber ORDER BY ProcessDateShort ROWS UNBOUNDED PRECEDING) AS last_balance
FROM #Temp
)
SELECT ProcessDate, ProcessDateShort, AccountNumber, AccountStatus, CAST(SUBSTRING(last_balance, 6, 5) AS MONEY) AS SumOpenShareBalance
FROM CTE c
WHERE ProcessDateShort = '20150131'
AND c.AccountStatus = 'Closed'
I'm casting to binary rather than char, because it is locale neutral when casting the date.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 21, 2017 at 6:09 am
drew.allen - Thursday, April 20, 2017 3:49 PMThe previous solutions require two table scans. Here is a solution that requires only one table scan. It does require that the appropriate index (AccountNumber, ProcessDateShort DESC) be in place to prevent the very expensive sort for the MAX() determination.
;
WITH CTE AS
(
SELECT *,
MAX(
CASE
WHEN AccountStatus = 'Open'
THEN CAST(ProcessDateShort AS BINARY(5)) + CAST(SumOpenShareBalance AS BINARY(5))
END
) OVER(PARTITION BY AccountNumber ORDER BY ProcessDateShort ROWS UNBOUNDED PRECEDING) AS last_balance
FROM #Temp
)
SELECT ProcessDate, ProcessDateShort, AccountNumber, AccountStatus, CAST(SUBSTRING(last_balance, 6, 5) AS MONEY) AS SumOpenShareBalance
FROM CTE c
WHERE ProcessDateShort = '20150131'
AND c.AccountStatus = 'Closed'I'm casting to binary rather than char, because it is locale neutral when casting the date.
Drew
Thank you Drew for your response!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply