Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Adding to Query

Hello All!

We would like to add our BP Customer Group to the Query below. Does anyone know how to do this? Just want another column at the very end that indicates which Group the customer belongs to.

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode

"BP Code", T0.CardName "Company",

T0.Address, T2.City, T2.State1, T2.ZipCode, T2.Phone1,

T3.Name, T0.DocDate,T0.DocNum,

T0.DocTotal "Gross Amt", T0.TotalExpns "Freight/Svc Charge",

(T0.DocTotal - T0.VatSum-T0.TotalExpns) "Net Order",

T1.SlpName "Sales Rep",

T1.Commission "% Commision",

((T0.DocTotal - T0.VatSum-T0.TotalExpns)*(T1.Commission/100))

"Commission Amount"

FROM dbo.OINV T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode

LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode

LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode

WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND

(T0.DocDate BETWEEN '[%2]' AND '[%3]')

UNION ALL

SELECT 'Credit Memo', T0.CardCode, T0.CardName,

T0.Address, T2.City, T2.State1, T2.ZipCode, T2.Phone1,

T3.Name, T0.DocDate,T0.DocNum, -T0.DocTotal,

-T0.TotalExpns, -(T0.DocTotal - T0.VatSum-T0.TotalExpns),

T1.SlpName, T1.Commission, ((T0.DocTotal - T0.VatSum-

T0.TotalExpns)*(-T1.Commission/100))

FROM dbo.ORIN T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode

LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode

LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode

WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND

(T0.DocDate BETWEEN '[%2]' AND '[%3]')

Thanks!

Mike

replied

Mike,

Here you are:

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode
"BP Code", T0.CardName "Company",
T0.Address, T2.City, T2.State1, T2.ZipCode, T2.Phone1,
T3.Name, T0.DocDate,T0.DocNum,
T0.DocTotal "Gross Amt", T0.TotalExpns "Freight/Svc Charge",
(T0.DocTotal - T0.VatSum-T0.TotalExpns) "Net Order",
T1.SlpName "Sales Rep",
T1.Commission "% Commision",
((T0.DocTotal - T0.VatSum-T0.TotalExpns)*(T1.Commission/100))
"Commission Amount", T4.GroupName 'Group'
FROM dbo.OINV T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode
LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode
LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode
WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND 
(T0.DocDate BETWEEN '[%2]' AND '[%3]') 
UNION ALL
SELECT 'Credit Memo', T0.CardCode, T0.CardName,
T0.Address, T2.City, T2.State1, T2.ZipCode, T2.Phone1,
T3.Name, T0.DocDate,T0.DocNum, -T0.DocTotal,
-T0.TotalExpns, -(T0.DocTotal - T0.VatSum-T0.TotalExpns),
T1.SlpName, T1.Commission, ((T0.DocTotal - T0.VatSum-
T0.TotalExpns)*(-T1.Commission/100)), T4.GroupName
FROM dbo.ORIN T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode
LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode
LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode
WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND
 (T0.DocDate BETWEEN '[%2]' AND '[%3]') 

Thanks,

Gordon

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question