Hi all,
I'm just doing some work on my own bank statements imported from CSV. Two salient columns, MoneyIn (int), MoneyOut (Int).
This is the formula I'm trying to calculate:
((Revenue - Outgoings) * .81) - Dividends Paid = Result
I've been able to
Select
(Select Sum ([Money In]) As 'Revenue'
from SmytheData.dbo.Transactions3)
-
(Select Sum ([ Money Out]) As 'Outgoings'
from SmytheData.dbo.Transactions3
Where Reference not like '%Div%')
However, I need to multiply the result by 0.81 (to allow for 19% tax), THEN subtract
(Select Sum ([ Money Out]) As 'Outgoings'
from SmytheData.dbo.Transactions3
Where Reference like '%Div%')
TIA
Something like this (untested, because you did not provide DDL etc)?
WITH Sums
AS (SELECT Revenue = SUM([Money In])
,Outgoings = SUM(IIF(Reference NOT LIKE '%Div%', [Money Out], 0))
,Dividends = SUM(IIF(Reference LIKE '%Div%', [Money Out], 0))
FROM SmytheData.dbo.Transactions3)
SELECT Result = ((Sums.Revenue - Sums.Outgoings) * 0.81) - Sums.Dividends
FROM Sums;
October 13, 2019 at 8:50 pm
The result is quite correct, cheers Phil !
Sum IIF....very nifty
October 22, 2019 at 1:52 am
Perfect.
IIF makes it easy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy