Split positive and negative amounts into own columns

[cspuckett]

I have a table that is basically:
Year, Month,Co, Division, GL account, amount (+ or -)

It is a detail transaction table.

I want to create a trial balance from this, which will list the debits (positives) in one column and credits (negatives) in a second column. It would also need to sum by, for this report, division + account (there is only one company).

Can this be done with an SQL query? I am using an SQL tool to read the db file directly.

Output might be:
Year Co Division Account Debit Credit
----- -- -------- ------- ----- ------
2010 1 100 1111 $125 ($100)
2010 1 100 4000 $10 ($2,000)
2010 1 105 4000 $15 ($1,500)

I would also like a sum column on the right that sums debits and credits for the division+account combo.

Possible?

Ideally, I would have the results in this format. I know I could create a union or do something in Excel / Access / Crystal to get it this way...but I want the extract to be in this format if possible.

 

[PHV]

something like this ?
SELECT [Year], Co, Division, [GL account], SUM(IIf(amount>0,amount,0)) AS Debit], SUM(IIf(amount<0,amount,0)) AS Credit, SUM(amount) AS [sum column]
FROM yourTable
GROUP BY [Year], Co, Division, [GL account]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

I suspect you'll need multiple queries to accomplish this.
Something like:

SELECT Year, Co, Division, Account, Sum(Amount) AS Debit
FROM YourTable
WHERE Amount > 0
GROUP BY Year, Co, Division, Account

SELECT Year, Co, Division, Account, Sum(Amount) AS Credit
FROM YourTable
WHERE Amount < 0
GROUP BY year, Co, Division, Account

SELECT Q1.Year, Q1.Co, Q1.Division, Q1.Account, Q1.Debit, Q2.Credit
FROM Q1, Q2
WHERE Q1.Year = Q2.Year
AND Q1.Co = Q2,Co
AND Q1.Division = Q2.Division
AND Q1.Account = Q2.Account

Randy

 

[randy700]

OK, so PHV has a better method (much better!).


Randy

 

[BigRed1212]

You may be able to do something with IIF.

SELECT fyear, co, div, acco, sum(debit) AS sdebit, sum(credit) AS scredit, sdebit + scredit as sum
FROM
(
SELECT fyear, co, div, acco, iif(amount>0, amount, 0) as debit, iif(amount<0, amount, 0) as credit
FROM transactions
where fyear = '2010'
)
GROUP BY fyear, co, div, acco;

 

[BigRed1212]

Ok, so everybody types faster than I do.

 

[cspuckett]

I definitely don't mind the multiple responses...thank you all.

Okay, this is good, you have helped move farther (or further) down the path. But, I am using an SQL tool for the AS/400 and it does not like "iif". I did some searching and am getting close with "CASE" (if anyone is familiar with that).

So, here is my attempt and the error message I receive:

select fiscal_year, company, division, account, case when base_amount > 0 then sum(base_amount) else 0 end as "Debit", case when base_amount < 0 then sum(base_amount) else 0 end as "Credit", sum(base_amount)
from gl_table
where fiscal_year = 2010
group by fiscal_year, company, division, account

When I run this, I get an error that "base_amount" is not in the "group by" clause. There are several errors listed in the log, but all on that theme.

If I add base_amount to the group_by...which, I realize is not the solution I need...the query does run.

 

[cspuckett]

Guys, I figured it out! Thanks for getting the old gray matter working.

select fiscal_year, company, division, account, sum(case when base_amount > 0 then base_amount else 0 end) as "Debit", sum(case when base_amount < 0 then base_amount else 0 end) as "Credit", sum(base_amount)
from gl_table
where fiscal_year = 2008
group by fiscal_year, company, division, account;

 

[PHV]

I am using an SQL tool for the AS/400
So, why asking in forum701 ?

 

[BigRed1212]

So, why asking in forum701: Microsoft: Access Queries and JET SQL ?

LMAO. My question exactly!

 

[cspuckett]

PHV / BigRed:

Comic relief?

I couldn't find an AS/400 & SQL forum so I figured the guidance I found here would lead me to the eventual answer.

Thanks for not busting my you know what too badly!

CP