Skip to Content
0

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

Jan 25, 2017 at 02:31 PM

92

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Johan Hakkesteegt Jan 30, 2017 at 02:20 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jan 26, 2017 at 01:43 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jan 25, 2017 at 10:53 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 30, 2017 at 01:45 PM
0

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?

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 30, 2017 at 04:36 PM
0

Dear Johan & Nagarajan

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

Best Regards,

Justin

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 30, 2017 at 04:36 PM
0

Dear Johan & Nagarajan

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

Best Regards,

Justin

Share
10 |10000 characters needed characters left characters exceeded