Solved

Summing accounting journal entries. Some have to be negative.

Posted on 2004-08-11
19
383 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
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
 
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:ScottPletcher
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 7

Assisted Solution

by:wesbird
wesbird earned 50 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:
ScottPletcher earned 75 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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

895 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

15 Experts available now in Live!

Get 1:1 Help Now