x

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
more ▼

asked Mar 14, 2012 at 06:04 PM in Default

avatar image

jkchowdary
1 2 2 2

where do the withdrawamount values of 20000 and 15000 come from? They are not in your source data.

Mar 15, 2012 at 08:59 AM Fatherjack ♦♦

@FatherJack that is actually the balance carried to the next row.

Mar 15, 2012 at 09:58 AM Usman Butt

@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?

Mar 15, 2012 at 11:31 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 15, 2012 at 06:43 AM

avatar image

Usman Butt
14k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2018

asked: Mar 14, 2012 at 06:04 PM

Seen: 918 times

Last Updated: Mar 15, 2012 at 11:31 AM

Copyright 2016 Redgate Software. Privacy Policy