Skip to Content
0
Former Member
Sep 02, 2008 at 10:24 PM

Modifying Another Query

8 Views

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