Linking tables

[Former Member]

Hi all,

Im trying to link the following table with AR INVOICE data. I was planning to have the invoice details in this table.

SELECT T1.CardCode as "CustCode", T1.CardName as "CustName", T2.SlpName, T1.DocNum, T1.DocDate, T1.DocTotal as "Amount Total" FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[ORCT] T1 ON T0.CardCode = T1.CardCode INNER JOIN [dbo].[OSLP] T2 ON T0.SlpCode = T2.SlpCode WHERE T1.DocDate >=[%0] AND T1.DocDate <=[%1] order by T1.DocDate

Appreciate those sql experts to help me.

Thanks.

Harith

[Former Member]

Hi Harith,

Try this:

SELECT T1.CardCode as "CustCode", T1.CardName as "CustName", T2.SlpName, T1.DocNum "Incoming#", T1.DocDate, T1.DocTotal as "Payment Total", T4.DocNum as "Invoice#", T3.SumApplied as "Applied Total"

FROM dbo.OCRD T0

INNER JOIN dbo.ORCT T1 ON T0.CardCode = T1.CardCode

INNER JOIN dbo.RCT2 T3 ON T3.DocNum = T1.DocNum

INNER JOIN dbo.OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN dbo.OINV T4 ON T4.DocEntry = T3.DocENtry AND T3.InvType = '13'

WHERE T1.DocDate >= [%0\] AND T1.DocDate <= [%1\]

order by T1.DocDate

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thanks for your replied response. Can I have a column to calculate the lapse day? e.g in between the incoming Doc date & invoice doc date?

Thanks.

HArith.

[Former Member]

Here you are:

SELECT T1.CardCode as "CustCode", T1.CardName as "CustName", T2.SlpName, T1.DocNum "Incoming#", T1.DocDate, T1.DocTotal as "Payment Total", T4.DocNum as "Invoice#", T3.SumApplied as "Applied Total", DateDiff(DD, T4.DocDate, T1.DocDate) as "Lapse Day"

FROM dbo.OCRD T0

INNER JOIN dbo.ORCT T1 ON T0.CardCode = T1.CardCode

INNER JOIN dbo.RCT2 T3 ON T3.DocNum = T1.DocNum

INNER JOIN dbo.OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN dbo.OINV T4 ON T4.DocEntry = T3.DocENtry AND T3.InvType = '13'

WHERE T1.DocDate >= [%0\] AND T1.DocDate <= [%1\]

order by T1.DocDate

[Former Member]

Hi Gordon,

Thanks alot Gordon!! It was a BIG help...thank again

-Harith-