Running customer report showing only one record of the invoice and last payment date please

[former_member1143779]

Hi there,

I am trying to run a query report of closed customer invoices. Sometimes, customers make multiple payments on different dates towards an invoice to close it out. However, I am trying to run this query to bring record of only one unique invoice with its corresponding last payment date recorded (which brought the invoice status to a close).

Could someone please assist on how I could pull that record up?

Below is my syntax:

SELECT

T2."DocNum" "Invoice#",

T0."CardCode" "Customer Code",

T0."CardName" "Customer Name",

CONCAT(CONCAT(T0."CardName",' ('),(CONCAT(T0."CardCode",')'))) AS "BusinessPartnerNameandCode",

T2."DocStatus",

T0."DocNum" "Last Payment No.",

T2."DocDate" "Invoice Date1",

T2."TaxDate" "Invoice Date2",

T0."DocDate" "Last Payment Date",

DAYS_BETWEEN(T2."TaxDate", T0."DocDate") "Days to Payment",

T2."DocTotal",

T4."PymntGroup",

T3."QryGroup3" "CALGA",

T3."QryGroup4" "REDER",

T3."QryGroup5" "COLMB",

T3."QryGroup6" "MEDIC",

T3."QryGroup7" "PRGEO"

FROM ORCT T0

JOIN RCT2 T1

ON T1."DocNum" = T0."DocNum"

JOIN OINV T2

ON T2."DocEntry" = T1."DocEntry"

JOIN OCRD T3

ON T3."CardCode" = T0."CardCode"

JOIN OCTG T4

ON T4."GroupNum" = T3."GroupNum"

WHERE

T2."DocStatus"='C'

Thank you,

LY

[kothandaraman_nagarajan]

Hi,

Try this query,

SELECT

T2."DocNum" "Invoice#",

T0."CardCode" "Customer Code",

T0."CardName" "Customer Name",

CONCAT(CONCAT(T0."CardName",' ('),(CONCAT(T0."CardCode",')'))) AS "BusinessPartnerNameandCode",

T2."DocStatus",

T0."DocNum" "Last Payment No.",

T2."DocDate" "Invoice Date1",

T2."TaxDate" "Invoice Date2",

MAX(T0."DocDate") "Last Payment Date",

DAYS_BETWEEN(T2."TaxDate", T0."DocDate") "Days to Payment",

T2."DocTotal",

T4."PymntGroup",

T3."QryGroup3" "CALGA",

T3."QryGroup4" "REDER",

T3."QryGroup5" "COLMB",

T3."QryGroup6" "MEDIC",

T3."QryGroup7" "PRGEO"

FROM ORCT T0

JOIN RCT2 T1

ON T1."DocNum" = T0."DocNum"

JOIN OINV T2

ON T2."DocEntry" = T1."DocEntry"

JOIN OCRD T3

ON T3."CardCode" = T0."CardCode"

JOIN OCTG T4

ON T4."GroupNum" = T3."GroupNum"

WHERE

T2."DocStatus"='C'

GROUP BY

T2."DocNum" , T0."CardCode" , T0."CardName" , T0."CardName",T0."CardCode", T2."DocStatus", T0."DocNum" , T2."DocDate" , T2."TaxDate" , T2."TaxDate", T0."DocDate", T2."DocTotal", T4."PymntGroup", T3."QryGroup3" , T3."QryGroup4" , T3."QryGroup5" , T3."QryGroup6" , T3."QryGroup7"

Regards,

Nagarajan