USP_DATALIST_BANKACCOUNTREGISTER

        CREATE procedure dbo.USP_DATALIST_BANKACCOUNTREGISTER(
          @BANKACCOUNTID uniqueidentifier, 
          @STARTDATE datetime = NULL, 
          @ENDDATE datetime = NULL, 
          @SHOWCODE smallint = 0, 
          @MAXNUMROWS int = 1000
        ) 
        as
          begin
          set nocount on;

          DECLARE @ResultSet TABLE(ID uniqueidentifier, RowNum int, TranDate date, TranType nvarchar(26), TranNum nvarchar(50), Ref nvarchar(100), Payment money, Deposit money, Balance money, Amount money, TranTypeCode int, RunningTotal money, PostStatusCode int, Status nvarchar(11), TransactionCurrencyID uniqueidentifier);

          DECLARE @AccountBalance money;
          DECLARE @RunningTotal money;
          DECLARE @TempRowNum int;
          DECLARE @LastAmount money;

          declare @CURRENTDATE datetime;

          --There are still records in the resultset after we change the filters. Hence I am clearing the resultset here.

          --Since this is right after Declare @ResultSet statement, we expect resultset to not have any values but I don't think it is

          --working that way

          DELETE FROM @ResultSet;

          --Retrieve the entire registry to ensure valid row balances

          INSERT INTO @ResultSet (ID, RowNum, TranDate, TranType, TranNum, Ref, Payment, Deposit, Balance, Amount, TranTypeCode, RunningTotal, PostStatusCode, Status, TransactionCurrencyID)
          SELECT ID, 
                 Row_Number() OVER (order by TRANSACTIONDATE DESC, GENERALLEDGERTRANSACTION, TRANSACTIONNUMBER), 
                 TransactionDate, 
                 TransactionType, 
                 TransactionNumber, 
                 Reference, 
                 case when STATUSCODE = 4 then 0 else CASE GENERALLEDGERTRANSACTION WHEN 2 THEN TRANSACTIONAMOUNT ELSE NULL END end,
                 CASE GENERALLEDGERTRANSACTION WHEN 1 THEN TRANSACTIONAMOUNT ELSE NULL END,
                 NULL,
                 case when STATUSCODE = 4 then 0 else CASE GENERALLEDGERTRANSACTION WHEN 1 THEN TRANSACTIONAMOUNT ELSE -1*TRANSACTIONAMOUNT END end,
                 TRANSACTIONTYPECODE, 
                 NULL,
                 BANKACCOUNTTRANSACTION.POSTSTATUSCODE,
                         BANKACCOUNTTRANSACTION.STATUS,
                 (select TRANSACTIONCURRENCYID from dbo.BANKACCOUNT where BANKACCOUNT.ID = @BANKACCOUNTID) TransactionCurrencyID
          from dbo.BANKACCOUNTTRANSACTION 
          where 
            BANKACCOUNTID = @BANKACCOUNTID 
            and DELETED = 0 
            and PROCESSING = 0
          order by TRANSACTIONDATE DESC, GENERALLEDGERTRANSACTION, TRANSACTIONNUMBER 

          begin try
            --Get the Total Account Balance of all transactions

            SELECT @AccountBalance=SUM(Amount) FROM @ResultSet

            --Update each transaction with the associated account balance

            SET @LastAmount=0;
            UPDATE @ResultSet
            SET @AccountBalance = Balance = @AccountBalance - @LastAmount, @LastAmount = Amount = Amount
            FROM @ResultSet
          end try
          begin catch
          end catch

          if @SHOWCODE = 99 begin --Specific Range

            set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
              set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
          end else begin
            set @CURRENTDATE = getdate();
            set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

            if @SHOWCODE = 1 --Last 30 days

                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
            else if @SHOWCODE = 2 --Last 60 days

                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-59,@CURRENTDATE));
            else if @SHOWCODE = 3 --Last 90 days

                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
            else if @SHOWCODE = 4 --Last 6 months

                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(month,-6,@CURRENTDATE)));
            else if @SHOWCODE = 5 --Last year

                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)));
            else if @SHOWCODE = 6 --Last 5 years

                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-5,@CURRENTDATE)));
            else begin --All

                set @STARTDATE = null;
                set @ENDDATE = null;
            end

          end


          --Remove unwanted transactions from the result set

          if @STARTDATE IS NOT NULL DELETE FROM @ResultSet WHERE TranDate < @STARTDATE; 
          if @ENDDATE IS NOT NULL DELETE FROM @ResultSet WHERE TranDate > @ENDDATE;

          SET @TempRowNum = 0;
          SET @RunningTotal = 0;

          begin try
            --Resequence the resultset, and update the running totals

            UPDATE @ResultSet
            SET @TempRowNum = RowNum = @TempRowNum + 1, @RunningTotal = RunningTotal = @RunningTotal+Amount
            FROM @ResultSet
          end try
          begin catch
          end catch

          SELECT ID, RowNum, TranDate, TranType, TranNum, Ref, Deposit, Payment, Balance, TranTypeCode, RunningTotal, PostStatusCode, Status, TransactionCurrencyID
          FROM @ResultSet 
          WHERE RowNum <= @MAXNUMROWS OR @MAXNUMROWS IS NULL
        end