Rolling Balance SQL

Question

Hello I need some help please I have the following select statement But the rolling balance seems not to be working.

Select Distinct
        Substring(Convert(Varchar(10), M.ValueDate, 101), 0, 11) As 'Value Date'
      , base.Reference
      , 'Transaction Discription' As [Transaction Discription]
      , M.Action
      , base.Nominal
      , base.Consideration
      , (BrokerFee + CSDPFee + CSDFee + InvestorProtectionLevyFee + ExchangeFee + GuaranteedFee) As 'Transaction Fee'
      , base.VATFee As 'VAT'
      , base.SecuritiesTransferTax
      , (base.Consideration - (BrokerFee + CSDPFee + CSDFee + InvestorProtectionLevyFee + ExchangeFee + GuaranteedFee)) As [Total Consideration]
      , (Select Top (1)
                Round(Sum((base.Consideration - (BrokerFee + CSDPFee + CSDFee + InvestorProtectionLevyFee + ExchangeFee + GuaranteedFee))), 2)
         From   CashMovements T2
         Where  T2.RowId <= M.RowId
                And T2.AccountNumber = M.AccountNumber
                And T2.Action In ('W', 'D')
        ) As 'RunningTotal'
From    (Select *
         From   Instructions
         Where  instructionId In (Select  Distinct
                                            XReference
                                  From      cashmovements As CM
                                  Where     accountnumber In (Select    AccountNumber
                                                              From      CashAccounts
                                                              Where     AccountNumber In (Select Distinct
                                                                                                    AccountNumber
                                                                                          From      CrossReferences
                                                                                          Where     Class In (Select    ScripAccountId
                                                                                                              From      ScripAccounts))))
        ) As base
Inner Join CashMovements As M
        On M.XReference = base.InstructionId
Where   M.AccountNumber = '00000000006'
        And M.Action In ('W', 'D')
        And base.Consideration <> 0
        And Nominal <> 0
Group By M.ValueDate
      , base.Reference
      , M.Action
      , base.Nominal
      , base.Consideration
      , base.BrokerFee
      , base.VATFee
      , base.CSDPFee
      , base.CSDFee
      , base.InvestorProtectionLevyFee
      , base.ExchangeFee
      , base.GuaranteedFee
      , base.SecuritiesTransferTax
      , M.RowId
      , M.AccountNumber
Order By base.Reference;

It seems its only duplicating the values

The following is the result set I get:

> Total Consideration      RunningTotal
> 137.21                    137.21  
> 137.21                    137.21
> 1462.25                   1462.25
> 4406.74                   4406.74
> 1462.25                   1462.25
> 1462.25                   1462.25
> 5878.99                   5878.99

This is the result set I want

> Total Consideration      RunningTotal
> 137.21                    137.21  
> 137.21                    274.42
> 1462.25                   1736.67
> 4406.74                   6143.41
> 1462.25                   7605.66
> 1462.25                   9067.91
> 5878.99                   14946.9

So the Running Total should increase each row with the amount of the Total consideration

I ran your query through an auto-formatter because it was insulting my eyeballs trying to read it. Please at least *try* to format your queries. It may even help highlight problems in the future. — Siyual, Feb 06 '17 at 13:55
plz explain what you expect from the query and how's the result from the one you've written is different from your expectation — Nasmi Sabeer, Feb 06 '17 at 14:00
Providing an MCVE might us understand where your query is going wrong (see [here](http://stackoverflow.com/help/mcve) and [here](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) for tips). — David Rushton, Feb 06 '17 at 14:17

score 0 · Accepted Answer · answered Feb 06 '17 at 14:54

0

It's hard to tell without seeing the data and what you believe is wrong but I think your sub query column should have the top 1 statement removed and the base.Consideration might need moving outside the select. I have also moved the rounding outside as well:

  , Round(base.Consideration - (Select Sum(BrokerFee + CSDPFee + CSDFee + InvestorProtectionLevyFee + ExchangeFee + GuaranteedFee)
    From   CashMovements T2
    Where  T2.RowId <= M.RowId
    And T2.AccountNumber = M.AccountNumber
    And T2.Action In ('W', 'D')
), 2) As 'RunningTotal'

answered Feb 06 '17 at 14:54

Richard

1,124
10
13

The Top (1) is because that result set returns more than one row, The compiler does not enjoy a subqueary that returns more than one value when the subquery is followed by =,<>,<= ect. – CodeGuy Feb 07 '17 at 07:30
If you are only returning 1 column and it has a SUM() around it how can it be returning more than one row? – Richard Feb 07 '17 at 15:37

Rolling Balance SQL

1 Answers1