Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AndroBast
Starting Member
6 Posts |
Posted - 2013-03-06 : 06:48:54
|
Dear Experts, I am trying to use this following query to track the balance quantity of Purchase Order (PO) after receipt and return. What I mean here is the shortage quantity of PO after receipt or / and return partially. What I mean is the remain PO quantity sum from the supplier. Here is the developed query but not success to solve my problem: select por1.LineNum+1"linenum", por1.ItemCode, s.baseentry, s.returnRunningTotalQty"Qty Return", isnull(por1.quantity,0)"Qty PO", (isnull(s.[remaining quantity after return],0))"running total GRPO", s.DocEntry"GRPO docentry", isnull(por1.Quantity,0)-ISNULL(s.[remaining quantity after return],0)"shortage"from por1 inner join( select pdn.DocEntry, pdn.BaseEntry, pdn.BaseLine, pdn.grporunningtotalqty, pdn.GRQty, rpd.ReturnRunningTotalQty, rpd.GRTQty, rpd.baseline"grtbaseline",CASE when isnull(rpd.GRTQty,0) = 0then isnull(pdn.grporunningtotalqty,0)when isnull(rpd.GRTQty,0) > 0then (isnull(pdn.grporunningtotalqty,0) - isnull(rpd.ReturnRunningTotalQty,0)) end as 'remaining quantity after return' from ( select distinct --a.Itemcode, a.docentry, a.Quantity"GRQty", a.LineNum, a.Baseref, a.baseline, a2.basetype, a2.baseentry, a2.u_sol_return, grporunningtotalqty = SUM(isnull(a2.quantity,0)) FROM pdn1 a INNER JOIN pdn1 a2 ON a.DocEntry >= a2.DocEntry --and a.BaseLine >= a2.baseline where a.BaseEntry = a2.BaseEntry and a.BaseLine = a2.BaseLine and a.LineNum = a2.LineNum --(a2.U_RETURN = 'reject' or a.U_RETURN is null) --and a.BaseLine = '0' GROUP BY a.Itemcode, a.LineNum, a.BaseLine, a.DocEntry, a.quantity, a2.BaseType, a.Baseref, a2.BaseEntry, a2.u_sol_return ) pdn inner join OPDN on pdn.docentry = opdn.docentry left join ( select distinct --a.Itemcode, a.Quantity"GRTQty", a.Baseref, a.baseline, a2.basetype, a2.baseentry, a2.u_sol_return, ReturnRunningTotalQty = SUM(isnull(a.Quantity,0)) FROM rpd1 a INNER JOIN rpd1 a2 ON a.DocEntry >= a2.DocEntry where a.BaseEntry >= a2.BaseEntry and a.itemcode = a2.itemcode and a.BaseLine >= a2.baseline and (a2.U_SOL_RETURN = 'reject') --and a.LineNum = a2.LineNum --and a.BaseEntry = '1953' --and a.quantity >= a2.quantity GROUP BY a.Itemcode, a.BaseLine, a.quantity, a2.BaseType, a.Baseref, a2.BaseEntry, a2.u_sol_return --order by a.BaseEntry--) rpd1 ) RPD onrpd.BaseEntry = pdn.DocEntry and rpd.BaseRef = opdn.DocNum and rpd.BaseLine = pdn.LineNum and rpd.BaseType = opdn.ObjType ) s on s.BaseEntry = por1.DocEntry and s.BaseLine = por1.LineNum --and s.grtbaseline >= por1.LineNum where s.BaseEntry = '1000' order by por1.LineNum asc The detail can be seen in this link:http://scn.sap.com/thread/3319700there is a screen shot of the result in the link. The thread in the forum created by my work mate.Could you please help me to solve the issue because my query can't give the expected result ? Thank youAndro |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-06 : 07:04:05
|
How come if you provide sample data in the form of Insert statements and the desired output based on that sample data? e.g. CheersMIK |
|
|
AndroBast
Starting Member
6 Posts |
Posted - 2013-03-06 : 19:08:47
|
quote: Originally posted by MIK_2008 How come if you provide sample data in the form of Insert statements and the desired output based on that sample data? e.g. CheersMIK
Thanks for your reply.Do you mean I have to use insert statement rather than subquery ? Could you please give an example ? I do not still understand. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 23:27:38
|
quote: Originally posted by AndroBast
quote: Originally posted by MIK_2008 How come if you provide sample data in the form of Insert statements and the desired output based on that sample data? e.g. CheersMIK
Thanks for your reply.Do you mean I have to use insert statement rather than subquery ? Could you please give an example ? I do not still understand.
The suggestion was to post some sample data and explain your requirementThat will be easier for others to understand what issue is aboutsee below on guidelineshttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
AndroBast
Starting Member
6 Posts |
Posted - 2013-03-07 : 00:44:09
|
Dear All,Thanks for your replies.The sample of my questions are as follows:1. The user of the application created 1 purchase orderwith details as follows:The table of purchase orders are OPOR & POR1, the primary key to link the table is docentry.Data input by user are in the table as follows:A. POR1, (the field or column tables are: itemcode, linenum, quantity, docentry). OPOR is the header table only that recorded supplier properties and document total amount.1. linenum : 02. itemcode : CX10063. quantity : 1000 4. docentry : 1671 (autocreated by application)1. linenum : 12. Itemcode : CX10063. Quantity : 20004. docentry : 1671 1671 (autocreated by application)Then the item CX1006 received from supplier.The tables to record it are OPDN & PDN1.Data input by user are in the table as follows:A. PDN1, (the field or column tables are: itemcode, linenum, quantity, docentry, baseline, baseref, baseentry).OPDN is the header table only that recorded supplier properties and document total amount. Here are the data input in the PDN1:1. linenum : 02. Itemcode : CX10063. Quantity : 9004. docentry : 1984 (autocreated by application)5. baseentry : 1671 (autocreated by application)6. baseline : 0B. PDN11. linenum : 02. Itemcode : CX10063. Quantity : 1004. docentry : 1985 (autocreated by application)5. baseentry : 1671 (autocreated by application)6. baseline : 0C. PDN11. linenum : 02. Itemcode : CX10063. Quantity : 12004. docentry : 1986 (autocreated by application)5. baseentry : 1671 (autocreated by application)6. Baseline : 1C. PDN11. linenum : 02. Itemcode : CX10063. Quantity : 5004. docentry : 1987 (autocreated by application)5. baseentry : 1671 (autocreated by application)6. Baseline : 1The user return some quantity of item CX1006. The returned quantity is 200The tables are ORPD and RPD1. ORPD is the header table only that recorded supplier properties and document total amount.The data input in the RPD1 are as follows:1. linenum : 02. Itemcode : CX10063. Quantity : 2004. docentry : 1984 (autocreated by application)5. baseentry : 1671 (autocreated by application)6. baseline : 0Based on the above data, it summarrized as follows:1. CX1006 for both linenum 0 & 1 are received 4 times with quantity per each are:900, 100, 1200 and 500Total received are : 2700 but returned 200. Remaining quantity to received are 5002. using the query, the running shortage or cumulative shortage in the last row is only 300. The query I made can result in 500. Based on the above sample, I really expected that the problem I submit here could be solved. Regards,Andro |
|
|
AndroBast
Starting Member
6 Posts |
Posted - 2013-03-08 : 02:35:27
|
Dear All,here is the sample of my question:item PO Qty Received Return ShortageCX1006 1000 900 0 100CX1006 1000 100 0 0CX1006 2000 1200 200 1000CX1006 2000 500 0 500CX1006 3000 3000 0 0 Purchase order (PO) table : POR1 Received Qty table : PDN1 Return Qty table : RPD1 Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-08 : 05:41:38
|
so what should be your output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
AndroBast
Starting Member
6 Posts |
Posted - 2013-03-08 : 11:44:09
|
quote: Originally posted by visakh16 so what should be your output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
My sample is the expected output but using the query I have developed, I can't get the expected output.Here is the result of my query:No Item Line Expected Qty (Purchase Order) Received Qty (Received Order) Return Qty (return document) Shortage1 CX1006 1 1000 900 0 1002 CX1006 1 1000 100 0 03 CX1006 2 2000 1200 200 10004 CX1006 2 2000 500 0 3005 CX1006 3 3000 3000 0 0The result of the number four in the shortage column should be 500 but using my query I only get 300. I need your help, all guys here in this forum to solve the problem. Thank you |
|
|
AndroBast
Starting Member
6 Posts |
Posted - 2013-03-12 : 21:27:40
|
quote: Originally posted by AndroBastMy sample is the expected output but using the query I have developed, I can't get the expected output.Here is the result of my query:No Item Line Expected Qty (Purchase Order) Received Qty (Received Order) Return Qty (return document) Shortage1 CX1006 1 1000 900 0 1002 CX1006 1 1000 100 0 03 CX1006 2 2000 1200 200 10004 CX1006 2 2000 500 0 3005 CX1006 3 3000 3000 0 0The result of the number four in the shortage column should be 500 but using my query I only get 300. I need your help, all guys here in this forum to solve the problem. Thank you
Hi Experts,Just want to inform that my problem is similar with the problem in this link :http://stackoverflow.com/questions/6180874/sql-running-subtraction-and-deviation?rq=1but there is no good return effect in the shortage calculation. So, in my problem, there is also good return that will affect the shortage. I need a good return qty column. And the calculation of the shortage will PO qty-REceived qty+good return qty.I appreciate your help so much. TIAAndro |
|
|
|
|
|
|
|