Modifying Detailed Sales Report Query
Hello All --
Can we adjust the Query below so that the current Company, Address, City, State and Zip have the letters BT in front (for Bill To)?
Then, can we add new columns of the Company, Address, City, State and Zip for the Ship To with ST in the front of the names?
Thanks!!
Mike
Gordon Du replied
Here you are:
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]')
Thanks,
Gordon