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

DueDate field in the Incoming payments

I need know the Incoming payments paid with the respective duedates..

for this, i use this SQL statement:

SELECT

T1.DocNum ,

T2.Serial ,

T1.CardCode ,

T1.Cardname ,

T6.GroupName,

T4.SlpName ,

T3.CityS ,

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

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

LEFT OUTER JOIN OSLP T4 ON T4.SlpCode=T2.SlpCode

LEFT OUTER JOIN OCRD T5 ON T5.CardCode=T2.CardCode

LEFT OUTER JOIN OCRG T6 ON T6.GroupCode=T5.GroupCode

WHERE T1.CardCode='[%0]'

How can i add the duedate field from INV6 table, without duplicate records in this select?There is any other duedate field

in another table that corresponds the INV6 table(duedate)?

Thanks

Edited by: Philip Eller on May 20, 2008 9:25 AM

Edited by: Philip Eller on Jun 3, 2008 8:37 AM*

Edited by: Philip Eller on Jun 17, 2008 9:30 AM**

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 May 06, 2008 at 03:07 PM

    What are you exactly referring by "respective duedates.."

    Is it the Invoice's DUE DATE. (DocDueDate) ?

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Thanks for reply...

      In this case, i showing the date of the first record and does not the date relative to the payment...

      if i add the field <InstlmntID> in the sql statement

      (select top 1 InstlmntID from inv6 where docentry = T2.docentry) as id)

      it shows wrong value...

  • author's profile photo Former Member
    Former Member
    Posted on May 06, 2008 at 03:28 PM

    add subselect into your query as

    SELECT

    (select top 1 docduedate from inv6 where xxxx) as duedate

    T1.DocNum ,

    T2.Serial ,

    T1.CardCode ,

    T1.Cardname ,

    T6.GroupName,

    T4.SlpName ,

    T3.CityS ,

    T0.SumApplied,

    T1.DocDate ,

    i

    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 INV12 T3 ON T3.DocEntry=T2.DocEntry

    LEFT OUTER JOIN OSLP T4 ON T4.SlpCode=T2.SlpCode

    LEFT OUTER JOIN OCRD T5 ON T5.CardCode=T2.CardCode

    LEFT OUTER JOIN OCRG T6 ON T6.GroupCode=T5.GroupCode

    WHERE T1.CardCode='%0'

    Add a comment
    10|10000 characters needed characters exceeded

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.