# A bit of mathematics...

• JaybeeSQL

Hall of Fame

Points: 3257

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

• Phil Parkin

SSC Guru

Points: 243790

Something like this (untested, because you did not provide DDL etc)?

`WITH SumsAS (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.DividendsFROM Sums;`

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

• JaybeeSQL

Hall of Fame

Points: 3257

The result is quite correct, cheers Phil !

Sum IIF....very nifty 🙂

• Sumathi

SSC Rookie

Points: 28

Perfect.

IIF makes it easy 🙂

Viewing 4 posts - 1 through 4 (of 4 total)