Query Amendment - Any Assistance Appreciated!

[richard_thurlow]

Hi,

I need to make a few amends to an existing query that I have:

--INCLUDES PRE-SAP HISTORY, DISCOUNTS & CREDITS

Declare @CURRENCY Nvarchar(50) = /* SELECT FROM OINV Q0 WHERE Q0.DocCur = */ '[%0]'

Declare @FRDT DateTime    = /* SELECT FROM OINV Q1 WHERE Q1.DocDate >= */ '[%1]'

Declare @TODT DateTime = /* SELECT FROM OINV Q2 WHERE Q2.DocDate <= */ '[%2]'

SELECT X.[DOCNUM], X.[DATE], SUM(QTY) [QTY], X.[CODE], X.[DESC], SUM(SALES) as [SALES], X.[ORIGIN], X.[MATERIAL]

FROM 

(

  SELECT Y.[DOCNUM], Y.[DATE], SUM(Y.QTY) [QTY], Y.[CODE], Y.[DESC], Y.[SALES], Y.[ORIGIN], Y.[MATERIAL]

    FROM (

SELECT T0.[DocNum] as [DOCNUM],

             CONVERT(VARCHAR(10),T0.[DocDate],103) as [DATE],

             T1.[Quantity] as [QTY],

             T1.[ItemCode] as [CODE],

             T1.[Dscription] as [DESC],

             Case T0.[DocCur] When 'AUD' then T1.[LineTotal] else T1.[TotalFrgn] end as [SALES],

             T2.[U_Origin] as [ORIGIN],

             T2.[U_NZTariff] as [MATERIAL]

FROM OINV T0

  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OITM T2 on T1.ItemCOde = T2.ItemCode

  LEFT JOIN RDR1 T3 ON T1.BaseEntry = T3.DocEntry AND T1.BaseLine = T3.LineNum AND T1.BaseType='17'

WHERE T0.[DocCur] = @CURRENCY AND

  T1.[OpenQty] <> 0 AND

  T0.[DocDate] BETWEEN @FRDT AND @TODT AND

  ISNULL(T3.OpenQty,0)=0

UNION ALL

SELECT T0.[DocNum] as [DOCNUM],

             CONVERT(VARCHAR(10),T0.[U_Date],103) as [DATE],

             T1.[U_Qty] as [QTY],

             T1.[U_ItemNo] as [CODE],

             T1.[U_Description] as [DESC],

             Case T0.[U_Currency] When 'AUD' then T1.[U_RowTotal] else T1.[U_RowTotalFC] end as [SALES],

             T2.[U_Origin] as [ORIGIN],

             T2.[U_NZTariff] as [MATERIAL]

FROM [@OINV] T0

  INNER JOIN [@INV1] T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OITM T2 on T1.U_ItemNo = T2.ItemCode

WHERE T0.[U_Currency] = @CURRENCY AND

  T1.[U_Qty] <> 0 AND

  T0.[U_Date] BETWEEN @FRDT AND @TODT

UNION ALL

SELECT T0.[DocNum] as [DOCNUM],

             CONVERT(VARCHAR(10),T0.[DocDate],103) as [DATE],

             T1.[Quantity]*-1 as [QTY],

             T1.[ItemCode] as [CODE],

             T1.[Dscription] as [DESC],

             Case T0.[DocCur] when 'AUD' then T1.[LineTotal]*-1 else T1.[TotalFrgn]*-1 end as [SALES],

             T2.[U_Origin] as [ORIGIN],

             T2.[U_NZTariff] as [MATERIAL]

FROM ORIN T0

  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

WHERE T0.[DocCur] = @CURRENCY AND

  T0.[DocDate] BETWEEN @FRDT AND @TODT

     ) Y

     GROUP BY Y.[DOCNUM], Y.[DATE], Y.[CODE], Y.[DESC], Y.[SALES], Y.[ORIGIN], Y.[MATERIAL]

) X

GROUP BY X.[DOCNUM],X.[DATE], X.[CODE], X.[DESC], X.[ORIGIN], X.[MATERIAL]

This query will ask the user for the document currency as well as the posting date range.

This works fine for what it is - but I need to add to it.

I need to add the following columns:

Gross Profit

Gross Profit %

Sales Tax

COGS within selected currency

I am having difficulties with this - as I know how to find out the gross profit with the system currency - but not the user selected currency. For example - our system currency is AUD and I need to do a report for NZD.

Any help with this would be appreciated.

Regards

Rick

[Johan_H]

Hi Rick,

If you wrote this query yourself, I have to say I am proud of you. That is a good looking and complicated query.

For the Gross profit and percentage, you can use the GrossBuyPr field from INV1 and RIN1

For the sales tax (VAT I take it ?), you will have to elaborate a little, because you can go through INV1 / RIN1 or through OINV / ORIN, depending on whether you just need a bulk sum, or say an item-wise percentage.

