Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

I'm summing up an accounting entries in a table.

The field I'm summing is amount.

There is a interger field called TransactionType.

For some of the entries,depending on the transactiontype, I need to sum the negative of the amount.

Is there a case statement that could do this.

I was trying it with 2 selects and a union but I got errors.

Anyone who can help out I would greatly appreciate it.

Thanks,

Chris

The field I'm summing is amount.

There is a interger field called TransactionType.

For some of the entries,depending on the transactiontype, I need to sum the negative of the amount.

Is there a case statement that could do this.

I was trying it with 2 selects and a union but I got errors.

Anyone who can help out I would greatly appreciate it.

Thanks,

Chris

select trastype,sum(amount)

from table1

where transtype in ('type1','type2') -- If you want to group only a few trans types ..else ignore this where clause

group by transtype

Let me know if I misunderstood your query.

Thanks -- Ram

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

I'll work on it and get back to you.

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 )

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.

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

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

FROM acctingTable

GROUP BY accountNo

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

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.

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".

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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

FROM acctingTable

WHERE transactionType NOT IN (3, 7)

-- if transactionType = 3 or 7, verify in current calendar month

OR transDate BETWEEN CAST(STUFF(CONVERT(CHAR(8)

GROUP BY accountNo