Summing accounting journal entries. Some have to be negative.

You could try having a small table with your transaction types in the first column and -1.0 or +1.0 on the second column named mult.  Be sure to use currency or decimal types.  NOT FLOATING POINT.

SELECT sum(mult * amount)
FROM
    yourTable as t1
    , smallTable as t2
WHERE
    t1.TransactionType = t2.TransactionType
    AND ... whatever


I'm not saying this is most efficient way to do it, but it should work

Basically I have 7 TransactionTypes. If the TransactionType is 2,4, or 6 I want to add it. If the type is 3 or 7 I have to subtract it. And I only want to include 3 and 7 if the date is the current month. What I've done in the past is use a temporary table to hold the sums and split up the problem. I have to think about the second soloution. I do have a lookup table for the transaction types so maybe if I add a multiplacation column that holds 1 or -1...
I'll work on it and get back to you.

let me clarify the small table

e.g.

TransactionType | mult
1 | 1.00    -- Sales Invoices
2 | -1.00   -- Cost of Sales
3.... etc

TransType | mult
2 | 1    |
3 | -1  |
4 | 1   |
6 | 1   |
7 | -1  |

SELECT sum(mult * amount)
FROM
    yourTable as t1
    , smallTable as t2
WHERE
    t1.TransactionType = t2.TransactionType
    AND t2.mult > 0
    OR ( t2.mult < 0 AND t1.TransDate > @StartOfMonth )

select sum(
  case
    when transactiontype in (2,4,6)
      then amount
    when transactiontype in (3, 7)
      then (
        case
          when datediff(month, transactiondate, getdate()) = 0
            then -amount
          else
            0
        end)
  end)
from
  yourtable

Of course, correct yourtable, amount and transactiondate to your field names.

what I think you want is :


select sum(tot) from
(
select sum(amount) as tot
from mytable where transactiontype in (2,4,6)
union
select -sum(amount) as tot
from mytable where transactiontype in (1,7)
and datediff(month, transactiondate, getdate()) = 0
) a

That seems overly complex to me, adding another column is not necessary.  If you really want to, you could add a computed column with 1 or -1 in it, but even that seems like overkill to me.  For example:


SELECT accountNo, SUM(amount * CASE WHEN transactionType IN (2, 4, 6) THEN 1 ELSE -1 END)
FROM acctingTable
GROUP BY accountNo

Cross posted agin, but basically I think SjoerdVerweij's answer is quite similar, either will do the trick

Scott, I think you're getting a little too funky for no good reason here.  :-)

Actually that's the least disruptive method I know of to see if a given date falls within the current calendar month ... unless I misunderstood that part of the question.

What's wrong with DateDiff(Month, Value, GetDate()) = 0?

Thanks guys.
I have two good approaches to work on this now.
I appreciate the help.
Chris

>> What's wrong with DateDiff(Month, Value, GetDate()) = 0? <<

Because SQL will then *never* use an index on "value" column, even if one exists.  SQL will not *consider* an index for a column that is involved in a function.

Also, as a secondary point, the DATEDIFF will require computation on every row in the table, but the other method requires only 1-time computation.  This can make a difference if the table has a large number of rows, although obviously the first issue is likely to hurt performance much worse in that situation.

Of course, of course... I was just wondering about "disruptive".

Yes, "least disruptive".

disruptive = To interrupt or impede the progress, movement, or procedure of

So, to me, the "least disruptive" to SQL Server was the method I used.  Another method -- such as DATEDIFF -- is more "disruptive" to SQL Server because it potentially prevents SQL from fully considering all indexes, that is, it "interrupts or impedes" SQL's normal "procedure".