RE: Debit / Credit SQL Output
This is an interesting problem. I would try looking at using a union
statement for your select statement.
select acctnum,value,0.00 from trans_table where transtype = "CREDIT"
union
select acctnum,0.00,value from trans_table where transtype = "DEBIT"
I don't know what type of reporting tool you are using to write your
report but whatever you are using should allow you to group by field1
and sum by fields 2 and 3.
this should provide you the results you are looking for. I have done
this type of reports before and found this to be the easiest way to do
it.....
hope this helps....
Russ...
}-----Original Message-----
}From: Dan Armstrong [SMTP:dana@stinky.quikrun.com]
}Sent: Sunday, July 20, 1997 1:35 PM
}To: informix-list@rmy.emory.edu
}Subject: Debit / Credit SQL Output
}
}I can't believe I am hung up on this one, but I just can't figure it
}out:
}
}I am writing an A/R accounting system for a customer. I have an AR
}Transactions table which basically contains an account number, a value
}(money) and whether it is a debit or credit.
}
}My problem is with the output. Any accounting report has the debits and
}
}credits listed in two columns. What I need is to group by account
}number, and put the sum(debits) and sum(credits) into two separate
}columns. Like this:
}
}ACCTNUM CREDITS DEBITS
}
}12000 $100.00 $00.00
}13000 $200.00 $10.00
}etc.
}
}The table I am trying to run a query against looks like this:
}
}ACCTNUM VALUE TRANTYPE
}
}12000 $50.00 Credit
}12000 $50.00 Credit
}13000 $100.00 Credit
}13000 $100.00 Credit
}13000 $10.00 Debit
}
}
}That nasty Ansi compliant GROUP BY clause has got me hog tied here...I
}have tried using the CASE statement, in which I get duplicate account
}numbers in the output, I have even tried a self-join which yields
}completely whacko results...This is so simple, does anybody have any
}thoughts on the SQL Query that could yield the correct results?
}
}Thanks a lot, Dan.
}
}
}
}