Results 1 to 2 of 2

Thread: help in a query

  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Red face help in a query

    Good day all....
    I av a problem in writing a query

    I av two tables

    Banks
    -BankCode
    -BankName
    -BankBalance
    -LastModified

    BankTransaction
    -StanNo
    -VoucherNo
    -BankCode
    -ChequeNo
    -Amount
    -DebitCreditCheck
    -Date
    -Remarks
    -AmountAfterTransaction

    Whenever there is a debit/credit transaction from bank..it is recorded in bankTransaction table and BankBalance is updated in banks table...

    I want to have ALL BANKS CREDITs on a specific date

    My query is
    (SELECT bTr.BankCode,SUM(CONVERT(money,bTr.Amount)) FROM benkTransaction bTr WHERE
    bTr.DebitCreditCheck='CREDIT' AND bTr.[Date]<='7/16/2010' GROUP BY bTr.BankCode);

    The problem is that If there is no transaction happened on or before this date ..there is no result.... I want it to be 0.00 if no transaction ever happened ...and the balance if some transaction happened before this period

    I anticipate your quick response

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Here is your code*

    Code:
    SELECT	Banks.BankCode,
    	SUM(CONVERT(money,BankTransaction.Amount))
    FROM	Banks
    	left outer join BankTransaction
    		on Banks.BankCode = BankTransaction.BankCode
    		and BankTransaction.DebitCreditCheck='CREDIT'
    		and BankTransaction.[Date]<='7/16/2010'
    GROUP BY Banks.BankCode



    *Typos corrected and superfluous obfuscating table aliases removed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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