Solved

Summing accounting journal entries. Some have to be negative.

Posted on 2004-08-11
19
384 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 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:
Scott Pletcher 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: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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

816 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

13 Experts available now in Live!

Get 1:1 Help Now