Skip to Content
author's profile photo Former Member
Former Member

Data from Multiple Tables

I have Five Tables

1. Bill (BillId, CustomerId, BillAmount, BillDate)

2. BillItems (BillId, ItemId, Rate, Quantity)

3. AdvancePayment(BillId, AdvDate, AdvAmount)

4. RecievedAmount (BillId, RecievedAmount, RecievedDate)

5. Customer(CustomerId, CustomerName, Address etc)

I Need SQL Server Query to show daily income report in following format

Customer Name BillAmount AdvancePaid Payable PaidAmount DueAmount

1. Qasim Ali 20,000 5000 15000 10000 5000

2. Zaigham Tauqeer 25000 - 25000 20000 5000

(Due Amount is equal to Payable - Paid Amount)

I need Sql Query in this regard or any other way to access this data correctly

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 12:15 PM

    Hi Qasim,

    You have five tables and you want to show the data from them.

    Using crystal reports designer we can directly create the report based on these table as per the requirements.

    SQL query will be automatically generated by crystal and you can see the sql generated.

    These five tables have the required fields to link with each other so there wonu2019t be any problem to fetch the correct data.

    Please let us know in case you want to know how to do it or I do not understand your requirement.

    Regards,

    Aditya Joshi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 11:29 AM

    Hi Quasim

    You can achieve filtered data from Add Command object. Instead of selecting table choose Add Command and write the SQL query.

    Hope this information will help you.

    Regards

    Asha.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks Asha

      Can you make sql query please

      Problem is that if there is no advance paid against billId or payment recieved it return no rows.

      if there are two advance payments made in advancePayment table then it return n *2 rows. and similarly of RecievedPayments

      Best Regards

      Qasim Ali

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.