Skip to Content
avatar image
Former Member

How do I add a Column for total doc quantity to this query?

SELECT

T0.DocNum AS 'Invoice Nr',
T0.DocStatus, T0.Jrnlmemo,
T0.NumAtCard AS 'Customer Ref Nr',

T1.SlpName AS 'Salesperson',

--T0.SlpCode AS 'SP Code',

T0.DocDate AS 'Posting Date',

T0.CardCode AS 'Customer Code',

T0.CardName AS 'CustomerName',

T0.Address2 AS 'ShipToAddress',

'Credit Memos' AS 'Doc Type',

((T0.DocTotal) * -1) AS 'Total Docs',

(((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales',

((T0.GrosProfit) * -1) AS 'Gross Profit',

CASE

WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) = 0.00 THEN 0.00

ELSE (((T0.GrosProfit) * -1)/(((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100

END AS 'Gross Profit %',

((T0.PaidToDate) * -1) AS 'Paid To Date',

((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs'

FROM ORIN T0

INNER JOIN OSLP T1

ON T0.SlpCode = T1.SlpCode

WHERE

(T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ')

AND T0.TaxDate >= '[%1]'

AND T0.TaxDate <= '[%2]'

UNION

SELECT

T0.DocNum AS 'Invoice Nr',
T0.DocStatus, T0.Jrnlmemo,
T0.NumAtCard AS 'Customer Ref Nr',

T1.SlpName AS 'Salesperson',

--T0.SlpCode AS 'SP Code',

T0.DocDate AS 'Posting Date',

T0.CardCode AS 'Customer Code',

T0.CardName AS 'CustomerName',

T0.Address2 AS 'ShipToAddress',

'Invoices' AS 'Doc Type',

(T0.DocTotal) AS 'Total Docs',

((T0.DocTotal - T0.VatSum) - T0.TotalExpns) AS 'Product Sales',

(T0.GrosProfit) AS 'Gross Profit',

CASE

WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) = 0.00 THEN 0.00

ELSE ((T0.GrosProfit)/((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100

END AS 'Gross Profit %',

(T0.PaidToDate) AS 'Paid To Date',

(T0.DocTotal - T0.PaidToDate) AS 'Open on Docs'

FROM OINV T0

INNER JOIN OSLP T1

ON T0.SlpCode = T1.SlpCode

WHERE

(T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ')

AND T0.TaxDate >= '[%1]'

AND T0.TaxDate <= '[%2]'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Jan 30, 2017 at 02:20 PM

    Hi Justin,

    Please note that because Nagarajan did not format his code, a larger part of his solution is commented out, than in your original query. In general it is preferable to use the /* comment here */ syntax instead of the -- comment here syntax.

    Please try this adaptation of Nagarajan's query:

    SELECT T0.DocNum AS 'Credit Nr'
         , T0.DocStatus
     , T0.Jrnlmemo
     , T0.NumAtCard AS 'Customer Ref Nr'
     , T1.SlpName AS 'Salesperson'
     , T0.DocDate AS 'Posting Date'
     , T0.CardCode AS 'Customer Code'
     , T0.CardName AS 'CustomerName'
     , T0.Address2 AS 'ShipToAddress'
     , 'Credit Memos' AS 'Doc Type'
     , ((T0.DocTotal) * -1) AS 'Total Docs'
     , (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales'
     , ((T0.GrosProfit) * -1) AS 'Gross Profit'
     , CASE
            WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) = 0.00 THEN 0.00 
            ELSE (((T0.GrosProfit) * -1)/(((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100 
           END AS 'Gross Profit %'
         , ((T0.PaidToDate) * -1) AS 'Paid To Date'
         , ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs'
     , SUM(T2.Quantity) as TotalQty
    FROM ORIN T0 
         INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode 
     INNER JOIN RIN1 T2 on T2.Docentry = T0.Docentry
    WHERE (T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') 
      AND T0.TaxDate >= '[%1]' 
      AND T0.TaxDate <= '[%2]'
    GROUP BY T0.Docnum,T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard, T1.SlpName , T0.DocDate , T0.CardCode , T0.CardName , T0.Address2 , T0.DocTotal, T0.VatSum, T0.TotalExpns, T0.GrosProfit, T0.PaidToDate
    union all
    SELECT T0.DocNum AS 'Invoice Nr'
         , T0.DocStatus
     , T0.Jrnlmemo
     , T0.NumAtCard AS 'Customer Ref Nr'
     , T1.SlpName AS 'Salesperson'
     , T0.DocDate AS 'Posting Date'
     , T0.CardCode AS 'Customer Code'
     , T0.CardName AS 'CustomerName'
     , T0.Address2 AS 'ShipToAddress'
     , 'Invoices' AS 'Doc Type'
     , ((T0.DocTotal) * -1) AS 'Total Docs'
     , (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales'
     , ((T0.GrosProfit) * -1) AS 'Gross Profit'
     , CASE 
        WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) = 0.00 THEN 0.00 
    ELSE (((T0.GrosProfit) * -1)/(((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100 
       END AS 'Gross Profit %'
     , ((T0.PaidToDate) * -1) AS 'Paid To Date'
     , ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs'
     , -SUM(T2.Quantity) as TotalQty
    FROM OINV T0 
         INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode 
     INNER JOIN INV1 T2 on T2.Docentry = T0.Docentry
    Where (T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') 
      AND T0.TaxDate >= '[%1]' 
      AND T0.TaxDate <= '[%2]'
    Group by T0.Docnum,T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard, T1.SlpName , T0.DocDate , T0.CardCode , T0.CardName , T0.Address2 , T0.DocTotal, T0.VatSum, T0.TotalExpns, T0.GrosProfit, T0.PaidToDate

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 26, 2017 at 01:43 AM

    Hi,

    Ignore above query. Try this,

    SELECT T0.DocNum AS 'Credit Nr', T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard AS 'Customer Ref Nr', T1.SlpName AS 'Salesperson', --T0.SlpCode AS 'SP Code', T0.DocDate AS 'Posting Date', T0.CardCode AS 'Customer Code', T0.CardName AS 'CustomerName', T0.Address2 AS 'ShipToAddress', 'Credit Memos' AS 'Doc Type', ((T0.DocTotal) * -1) AS 'Total Docs', (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales', ((T0.GrosProfit) * -1) AS 'Gross Profit', CASE WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) = 0.00 THEN 0.00 ELSE (((T0.GrosProfit) * -1)/(((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100 END AS 'Gross Profit %', ((T0.PaidToDate) * -1) AS 'Paid To Date', ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs', SUM(T2.Quantity) as TotalQty

    FROM ORIN T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN RIN1 T2 on T2.Docentry = T0.Docentry

    Where (T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND T0.TaxDate >= '[%1]' AND T0.TaxDate <= '[%2]'

    Group by T0.Docnum,T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard, T1.SlpName , T0.DocDate , T0.CardCode , T0.CardName , T0.Address2 , T0.DocTotal, T0.VatSum, T0.TotalExpns, T0.GrosProfit, T0.PaidToDate

    union all

    SELECT T0.DocNum AS 'Invoice Nr', T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard AS 'Customer Ref Nr', T1.SlpName AS 'Salesperson', --T0.SlpCode AS 'SP Code', T0.DocDate AS 'Posting Date', T0.CardCode AS 'Customer Code', T0.CardName AS 'CustomerName', T0.Address2 AS 'ShipToAddress', 'Credit Memos' AS 'Doc Type', ((T0.DocTotal) * -1) AS 'Total Docs', (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales', ((T0.GrosProfit) * -1) AS 'Gross Profit', CASE WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) = 0.00 THEN 0.00 ELSE (((T0.GrosProfit) * -1)/(((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100 END AS 'Gross Profit %', ((T0.PaidToDate) * -1) AS 'Paid To Date', ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs', -SUM(T2.Quantity) as TotalQty

    FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2 on T2.Docentry = T0.Docentry

    Where (T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND T0.TaxDate >= '[%1]' AND T0.TaxDate <= '[%2]'

    Group by T0.Docnum,T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard, T1.SlpName , T0.DocDate , T0.CardCode , T0.CardName , T0.Address2 , T0.DocTotal, T0.VatSum, T0.TotalExpns, T0.GrosProfit, T0.PaidToDate

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 25, 2017 at 10:53 PM

    Hi,

    Try this,

    SELECT T0.DocNum AS 'Invoice Nr', T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard AS 'Customer Ref Nr', T1.SlpName AS 'Salesperson', --T0.SlpCode AS 'SP Code', T0.DocDate AS 'Posting Date', T0.CardCode AS 'Customer Code', T0.CardName AS 'CustomerName', T0.Address2 AS 'ShipToAddress', 'Credit Memos' AS 'Doc Type', ((T0.DocTotal) * -1) AS 'Total Docs', (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales', ((T0.GrosProfit) * -1) AS 'Gross Profit', CASE WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) = 0.00 THEN 0.00 ELSE (((T0.GrosProfit) * -1)/(((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100 END AS 'Gross Profit %', ((T0.PaidToDate) * -1) AS 'Paid To Date', ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs', SUM(T2.Quantity] as TotalQty FROM ORIN T0 inner join RIN1 T2 on T0.Docentry = T2.Docentry INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode WHERE (T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND T0.TaxDate >= '[%1]' AND T0.TaxDate <= '[%2]'

    UNION

    SELECT T0.DocNum AS 'Invoice Nr', T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard AS 'Customer Ref Nr', T1.SlpName AS 'Salesperson', --T0.SlpCode AS 'SP Code', T0.DocDate AS 'Posting Date', T0.CardCode AS 'Customer Code', T0.CardName AS 'CustomerName', T0.Address2 AS 'ShipToAddress', 'Invoices' AS 'Doc Type', (T0.DocTotal) AS 'Total Docs', ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) AS 'Product Sales', (T0.GrosProfit) AS 'Gross Profit', CASE WHEN (((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) = 0.00 THEN 0.00 ELSE ((T0.GrosProfit)/((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100 END AS 'Gross Profit %', (T0.PaidToDate) AS 'Paid To Date', (T0.DocTotal - T0.PaidToDate) AS 'Open on Docs', SUM(T2.Quantity] as TotalQty FROM OINV T0 inner join INV1 T2 on T0.Docentry = T2.Docentry INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode WHERE (T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND T0.TaxDate >= '[%1]' AND T0.TaxDate <= '[%2]'

    Group by T0.DocNum , T0.DocStatus, T0.Jrnlmemo, T0.NumAtCard, T1.SlpName , T0.DocDate , T0.CardCode , T0.CardName , T0.Address2 , T0.DocTotal, T0.VatSum, T0.TotalExpns, T0.GrosProfit, T0.PaidToDate

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 30, 2017 at 01:45 PM

    Dear Nagarajan K I appreciate your inquiry but it was unsuccessful. There wasn't an error but the report doesn't run. Do you have any other advise for me to trouble shoot the report?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 30, 2017 at 04:36 PM

    Dear Johan & Nagarajan

    I believe we have it thank you both very much for your assistance.

    Best Regards,

    Justin

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 30, 2017 at 04:36 PM

    Dear Johan & Nagarajan

    I believe we have it thank you both very much for your assistance.

    Best Regards,

    Justin

    Add comment
    10|10000 characters needed characters exceeded