Skip to Content

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

Modifying Another Query

Hi All --

We have this Query below. Is there any way we can add to the Query so that it shows the Billing State in a separate column next to the company?

So everything is the same, except in a column next to the Company it shows the Billing State.

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode "BP Code", T0.CardName "Company", 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 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND (T0.DocDate BETWEEN '[%2]' AND '[%3]')

UNION ALL

SELECT 'Credit Memo', T0.CardCode, T0.CardName, 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 INNER JOIN [dbo].[OSLP] T1 ON T0.SlpCode = T1.SlpCode

WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND (T0.DocDate BETWEEN '[%2]' AND '[%3]')

Thanks!!

Mike

Former Member
Former Member replied

Hi Mike,

Try this:

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode 
"BP Code", T0.CardName "Company",  T2.State1,
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 
INNER JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.CardCode
WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND 
(T0.DocDate BETWEEN '[%2]' AND '[%3]')
UNION ALL
SELECT 'Credit Memo', T0.CardCode, T0.CardName,  
T2.State1,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 
INNER JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN  dbo.OCRD T2 ON T2.CardCode = T0.CardCode
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