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
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'