on 07-16-2015 12:31 AM
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
Hi,
Try this query based on your last reply.
DECLARE @SalesPersonName AS NVARCHAR(32)
SET @SalesPersonName = (select ta.slpname from OSLP ta where ta.slpname = [%0])
SELECT X.CardCode
,X.CardName
,SUM(X.[$ Value]) as '$ Value'
FROM
(SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = @SalesPersonName
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100) X
GROUP BY X.CardCode
,X.CardName
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Richard
you can try this
SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], sum ( T1.[OpenQty] * T1.[Price] ) as '$ value' FROM [dbo].[ORDR] T0 INNER JOIN [dbo].[RDR1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[OSLP] T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OITW T4 ON T3.ItemCode = T4.ItemCode AND T1.WhsCode = T4.WhsCode WHERE (T1.[PickStatus] = 'N') AND (T1.[OpenQty] > '0') and T2.[SlpName] = [%0] and ( T4.[OnHand] * T1.[Price] ) > 10 GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName] , T1.[openqty] , T1.[Price]
Regards
Ranu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ranu,
Thank you for this.
However - when I run this query it doesn't work as intended.
I am looking for a query which will have one line per sales order, and check to see if the sum of the product that can be supplied is over $100.
Your query looks like it is displaying one line per each product.
Regards
Rick
Hi Rick,
Your query is already almost complete. Just add a couple of lines:
SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, T3.OnHand * T3.AvgPrice AS StockTotalValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = [%0]
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
HAVING (T3.OnHand * T3.AvgPrice) >= 100
Then once you have your detailed data, you can use an encapsulating query to get the totaled data:
SELECT X.CardCode
,X.CardName
,SUM(X.[$ Value]) as '$ Value'
FROM
(SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, T3.OnHand * T3.AvgPrice AS StockTotalValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = [%0]
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
HAVING (T3.OnHand * T3.AvgPrice) >= 100) X
GROUP BY X.CardCode
,X.CardName
Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rick,
Considering that the B1 query variable ([%0]) is now in the embedded query, you might be right.
You can test the query in SQL Management Studio to see if it works for you otherwise.
For the variable to work in B1, you would have to add something like this at the top:
DECLARE @SalesPersonName AS NVARCHAR(32) /* SELECT SlpName FROM OSLP WHERE SlpName */ = [%0]
and replace the [%0] variable in the WHERE clause with @SalesPersonName
My own B1 version is too old to understand this syntax, so I cannot test it myself. You will probably have to play around with it, and/or search the forum for examples.
Regards,
Johan
Hi Johan,
OK this is what I've got at the moment:
DECLARE @SalesPersonName AS NVARCHAR(32) /* SELECT SlpName FROM OSLP WHERE SlpName */ = [%0]
SELECT X.CardCode
,X.CardName
,SUM(X.[$ Value]) as '$ Value'
FROM
(SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, T3.OnHand * T3.AvgPrice AS StockTotalValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = @SalesPersonName
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
HAVING (T3.OnHand * T3.AvgPrice) >= 100) X
GROUP BY X.CardCode
,X.CardName
But when I put it into the SQL Management Studio - it comes up with an error:
Msg 207, Level 16, State 1, Line 1
Invalid column name '%0'
Any further thoughts on this?
Thanks
Rick
Hi Rick,
Like I said, I can't test this myself, but I took a look at another post with a similar construction.
Could you please change this:
DECLARE @SalesPersonName AS NVARCHAR(32) /* SELECT SlpName FROM OSLP WHERE SlpName */ = [%0]
to this:
DECLARE @SalesPersonName AS NVARCHAR(32)
SET @SalesPersonName = SELECT SlpName FROM OSLP WHERE SlpName = '[%0]'
Oh, and test this in B1, not SQL Management Studio.
Regards,
Johan
Hi Rick,
Ok, lets (double) check a few things, because when I run the following query on my system, I get results:
/* SELECT * FROM OSLP sp */
DECLARE @SalesPersonName AS NVARCHAR(32)
SET @SalesPersonName = /* sp.SlpName */ '[%0]'
SELECT X.CardCode
,X.CardName
,SUM(X.[$ Value]) as '$ Value'
FROM
(SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, T3.OnHand * T3.AvgPrice AS StockTotalValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = @SalesPersonName
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
HAVING (T3.OnHand * T3.AvgPrice) >= 100) X
GROUP BY X.CardCode
,X.CardName
/* SELECT * FROM OSLP sp */
DECLARE @SalesPersonName AS NVARCHAR(32)
SET @SalesPersonName = /* sp.SlpName */ '[%0]'
SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, T3.OnHand * T3.AvgPrice AS StockTotalValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = @SalesPersonName
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
HAVING (T3.OnHand * T3.AvgPrice) >= 100
Regards,
Johan
Hi Johan,
1. Yes - this is the exact query I am running
2. Yes found one
3. I have found an example, there is an order which has come onto SAP today - but hasn't been put onto picklist. All of the product on this order is in stock and the total is > 300.
a Pick status is N
b. Yes, this is a fresh order with no closed lines
c. Yes
d. Yes
4. This is returning no results whatsoever.
5. This doesn't return any results either.
Regards
Rick
Hi Rick,
Time for a triple check. Please don't be offended, I certainly don't mean to imply that you are lying, but if the query is not throwing an error, then the only logical explanation is that the requirements you just checked (or that I understood you asked for) are not equal to the requirements as stated in the query. If we can determine where the two diverge, we can fix the query.
Regards,
Johan
Hi Johan,
Thank you for all your efforts to get this working for me.
This is a screenshot from the sales order:
and here is a screenshot from the query list:
Since we last communicated - the example order I was looking at has now gone onto picklist.
As such I have another example - I put this onto SAP just the other day, and I am certain that all of te product on the order is in stock.
With regards to your last comment - I am wanting the query to look at the total stock value of the complete order. So for example... there could be 10 lines open on the order. Each line is worth $20. But only 6 lines are in stock (and available to ship - ie: not assigned to a picklist). But since 6x20 is $120 - then this result should show up in the query.
Hope this helps.
Thanks
Rick
Hi Rick,
That last one is the answer I think. My query, as is, looks for a single item's stock to be worth more than 100. That is why you were getting an empty result set, none of the order's item's stock alone was worth more than 100.
Please replace the following bits in both queries:
, T3.OnHand * T3.AvgPrice AS StockTotalValue
becomes
, SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue
and
HAVING (T3.OnHand * T3.AvgPrice) >= 100
becomes
HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100
Regards,
Johan
Hi Johan,
I'm sorry to say - but this is returning no results either.
Just checking this is what I am running:
/* SELECT * FROM OSLP sp */
DECLARE @SalesPersonName AS NVARCHAR(32)
SET @SalesPersonName = /* sp.SlpName */ '[%0]'
SELECT X.CardCode
,X.CardName
,SUM(X.[$ Value]) as '$ Value'
FROM
(SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = @SalesPersonName
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100) X
GROUP BY X.CardCode
,X.CardName
Regards
Rick
Hi Rick,
I see I missed something. Please remove the following highlighted bits:
...
SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, T3.OnHand
, SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue
...
and
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice
Regards,
Johan
Hi Rick,
Hmm, could you please test something? Comment out this bit:
HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)
by enclosing it in /* */, so you get:
/* HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0) */
If you still get nothing, please comment out:
T1.[PickStatus] = 'N'
If you then still get nothing, please comment out:
T1.[OpenQty] > '0'
Remember to also comment out the ANDs as needed.
Regards,
Johan
Hi Johan,
OK - so this is what I am running with currently:
/* SELECT * FROM OSLP sp */
DECLARE @SalesPersonName AS NVARCHAR(32)
SET @SalesPersonName = /* sp.SlpName */ '[%0]'
SELECT X.CardCode
,X.CardName
,SUM(X.[$ Value]) as '$ Value'
FROM
(SELECT T0.[DocNum]
, T0.[DocDate]
, T0.[CardCode]
, T0.[CardName]
, SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue
, 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
INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode
WHERE T1.[PickStatus] = 'N'
AND T1.[OpenQty] > '0'
AND T2.[SlpName] = @SalesPersonName
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName]
/* HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0) */) X
GROUP BY X.CardCode
,X.CardName
This returns results for me
Thanks
Rick
Hi Rick,
If we analyze the part of the query that generates this number:
SUM( <-- the sum statement adds up all values
ISNULL( <-- isnull makes sure that we get zero if there is no value at all
T3.OnHand <-- through the inner join of OITW (item data in warehouse), this value tells us the stock of the given item in the given warehouse
,0)
*
ISNULL(
T3.AvgPrice <-- through the inner join of OITW (item data in warehouse), this value tells us the cost price of one unit of the given item in the given warehouse
,0)
)
In other words, even though it takes items into account which aren't in stock, that stock is zero. So for example we get:
Item in Order nr. 123456 | Stock in warehouse | Cost price in warehouse | Total stock value |
---|---|---|---|
THURLOW_GIZMO_01 | 10 | 9.00 | 90.00 |
THURLOW_GIZMO_02 | 150 | 0.85 | 120.00 |
THURLOW_GIZMO_03 | 0 | 5.25 | 0.00 |
THURLOW_GIZMO_04 | 4 | 16.75 | 67.00 |
Total | 277.00 |
Because THURLOW_GIZMO_03 is not in stock, its stock value in this equation is zero, and therefore it is not responsible for this order to show up in the result set. The same goes for THURLOW_GIZMO_01 and THURLOW_GIZMO_04. In this case the stock value of THURLOW_GIZMO_02 is larger than 100, making the order show up in the result set.
So the question is, looking at it like this, is this the result you would expect, or am I still getting the question wrong ?
Regards,
Johan
Hi Johan,
I don't think the above is accurate.
Here is an example:
Item in Order nr. 123456 | Stock in warehouse | Cost price in warehouse | Total stock value |
---|---|---|---|
THURLOW_GIZMO_01 | 10 | 9.00 | 90.00 |
THURLOW_GIZMO_02 | 10 | 1.00 | 10.00 |
THURLOW_GIZMO_03 | 0 | 5.00 | 0.00 |
THURLOW_GIZMO_04 | 0 | 15.00 | 0.00 |
Total | 100.00 |
Here is another example:
Item in Order nr. 654321 | Stock in warehouse | Cost price in warehouse | Total stock value |
---|---|---|---|
THURLOW_GIZMO_01 | 20 | 9.00 | 180.00 |
THURLOW_GIZMO_02 | 0 | 1.00 | 0.00 |
THURLOW_GIZMO_03 | 0 | 5.00 | 0.00 |
THURLOW_GIZMO_04 | 0 | 15.00 | 0.00 |
Total | 180.00 |
Both of these orders should show in the report.
So - in the end, it is not a requirement for a line item to be over $100, but collectively over the sales order if it is over $100 it should show.
Thanks
Rick
Hi Rick,
You are right, I did not describe my logic correctly, but my intention was what you describe in your examples, and all three should show up in the list.
Incorporating your last example into the "technical" explanation, effectively we do this::
SUM(
(20 * 9.00) = 180.00 <-- THURLOW_GIZMO_01
+
(0 * 1.00) = 0.00 <-- THURLOW_GIZMO_02
+
(0 * 5.00) = 0.00 <-- THURLOW_GIZMO_03
+
(0 * 15.00) = 0.00 <-- THURLOW_GIZMO_04
) = 180.00 <-- total warehouse value of the entire stock in the warehouse that was determined in the order row for the respective item, for the items in the order.
That is what we do with the HAVING statement that I asked you to comment out earlier:
HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100
Same calculation once more, but this time with the requirement: >= 100
So according to this logic all three examples should show up. If some case does not show up, then it either does not conform to this logic, or the logic does not conform to your expectations.
Looking at the above, could you please check a live example, and see which it is ?
Regards,
Johan
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.