help in a query

albloshi · #1 (**permalink**) 09-18-10, 04:45

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

blindman · #2 (**permalink**) 09-20-10, 08:56

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech