Geek Answers Handbook

T-SQL / SQL Server: Helps Calculate Account Turnover Times to Become Positive From Negative Balances

I need help to calculate the turnaround time of any account in order to become positive with a negative daily balance. For example, the account 12345 is positive on 04/05/2013, but has a negative balance on 04/06, 04/07, 04/08 (three days), and on the fourth day it became positive. I would like to develop a query to calculate turnaround time (4 days).

Account Number Transaction Date Daily Balance 12345 4/1/2013 304 12345 4/2/2013 -78 12345 4/3/2013 -65 12345 4/4/2013 12 12345 4/5/2013 25 12345 4/6/2013 -345 12345 4/7/2013 -450 12345 4/8/2013 -650 12345 4/9/2013 105 12345 4/10/2013 110 110000 4/1/2013 150 110000 4/2/2013 -15 110000 4/3/2013 -56 110000 4/4/2013 -35 110000 4/5/2013 -15 110000 4/6/2013 106 110000 4/7/2013 500 110000 4/8/2013 -150 110000 4/9/2013 50 110000 4/10/2013 100 55544 4/1/2013 150 55544 4/2/2013 120 55544 4/3/2013 -145 55544 4/4/2013 -250 55544 4/5/2013 15 55544 4/6/2013 20 55544 4/7/2013 40 55544 4/8/2013 50 55544 4/9/2013 -10 55544 4/10/2013 60

I am trying to get the following result from the table above. The query will apply to a transaction table with thousands of rows. Therefore, I need help creating some efficient query to calculate the following results.

 Account Number Turnover time for an account to become positive 12345 3 12345 4 110000 5 110000 2 55544 3 55544 2

I am using MS SQL Server version 2008.

Yes there are zeros. and they are considered positive. I mean, when the balance turns from negative to 0 or to positive, it will be considered a turnover.

The solution provided by Fadi Hassan is the most efficient and easiest to implement. Although there is one problem with this, because when the account balance is negative at the beginning, it does not work. For example, when the balance is -110, -40, 0, 10, 90, 30, 0, -10, -20, 0. This will not work.

enter image description here

If anyone has a better solution, kindly help.

+4
source share
4 answers

Assuming version> = SQL Server 2005. There are two things to keep in mind. 1. How is the zero account balance processed? 2. The query scans the table four times. Therefore, it is not very effective.

 ;WITH cte AS( SELECT t1.AccountNumber, t1.TransactionDate, t1.DailyBalance, NextRowDailyBalance FROM #Accounts t1 CROSS APPLY (SELECT TOP 1 DailyBalance AS NextRowDailyBalance FROM #Accounts t2 WHERE t1.AccountNumber = t2.AccountNumber AND t1.TransactionDate < t2.TransactionDate ORDER BY t2.TransactionDate)t WHERE (DailyBalance < 0 AND NextRowDailyBalance>0) OR (DailyBalance > 0 AND NextRowDailyBalance < 0) ) SELECT a1.AccountNumber, DATEDIFF(Day,a1.TransactionDate,t.NRTransactionDate) + 1 AS TurnOverDate FROM cte a1 CROSS APPLY (SELECT TOP 1 TransactionDate AS NRTransactionDate FROM cte a2 WHERE a1.AccountNumber = a2.AccountNumber AND a1.TransactionDate < a2.TransactionDate ORDER BY a2.TransactionDate)t WHERE a1.DailyBalance>0
+1
source

Hope this is what you were looking for @prem (edit)

  SELECT * FROM ( SELECT a.[Account Number], a.[Transaction Date], a.[Daily Balance], DATEDIFF(dd, ISNULL( ( SELECT MAX(c.[Transaction Date]) FROM Accounts c WHERE c.[Account Number] = a.[Account Number] AND c.[Transaction Date] < a.[Transaction Date] AND c.[Daily Balance] >= 0 ), ( SELECT DATEADD(dd, -1, MIN(c.[Transaction Date])) FROM Accounts c WHERE c.[Account Number] = a.[Account Number] ) ), a.[Transaction Date]) AS dat FROM Accounts a WHERE a.[Daily Balance] >= 0 ) t WHERE dat > 1
+1
source 
 ;WITH T AS (SELECT [Account Number], [Transaction Date], [Daily Balance], MAX([Transaction Date]) OVER (PARTITION BY [Account Number]) AS FinalAccountTransaction, ROW_NUMBER() OVER (PARTITION BY [Account Number] ORDER BY [Transaction Date]) - ROW_NUMBER() OVER (PARTITION BY [Account Number], CASE WHEN [Daily Balance] < 1 THEN 1 END ORDER BY [Transaction Date]) AS Grp FROM DailyBalance) SELECT [Account Number], MIN([Transaction Date]) AS Start, DATEDIFF(DAY, MIN([Transaction Date]), MAX([Transaction Date])) + 2 AS [Turnover time] FROM T WHERE [Daily Balance] < 1 GROUP BY [Account Number], Grp, FinalAccountTransaction /*If this is the final group then it is not followed by a positive so exclude*/ HAVING MAX([Transaction Date]) <> FinalAccountTransaction ORDER BY [Account Number], Start

SQL Fiddle

+1
source

Check the solution below with the cursor, change the column names accordingly. Hope this helps you.

 -- CREATE temp table with rownum to add row numbers CREATE TABLE #TurnOverTimeTaken ( rownum INT, Account_Number INT NULL, Daily_Balance INT ) -- Insert data into the temp table with row number INSERT INTO #TurnOverTimeTaken SELECT ROW_NUMBER() OVER(PARTITION BY Account_Number ORDER BY Account_Number) AS rownum ,Account_Number ,Daily_Balance FROM TurnOverTimeTaken -- Create another temp table to display the result CREATE TABLE #Result ( Account_Number INT NULL, Total INT ) DECLARE @rownum INT DECLARE @Account_Number INT DECLARE @Daily_Balance INT DECLARE @CurrentVal INT SET @CurrentVal = 0 --Cursor starts here DECLARE CountCursor CURSOR FOR SELECT rownum, Account_Number, Daily_Balance FROM #TurnOverTimeTaken OPEN CountCursor FETCH NEXT FROM CountCursor INTO @rownum, @Account_Number, @Daily_Balance WHILE @@FETCH_STATUS = 0 BEGIN -- Check Daily_Balance is negative and rownumber is not equal to one and previous Daily_Balance > 0 IF(@Daily_Balance < 0 AND @rownum <> 1 AND (SELECT Daily_Balance FROM #TurnOverTimeTaken WHERE rownum=@rownum-1 AND Account_Number=@Account _Number) > 0) BEGIN SET @CurrentVal = @CurrentVal + 1 INSERT INTO #Result VALUES(@Account_Number, @CurrentVal) END ELSE IF(@Daily_Balance < 0) BEGIN INSERT INTO #Result VALUES(@Account_Number, @CurrentVal) END FETCH NEXT FROM CountCursor INTO @rownum, @Account_Number, @Daily_Balance END CLOSE CountCursor DEALLOCATE CountCursor --SELECT * FROM #TurnOverTimeTaken SELECT Account_Number,COUNT(Total)+1 FROM #Result GROUP BY Account_Number,Total DROP TABLE #TurnOverTimeTaken DROP TABLE #Result
0
source

More articles:


All Articles