Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Credit / Debit problem

    I have a table that has account # and amount columns. All the debit and credit amounts are stored in same column. All debit amounts are positive and credit amounts are negative.
    Problem is: I want to write a SQL to list account #, debit and credit in same line and the difference between debit and credit in last column.

    For eg: following are 2 rows in table (ACCOUNT|AMOUNT)

    A1 $500
    A1 $-500

    I want result as: (ACCOUNT|DEBIT|CREDIT|DIFFERENCE)

    A1 $500 $-500 $0

    Thanks.

    ~BS.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Select A.account,a.amount,b.amount.a.amount - B.amount
    From My_table A,
    My_table B
    Where A.amount > 0
    And B.amount < 0
    And A.account = B.account
    Order By A.account;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2004
    Posts
    78
    Code:
    Select A.account,sum(a.posted_total_amt),sum(b.posted_total_amt),sum(a.posted_total_amt + B.posted_total_amt)
      From ps_ledger A,
      ps_ledger B
      Where A.posted_total_amt > 0
      And B.posted_total_amt < 0
      And A.account = B.account
      AND A.ACCOUNT = A1001
      group By A.account
    /
    Above query is variation of beilstwh's query.
    Problem with this query is that-
    Say I have 3 rows: (ACCOUNT|AMOUNT)

    A1001 $600
    A1001 $-200
    A1001 $-100

    The result for thsi query comes as: (Account|Amount|Amount|difference)

    A1001 $1200 $-300 $900

    The $1200 in orange is extra amount because $600 gets repeated as many times the number of rows having negative amount (in this case 2).

    I wrote the following query and problem now is that all negative values are summed up in one row and positive values in second row:

    Code:
    select account,sum(posted_total_amt)"dr",0"cr" from ps_ledger where 
    posted_total_amt>0 and account = A1001 group by account union select account,0,sum(posted_total_amt)
     from ps_ledger where posted_total_amt<0 and account = A1001 group by account
    /
    Result for above query is: (Account|Amount|Amount)

    A1001 $0 $-300
    A1001 $600 $0

    The result I need is: (Account|Amount|Amount
    A1001 $600 $-300


    ~BS.
    Last edited by bhavesh78; 01-10-05 at 18:01.

  4. #4
    Join Date
    Dec 2004
    Posts
    78

    Thumbs up

    Hi again,

    The solution for above would be:
    Code:
    select account,sum("dr"),sum("cr") from (select account,sum(posted_total_amt)"dr",0"cr" from ps_ledger where 
    posted_total_amt>0 and account = A1001 group by account union select account,0,sum(posted_total_amt)
     from ps_ledger where posted_total_amt<0 and account = A1001 group by account) group by account
    /

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    A more simple solution would be:
    Code:
    Select A.Account
         ,Sum(Case When A.Posted_Total_Amt > 0 
              Then A.Posted_Total_Amt
              Else 0 End) Debit
         ,Sum(Case When A.Posted_Total_Amt < 0 
              Then A.Posted_Total_Amt
              Else 0 End) Credit
         ,Sum(A.Posted_Total_Amt) Difference
      From Ps_Ledger A
      Where A.Account = A1001
        And A.Posted_Total_Amt Is Not Null
      Group By A.Account;

    Last edited by LKBrwn_DBA; 01-10-05 at 18:33.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Dec 2004
    Posts
    78

    Thumbs up

    Query by LKBrwn_DBA also works great. But right now I am using the following because we are using an addin software to create queries and it does not allow CASE logic-

    Code:
    select account,sum(dr) DEBIT,sum(cr)*(-1) CREDIT, sum(dr)-sum(cr) "DR-CR" from 
    (select account,posted_total_amt dr,0 cr,accounting_period from ps_ledger where 
    posted_total_amt>=0 and accounting_period=1 union select account,0,posted_total_amt,accounting_period
    from ps_ledger where posted_total_amt<=0 and accounting_period=1) 
    group by account
    /
    Thanks for all your time and effort.

    ~BS.
    Last edited by bhavesh78; 01-10-05 at 19:46.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Carefull with this: sum(dr)-sum(cr), because cr is the sum of negative numbers!
    better use: sum(dr+cr)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Dec 2004
    Posts
    78
    Thanks. It was just to understand the query better.

    ~BS

  9. #9
    Join Date
    Dec 2004
    Posts
    78
    A more optimized way would be:

    Code:
    select account,(select sum(posted_total_amt) from ps_ledger 
    where accounting_period=1 and account=a.account and posted_total_amt >= 0) cr,
    (select sum(posted_total_amt)*(-1) from ps_ledger 
    where accounting_period=1 and account=a.account and posted_total_amt <= 0) dr
    from ps_ledger a 
    where a.accounting_period=1  
    group by a.account 
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •