Skip to Content
0
Jul 15, 2015 at 11:31 PM

Help with 2 queries - sales orders greater than a specific "in stock" value

262 Views

Hi,

I was hoping that you could help me with two queries I am trying to write.

I want to have a query that I can run, that will prompt me for a sales person and then display all customers for that sales person that have a sales order which has available stock on it that comes to equal to or greater than $100 that isn't already on a picklist.

Here is what I have currently:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

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

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE (T1.[PickStatus] = 'N') AND (T1.[OpenQty] > '0') and T2.[SlpName] = [%0]

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

I need to add in the WHERE clauses to indicate that the sales order needs to be have "in stock" product of over $100.

By "in stock" - this would mean Available to Release, ensuring that there is enough stock in the OWHS.[WhsCode] = 01. Main that isn't already on picklists.

I also want to have a similar query that can run - but this time I only want to have the following fields:

SELECT T0.[CardCode], T0.[CardName], sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

This way it will total up the available to release stock across multiple orders toascertain if it is above the $100, considering that should there be multiple sales orders below the $100 threshold, it wouldn't show up in the first query - so could be missed.

Let me know if the above needs clarification.

Any help would be much appreciated.

Regards

Rick