• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

Summing accounting journal entries. Some have to be negative.

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
stopher2475
Asked:
stopher2475
  • 5
  • 5
  • 4
  • +3
2 Solutions
 
ram2098Commented:
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
 
wesbirdCommented:
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
 
stopher2475Author Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
wesbirdCommented:
let me clarify the small table

e.g.

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


0
 
wesbirdCommented:

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
 
SjoerdVerweijCommented:
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
 
BillAn1Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
BillAn1Commented:
Cross posted agin, but basically I think SjoerdVerweij's answer is quite similar, either will do the trick
0
 
wesbirdCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
SjoerdVerweijCommented:
Scott, I think you're getting a little too funky for no good reason here.  :-)
0
 
Scott PletcherSenior DBACommented:
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
 
SjoerdVerweijCommented:
What's wrong with DateDiff(Month, Value, GetDate()) = 0?
0
 
stopher2475Author Commented:
Thanks guys.
I have two good approaches to work on this now.
I appreciate the help.
Chris
0
 
Scott PletcherSenior DBACommented:
>> 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
 
SjoerdVerweijCommented:
Of course, of course... I was just wondering about "disruptive".
0
 
Scott PletcherSenior DBACommented:
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
 
SjoerdVerweijCommented:
It's too early in the day to get tetchy Scott. I would consider "optimal" more fitting, that's all.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now