JOIN for matching of receipts with salesinvoices

Accounting and inventory IFRS/GAAP suite for SMB. General questions. See 1C:AccountingSuite details

Pages: 1

Lioudmila Nemtseva

People who like this: 0 Yes / 0 No

Mar 18, 2012 01:18 PM

Interested

Rating: 11
Joined: Nov 10, 2011
Company: 1A Software e.U

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?

Timofey Bugaevsky

People who like this: 0 Yes / 0 No

Mar 19, 2012 11:23 AM

Timofey Bugaevsky

Guest

Joined:
Company:

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

Mikhail Goncharov

People who like this: 0 Yes / 0 No

Mar 19, 2012 12:05 PM

Just came

Rating: 1
Joined: Mar 15, 2012
Company: no

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

Lioudmila Nemtseva	#4 People who like this: 0 Yes / 0 No Mar 20, 2012 06:40 PM
Interested Rating: 11 Joined: Nov 10, 2011 Company: 1A Software e.U	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

Alexey Gerasimov	#5 People who like this: 0 Yes / 0 No Mar 21, 2012 06:12 PM
Interested Rating: 32 Joined: Oct 27, 2011 Company: Abaco Soluciones S.A.	use OUTER JOIN (left, right). You can google how to use it.

Konstantin Zuzik	#6 People who like this: 0 Yes / 0 No Mar 22, 2012 12:49 PM
Active user Rating: 4 Joined: Jul 29, 2011 Company: 1C Company	Lioudmila, there might be some issues with grouping in your report. I emailed to you a sample database where it all works.

Lioudmila Nemtseva

People who like this: 0 Yes / 0 No

Mar 22, 2012 01:41 PM

Interested

Rating: 11
Joined: Nov 10, 2011
Company: 1A Software e.U

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?

Alexey Gerasimov	#8 People who like this: 0 Yes / 0 No Mar 22, 2012 10:15 PM
Interested Rating: 32 Joined: Oct 27, 2011 Company: Abaco Soluciones S.A.	FULL OUTER JOIN? I bet it helps.

Lioudmila Nemtseva	#9 People who like this: 0 Yes / 0 No Mar 23, 2012 07:22 PM
Interested Rating: 11 Joined: Nov 10, 2011 Company: 1A Software e.U	Yep! Thanks, Alexey!

Pages: 1

Users browsing this topic (guests: 1, registered: 0, hidden: 0)