cancel
Showing results for 
Search instead for 
Did you mean: 

Add To Query

Former Member
0 Kudos

Hello All,

Can we add the Phone # to this Query? Should add right after the BT Zip Code. Thanks!

Mike

SELECT 'Invoice' "Invoice/AR CM", T0.CardCode

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

T0.Address 'BT Address', T2.City 'BT City', T2.State1 'BT State', T2.ZipCode 'BT Zip', T0.Address2 'ST Address',

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' "Credit", T0.CardCode, T0.CardName,

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

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 'Group'

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]')

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Mike,

Try this:


SELECT 'Invoice' "Invoice/AR CM", T0.CardCode
"BP Code", T0.CardName 'Company',
T0.Address 'BT Address', T2.City 'BT City', T2.State1 'BT State', T2.ZipCode 'BT Zip', 
T2.Phone1 'BT Phone', T0.Address2 'ST Address',
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' "Credit", T0.CardCode, T0.CardName,
T0.Address, T2.City, T2.State1, T2.ZipCode, T2.Phone1,T0.Address2,
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 'Group'
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

Answers (1)

Answers (1)

jitin_chawla
Advisor
Advisor
0 Kudos

Mike,

Check this

SELECT 'Invoice' "Invoice/AR CM", T0.CardCode

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

T0.Address 'BT Address', T2.City 'BT City', T2.State1 'BT State', T2.ZipCode 'BT Zip', T2.Phone1, T2.Phone2, T0.Address2 'ST Address',

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' "Credit", T0.CardCode, T0.CardName,

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

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 'Group'

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')

Regards,

Jitin