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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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