Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Sep 02, 2008 at 10:40 PM

    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

    Add comment
    10|10000 characters needed characters exceeded