SQL - Sales analysis report by customer

[Former Member]

How to create a SQL statement that it has the same resulted that the <sales analysis report by customer>?

Thanks...

[Former Member]

Solution in last thread

[former_member583013]

Marcio,

You could run a SQL trace using SQL Server Profiler (MS SQL Server 2005 > Performance Tools > ....)

and get the SQL being used in the Sales Analysis report

OR alternately create your Own SQL Report with the field you need

Suda

[Former Member]

Hi Suda, Thanks for reply...

I ask you this because i need a query wich returns me the incoming payments->Documents for Payment already paid with its own due date

But, when i request a Trace in the SQL Server Profiler, i found that the SQL instruction is a stored procedure (TmSp_Sales_An_Crd_Det_Y) in wich is not possivel to acess the internal code....

Is there any way to do this query?I need basically this fields:

Serial number (Invoice)

Total payment (incoming payments)

Due Date (incoming payments)

I use this SQL statement, but when i join the INV6(that conteins the due date) table it returns me a duplicated records:

SELECT

T1.DocNum ,

T2.Serial ,

T1.CardCod,

T1.Cardname ,

T0.SumApplied ,

FROM dbo.RCT2 T0

LEFT OUTER JOIN dbo.ORCT T1 ON T1.DocNum = T0.DocNum

LEFT OUTER JOIN dbo.OINV T2 ON T2.DocEntry = T0.DocEntry

WHERE T1.CardCode='%0'

Thanks...

[former_member583013]

I am clear on what DueDate you are talking about and why INV6 table. Is you payment terms having installments?

Suda

[Former Member]

Hi,

Yes it has installments. Let me explain better, forget the inv6 table...

I need a query with documents paid in incomming payments wich show the due date (the same field witch show in the Documents for payment)

For example:

There are invoices with the payment divided in 3 parts, each part generates a incoming payment with differents duedate

I need a query that returns all the documents paid with these fields:

Serial number (Invoice)

Total payment (incoming payments)

Due Date (incoming payments/installments)

Note:There are incomming payments that are posted manualy (journal entry), in these case the <serial> field of them is null...

Edited by: Marcio Abreu on May 8, 2008 8:00 PM

[former_member583013]

Can you send your final SQL which has the INV6 join, I want to see where it is repeating.

If you simply want the DueDate on the Incoming Payment it is easy.

Let me know

Suda

[Former Member]

This SQL works fine:

-

---

SELECT

T1.DocNum ,

T2.Serial ,

T1.CardCode ,

T1.Cardname ,

T0.SumApplied ,

T1.DocDate

FROM [dbo].[RCT2] T0

LEFT OUTER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum

LEFT OUTER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry

WHERE T1.CardCode='[%0]'

I need the DueDate Field and Instalment...But returns me a duplicated records, because there are more than one installments related to the invoices (OINV) and there is no key to relate with the RCT2 table (InstlmntID for example)

Resuming...I need the field

(Instalment and DueDate) incomming payments

-

---

SELECT

T1.DocNum ,

T2.Serial ,

T1.CardCode ,

T1.Cardname ,

T0.SumApplied ,

T1.DocDate ,

T3.DueDate,

T3.InstlmntID

FROM [dbo].[RCT2] T0

LEFT OUTER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum

LEFT OUTER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry

LEFT OUTER JOIN INV6 T3 ON T3.DocEntry=T2.DocEntry

WHERE T1.CardCode='[%0]'