Skip to Content
0
Aug 23, 2023 at 06:21 PM

SAP B1 Query showing customer information but only for customer who have transactions

116 Views

I am trying to pull a query showing the customers information but only for those customers who have made purchases or refunds within the past 2 years. When every I add the OINV table it duplicate or triplicates the entries, depending on the transaction. Any guidance is greatly appreciated.

SELECT right(left(t0.cardcode,4),3) as 'St Acct Open',T0.[CardName],T0.[Address], T0.[City], T0.[State1], T0.[ZipCode],T0.[Country], T1.[Name], T0.[Cellular] as 'Phone Number', T0.[VatStatus] as 'Taxable', T0.[LicTradNum] as 'Resale Code', T0.[Phone2] as 'Loyaly Id', T0.[IntrntSite] as 'BirthMonth', t0.fax as 'Gender', t0.E_mail as 'email', T0.[frozenFor], T0.[Free_Text]

FROM OCRD T0 Left JOIN OCPR T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN OINV T2 ON T0.[CardCode] = T2.[CardCode]

WHERE T0.[CardType] =[%0] and

T0.[CardName] is not Null and

t2.Docdate >= [%1]