Sorry, COGS means what ? Cognitively and Overtly Gallivanting Scientists ?

Regards,

Johan

[richard_thurlow]

Hi Johan,

I'm not going to take full credit for this query - I think I may have started it - but not finished it...

With regards to COGS - Cost of Goods Sold - so I guess:

(AvgPrice * Quantity Sold)

The problem with doing this - is the AvgPrice on the OITM table is the system currency - rather than the selected currency.

Sales Tax = VAT, yep

This query is listing out all items sold - so it would be per line.

The sales tax varies based on the currency selected - for example AUD has a VAT tax at 10%. NZD has it set at 15%.

Cheers

Rick

[Johan_H]

AvgPrice, you may want take from OITW for accurracy. You should join OITW on both the ItemCode and warehouse code fields:

FROM INV1

INNER JOIN OITW ON INV1.ItemCode = OITW.ItemCode AND INV1.WhsCode = OITW.WhsCode

About the currency, in your query as is, the @CURRENCY parameter is actually limiting the result set to documents that were created in the given currency.

That means that assuming that all or most documents in your system were added with AUD, and you were to choose NZD, you would not get any results, or at least not nearly all results you should.

Before I can say anything useful about the VAT thing, can you tell me the premise of this query ? What is it supposed to tell us ?

Regards,

Johan

[richard_thurlow]

Hi Johan,

I have tried it another way - I believe I have got the first part right - but when I try to UNION the ORIN query - I can't recall how to do this, do you think so?:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotalFC]- T0.[DiscSumFC]- T0.[VatSumFC] as 'Total Sales ex GST',T0.[VatSumFC] as 'Total Tax',T0.[DocTotalFC],  T0.[GrosProfFC], T0.[DocTotalFC]- T0.[DiscSumFC]- T0.[VatSumFC]-T0.[GrosProfFC] as 'COGS NZD' FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE T0.[DocCur] = 'nzd' and (T0.[DocDate] between [%0] and [%1])

UNION ALL

SELECT T3.[DocNum], T3.[DocDate], T3.[CardCode], T3.[CardName], (T3.[DocTotalFC]- T3.[DiscSumFC]- T3.[VatSumFC])*-1 as 'Total Sales ex GST',T3.[VatSumFC]*-1 as 'Total Tax',T3.[DocTotalFC]*-1,  T3.[GrosProfFC]*-1, (T3.[DocTotalFC]- T3.[DiscSumFC]- T3.[VatSumFC]-T3.[GrosProfFC]) as 'COGS NZD' FROM ORIN T3  JOIN RIN1 T4 ON T3.DocEntry = T4.DocEntry INNER JOIN OITM T5 ON T4.ItemCode = T5.ItemCode WHERE T3.[DocCur] = 'nzd' and (T3.[DocDate] between [%0] and [%1])

GROUP BY T0.[DocNum], T0.[DocDate]

Any help here would be great!

Cheers

Rick

[Johan_H]

Hi Rick,

This query looks correct, but the result set will look different in that it will give you separate lines for invoices and credit notes, instead of separate columns.

Does this query give you the expected result ?

Regards,

Johan

[richard_thurlow]

Hi Johan,

The issue I am running into is that it isn't giving me one line per docnum - rather it is giving me a line for each product line on the invoice.

Regard

Rick

[Johan_H]

Okay, that is fortunately an easy one. You have joined INV1 and RIN1, which are the lines tables. However, you do not need them, so give it a try without:

SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T0.[DocTotalFC]- T0.[DiscSumFC]- T0.[VatSumFC] as 'Total Sales ex GST'

     , T0.[VatSumFC] as 'Total Tax'

     , T0.[DocTotalFC]

     , T0.[GrosProfFC]

     , T0.[DocTotalFC]- T0.[DiscSumFC]- T0.[VatSumFC]-T0.[GrosProfFC] as 'COGS NZD'

FROM OINV T0

WHERE T0.[DocCur] = 'EUR' and (T0.[DocDate] between [%0] and [%1])

UNION ALL

SELECT T3.[DocNum]

     , T3.[DocDate]

     , T3.[CardCode]

     , T3.[CardName]

     , (T3.[DocTotalFC]- T3.[DiscSumFC]- T3.[VatSumFC]) * -1 as 'Total Sales ex GST'

     , T3.[VatSumFC] * -1 as 'Total Tax'

     , T3.[DocTotalFC] * -1

     , T3.[GrosProfFC] * -1

     , (T3.[DocTotalFC]- T3.[DiscSumFC]- T3.[VatSumFC]-T3.[GrosProfFC]) as 'COGS NZD'

FROM ORIN T3 

WHERE T3.[DocCur] = 'EUR'

  and (T3.[DocDate] between [%0] and [%1])

Regards,

Johan