Solved

Summing accounting journal entries. Some have to be negative.

Posted on 2004-08-11
19
382 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:stopher2475
  • 5
  • 5
  • 4
  • +3
19 Comments
 
LVL 11

Expert Comment

by:ram2098
Comment Utility
Not sure I understood your requirement...If you want to get the sum of amounts based on Transaction Types (for sepcific transaction types)..here is the query.

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
0
 
LVL 7

Expert Comment

by:wesbird
Comment Utility
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
0
 
LVL 2

Author Comment

by:stopher2475
Comment Utility
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.
0
 
LVL 7

Expert Comment

by:wesbird
Comment Utility
let me clarify the small table

e.g.

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


0
 
LVL 7

Expert Comment

by:wesbird
Comment Utility

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 )



0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
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.
0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
Cross posted agin, but basically I think SjoerdVerweij's answer is quite similar, either will do the trick
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Assisted Solution

by:wesbird
wesbird earned 50 total points
Comment Utility
Oops on mine I forgot a bracket:

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

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 75 total points
Comment Utility
Oops, left off the WHERE:


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), GETDATE(), 112), 7, 2, '01') AS DATETIME) AND DATEADD(MS, -3, STUFF(CONVERT(CHAR(8), DATEADD(MONTH, 1, GETDATE()), 112), 7, 2, '01'))
GROUP BY accountNo
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
Scott, I think you're getting a little too funky for no good reason here.  :-)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
What's wrong with DateDiff(Month, Value, GetDate()) = 0?
0
 
LVL 2

Author Comment

by:stopher2475
Comment Utility
Thanks guys.
I have two good approaches to work on this now.
I appreciate the help.
Chris
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> 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.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
Of course, of course... I was just wondering about "disruptive".
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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".
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
It's too early in the day to get tetchy Scott. I would consider "optimal" more fitting, that's all.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 41
t-sql month question 8 40
Complex SQL 10 32
SQL Date Retrival 7 25
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now