(Solved) Problem in formulating sql query for generating a balance sheet

[Mycroft Holmes]

I suggest you start exploring the wonderful world of JOINs. Identify the common field (foreign key) and create an inner join then select the filed you need from the 2 tables.

Never underestimate the power of human stupidity RAH

[paul4everyone]

Thanks Holmes, I know the solution to my problem can be solved by using one of the JOINS. But my challenge is that my little brain is not just connecting me to the appropriate JOIN to use. I tried using a CROSS JOIN but the errors thrown shows that i am not formulating the query properly at all. Please help me. thanks a lot

[thatraja]

paul4everyone wrote:

I tried using a CROSS JOIN but the errors thrown shows that i am not formulating the query properly at all.

Why didn't you share those details in your question? :|

thatraja

Code converters | Education Needed No thanks, I am all stocked up. - Luc Pattyn When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute

[Simon_Whale]

you would need to look at an INNER JOIN BUT also formulate your SELECT accordingly to show only the columns that you require.

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

[paul4everyone]

because what i did with the joins doesnt make sense. i just messed up the query 2 and 3 using JOIN. please help me

[paul4everyone]

thanks for the reply. but i really dont know what to do in this. please assist me

[Simon_Whale]

http://www.w3schools.com/sql/sql_join_inner.asp[^] have a read through that

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

[paul4everyone]

Thanks for the link but all the examples i am seeing is slightly different from my case. i dont know how to capture this fraction of code in the query. i mean

(Select Max(Transaction_Date) From ThriftsTrans Where Transaction_Date
Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID)

[Mycroft Holmes]

I suggest you start studying, this article [^]may be useful to you.

Never underestimate the power of human stupidity RAH

[paul4everyone]

After asking so many questions, i finally succeeded in tweaking out a query that is correct. it is as follows;

Select Customer, Count, Credit, Debit, Balance, Date from
(
Select Customer_ID as Customer, (count(Credit) + count(Debit)) as Count, sum(Credit) as Credit, sum(Debit) as Debit
from ThriftsTrans Where Transaction_Date Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID
) as a left join (
Select Customer_ID as Cust, Account_Balance as Balance,
Transaction_Date as Date From ThriftsTrans Where Transaction_Date In
(Select Max(Transaction_Date) From ThriftsTrans Where Transaction_Date
Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID)
) as b on a.Customer = b.Cust

thanks to you all