?
Solved

Summing accounting journal entries. Some have to be negative.

Posted on 2004-08-11
19
Medium Priority
?
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +3
19 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 11776770
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
ID: 11776778
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
ID: 11776929
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Expert Comment

by:wesbird
ID: 11776930
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
ID: 11776981

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
ID: 11777124
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
ID: 11777193
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:Scott Pletcher
ID: 11777208
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
ID: 11777212
Cross posted agin, but basically I think SjoerdVerweij's answer is quite similar, either will do the trick
0
 
LVL 7

Assisted Solution

by:wesbird
wesbird earned 200 total points
ID: 11777283
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:
Scott Pletcher earned 300 total points
ID: 11777460
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
ID: 11780513
Scott, I think you're getting a little too funky for no good reason here.  :-)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11784373
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
ID: 11787194
What's wrong with DateDiff(Month, Value, GetDate()) = 0?
0
 
LVL 2

Author Comment

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

Expert Comment

by:Scott Pletcher
ID: 11787711
>> 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
ID: 11787905
Of course, of course... I was just wondering about "disruptive".
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11788075
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
ID: 11794779
It's too early in the day to get tetchy Scott. I would consider "optimal" more fitting, that's all.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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