Forum

JOIN for matching of receipts with salesinvoices

Mar 18, 2012 01:18 PM

Interested

Points:: 0
Joined:: Nov 10, 2011

I try to make a report where I need a table with sales invoices and in the same row date and amount of receipts. If I write a code:

Code

SEL ECT
   ReceiptLineItems.Ref,
   ReceiptLineItems.Payment,
   ReceiptLineItems.Ref.Date,
   SalesInvoice.Ref AS Ref1
FR OM
   Document.Receipt.LineItems AS ReceiptLineItems
      LEFT JOIN Document.SalesInvoice AS SalesInvoice
      ON ReceiptLineItems.Document = SalesInvoice.Ref

I get all Lines of receipt to the salesinvoice, also Lines to another salesinvoice, if the sales invoice was "mentioned" in the document receipt.
Where can be an error?

Profile

Timofey Bugaevsky

Mar 19, 2012 11:23 AM

Guest

Points::
Joined::

You have wrong order of joined tables, if you need SalesInvoices with Payments you need to place SalesInvoices first in FROM clause and then join payments to it:

Code

SELECT
    ReceiptLineItems.Ref,
    ReceiptLineItems.Payment,
    ReceiptLineItems.Ref.Date,
    SalesInvoice.Ref AS Ref1
FROM
   Document.SalesInvoice AS SalesInvoice
        LEFT JOIN Document.Receipt.LineItems AS ReceiptLineItems 
        ON ReceiptLineItems.Document = SalesInvoice.Ref

Profile

Mikhail Goncharov

Mar 19, 2012 12:05 PM

Just came

Points:: 7
Joined:: Mar 15, 2012

May be you need this:

Code

SEL ECT
   COUNT(DISTINCT ReceiptLineItems.Ref),
   ReceiptLineItems.Ref.Date,
   ReceiptLineItems.Document AS Ref1
FR OM
   Document.Receipt.LineItems AS ReceiptLineItems
GROUP BY
   ReceiptLineItems.Ref.Date,
   ReceiptLineItems.Document

Profile

Lioudmila Nemtseva	#4 Mar 20, 2012 06:40 PM
Interested Points:: 0 Joined:: Nov 10, 2011	bugt, it hasn't fixed the problem, I have the same output table. Mikhail, primarely I need a list of all sales invoices, and information with date of payment (receipt). But it doesn't work
	Profile

Alexey Gerasimov	#5 Mar 21, 2012 06:12 PM
Interested Points:: 15 Joined:: Oct 27, 2011	use OUTER JOIN (left, right). You can google how to use it.
	Profile

Konstantin Zuzik	#6 Mar 22, 2012 12:49 PM
Active user Points:: 23 Joined:: Jul 29, 2011	Lioudmila, there might be some issues with grouping in your report. I emailed to you a sample database where it all works.
	Profile

Lioudmila Nemtseva

Mar 22, 2012 01:41 PM

Interested

Points:: 0
Joined:: Nov 10, 2011

Yes, Konstantin, there was an error in grouping of fields. This way it works:

Code

SEL ECT
   ReceiptLineItems.Ref AS Receipt,
   ReceiptLineItems.Payment,
   SalesInvoice.Ref AS Invoice,
   SalesInvoice.DocumentTotalRC AS InvoiceAmount,
   ReceiptLineItems.Ref.Date AS PaymentDate
FR OM
   Document.Receipt.LineItems AS ReceiptLineItems
      LEFT JOIN Document.SalesInvoice AS SalesInvoice
      ON ReceiptLineItems.Document = SalesInvoice.Ref

But I have one more question: how can I change the query in order to get all invoices, even if they are not payed?

Profile

Alexey Gerasimov	#8 Mar 22, 2012 10:15 PM
Interested Points:: 15 Joined:: Oct 27, 2011	FULL OUTER JOIN? I bet it helps.
	Profile

Lioudmila Nemtseva	#9 Mar 23, 2012 07:22 PM
Interested Points:: 0 Joined:: Nov 10, 2011	Yep! Thanks, Alexey!
	Profile