question

How to i get this as per below data

Auctal Table data like below GLNO LoanNo TRANSACDATE WITHDRAWAMOUNT COLLECTIONAMOUNT 1170 553 01/04/2010 25000 NULL 1170 553 10/04/2010 NULL 5000 1170 553 15/04/2010 NULL 5000 1170 553 20/04/2010 NULL 10000 Above Table data I want to llike this Glno LoanNo WithdrawDate WithdrawAmount CollectionDate CollectionAmount Balance 1170 553 01/04/2010 25000 10/04/2010 5000 20000.00 1170 553 10/04/2010 20000 15/04/2010 5000 15000.00 1170 553 15/04/2010 15000 20/04/2010 10000 5000.00

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@Usman Butt - I see that but it doesnt make sense to have a column called WithdrawAmount that, in the source data has an amount that is withdrawn and then in the output show a running total. What I am asking I suppose is: Is the 25000 in the source data a balance or a transaction?
1 Like 1 ·
·
where do the withdrawamount values of 20000 and 15000 come from? They are not in your source data.
0 Likes 0 ·
·
@FatherJack that is actually the balance carried to the next row.
0 Likes 0 ·

·

Keeping in mind that data could be huge, the quirky update could have been the fastest. For that you can visit this great article by Mr. Jeff Moden. But I have approached it in two ways i.e.

1. RecursiveCTE which could not be good for huge data
2. Use Temporary table and indexing which could be better for huge data
``````
DECLARE @table TABLE
(
GLNO INT
,LoanNo INT
,TRANSACDATE DATETIME
,WITHDRAWAMOUNT INT
,COLLECTIONAMOUNT INT
)

INSERT @table
(
[GLNO]
,[LoanNo]
,[TRANSACDATE]
,[WITHDRAWAMOUNT]
,[COLLECTIONAMOUNT]
)
SELECT 1170,    553,       '20100401',     25000,             NULL
UNION ALL SELECT 1170,    553  ,     '20100410',     NULL,              5000
UNION ALL SELECT 1170 ,   553 ,      '20100415',     NULL,              5000
UNION ALL SELECT 1170 ,   553,       '20100420',     NULL,              10000

-- RECURSIVE CTE SOLUTION
;WITH    TransactionsCTE
AS (SELECT  ROW_NUMBER() OVER ( PARTITION BY [GLNO], [LoanNo] ORDER BY [TRANSACDATE] )ROWNUM
,       [GLNO]
,       [LoanNo]
,       [TRANSACDATE]
,       SUM(ISNULL([WITHDRAWAMOUNT], 0)) [WITHDRAWAMOUNT]
,       SUM(ISNULL([COLLECTIONAMOUNT], 0))[COLLECTIONAMOUNT]
,       SUM(ISNULL([WITHDRAWAMOUNT], 0))
- SUM(ISNULL([COLLECTIONAMOUNT], 0)) TotalAmount
FROM    @table AS T
GROUP BY [GLNO]
,       [LoanNo]
,       [TRANSACDATE]
)

, CalculationCTE
AS
(
SELECT *,TotalAmount AS RunningTotal
FROM TransactionsCTE WHERE ROWNUM = 1
UNION ALL
SELECT C.*, C.TotalAmount + T.RunningTotal
FROM [TransactionsCTE] AS C
INNER JOIN CalculationCTE AS T ON C.[GLNO] = T.[GLNO]
AND C.[LoanNo] = T.[LoanNo]
AND C.[ROWNUM] = T.[ROWNUM] + 1
WHERE C.ROWNUM > 1
)

SELECT  [C].[GLNO]
,       [C].[LoanNo]
,       [C].[TRANSACDATE] WithDrawDate
,       [C].[RunningTotal] [WITHDRAWAMOUNT]
,       [T].[TRANSACDATE] CollectionDate
,       [T].[COLLECTIONAMOUNT]
,       [T].[RunningTotal] Balance
FROM CalculationCTE C
INNER JOIN [CalculationCTE] T ON C.[GLNO] = T.[GLNO]
AND C.[LoanNo] = T.[LoanNo]
AND T.[ROWNUM] = C.[ROWNUM] + 1

CREATE TABLE #Output
(
ROWNUM INT
,GLNO INT
,LoanNo INT
,TRANSACDATE DATETIME NOT NULL
,WITHDRAWAMOUNT INT
,COLLECTIONAMOUNT INT
,WithDrawAmountCalc INT
,PRIMARY KEY ( ROWNUM, GLNO, LoanNo )
)

INSERT  [#Output]
(
[ROWNUM]
,[GLNO]
,[LoanNo]
,[TRANSACDATE]
,[WITHDRAWAMOUNT]
,[COLLECTIONAMOUNT]
,[WithDrawAmountCalc]
)
SELECT  ROW_NUMBER() OVER ( PARTITION BY [GLNO], [LoanNo] ORDER BY [TRANSACDATE] )
,       [GLNO]
,       [LoanNo]
,       [TRANSACDATE]
,       SUM(ISNULL([WITHDRAWAMOUNT], 0))
,       SUM(ISNULL([COLLECTIONAMOUNT], 0))
,       SUM(ISNULL([WITHDRAWAMOUNT], 0))
- SUM(ISNULL([COLLECTIONAMOUNT], 0))
FROM    @table AS T
GROUP BY [GLNO]
,       [LoanNo]
,       [TRANSACDATE]

DECLARE @RowNum INT
,   @RowCount INT ;
SELECT  @RowNum = 1
,       @RowCount = 1 ;

WHILE @RowCount > 0
BEGIN;
SET @RowNum = @RowNum + 1 ;

UPDATE  nxt
SET     [WithDrawAmountCalc] = prv.[WithDrawAmountCalc]
+ ( [nxt].[WITHDRAWAMOUNT] - [nxt].[COLLECTIONAMOUNT] )
FROM    #Output AS nxt
INNER JOIN #Output AS prv
ON prv.[GLNO] = nxt.[GLNO]
AND [nxt].[LoanNo] = [prv].[LoanNo]
AND prv.RowNum = @RowNum - 1
WHERE   nxt.RowNum = @RowNum ;

SET @RowCount = @@ROWCOUNT ;
END ;

SELECT  [GLNO]
,       [LoanNo]
,       [O].[TRANSACDATE] WithDrawDate
,       [O].[WithDrawAmountCalc] [WITHDRAWAMOUNT]
,       [A].[TRANSACDATE] CollectionDate
,       [A].[COLLECTIONAMOUNT]
,       [A].[WithDrawAmountCalc] Balance
FROM    [#Output] AS O
CROSS APPLY ( SELECT    [TRANSACDATE]
,         [WithDrawAmountCalc]
,         [COLLECTIONAMOUNT]
FROM      [#Output] AS O2
WHERE     [O].[GLNO] = [O2].[GLNO]
AND [O].[LoanNo] = [O2].[LoanNo]
AND [O2].[ROWNUM] = [O].[ROWNUM] + 1
) A

DROP TABLE [#Output]
``````

Hope it helps.

P.S. Sorry but due to the time constraints I was not able to add the comments in the code.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.