Adding to Another Query

[Former Member]

Hi All!

Also, for this Query we want to add BP Customer Group to the end as well.

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!

Mike

[Former Member · Accepted solution]

Here you are:

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", 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,
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)),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

[Former Member]

Hi Mike,

as I answer to your other thread

you need to join OCRD with OCRG table

SELECT OCRG.GroupName FROM OCRD left join OCRG on OCRD.GroupCode = OCRG.GroupCode

....

Regards,

Marta