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

jkchowdary gravatar 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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1948

asked: Mar 14, 2012 at 06:04 PM

Seen: 816 times

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