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

    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)

You must be logged in to reply to this topic. Login to reply