Skip to Content
0
Former Member
Jan 23, 2015 at 10:50 PM

Query for Gross Profit

157 Views

All,

I have a query that was written for gross profit, however, I am having issues getting it to bring in the gross profit on items that have yet to be invoiced. Please see the attached screenshot.

Here is the Query that has been written. Can you give me guidance as to what I am missing?

Please let me know if you need any further information.

Thank you for your assistance

SELECT T3.[SlpName], T0.[DocNum], T1.[ItemCode], T1.[LineTotal], T1.[GrssProfit] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode WHERE (T1.[WhsCode] = 'ECOM' AND T3.[U_PRX_SID] = 'INSIDE' AND T2.[U_Commission_Item] = 'Y' AND T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]) OR

(T1.[WhsCode] = 'WIN' AND T3.[U_PRX_SID] = 'INSIDE' AND T2.[U_Commission_Item] = 'Y' AND T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]) OR

(T1.[WhsCode] = 'FAY' AND T3.[U_PRX_SID] = 'INSIDE' AND T2.[U_Commission_Item] = 'Y' AND T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]) OR

(T1.[WhsCode] = 'GRE' AND T3.[U_PRX_SID] = 'INSIDE' AND T2.[U_Commission_Item] = 'Y' AND T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]) OR

(T1.[WhsCode] = 'RAL' AND T3.[U_PRX_SID] = 'INSIDE' AND T2.[U_Commission_Item] = 'Y' AND T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1])

Attachments

Query results.JPG (100.5 kB)