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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Cumulative shortage query

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) = 0
then isnull(pdn.grporunningtotalqty,0)
when isnull(rpd.GRTQty,0) > 0
then (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 on

rpd.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/3319700

there 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 you

Andro

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.


Cheers
MIK
Go to Top of Page

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.


Cheers
MIK



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.
Go to Top of Page

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.


Cheers
MIK



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 requirement
That will be easier for others to understand what issue is about

see below on guidelines

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 order
with 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 : 0
2. itemcode : CX1006
3. quantity : 1000
4. docentry : 1671 (autocreated by application)

1. linenum : 1
2. Itemcode : CX1006
3. Quantity : 2000
4. 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 : 0
2. Itemcode : CX1006
3. Quantity : 900
4. docentry : 1984 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. baseline : 0

B. PDN1
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 100
4. docentry : 1985 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. baseline : 0

C. PDN1
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 1200
4. docentry : 1986 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. Baseline : 1

C. PDN1
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 500
4. docentry : 1987 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. Baseline : 1


The user return some quantity of item CX1006. The returned quantity is 200
The 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 : 0
2. Itemcode : CX1006
3. Quantity : 200
4. docentry : 1984 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. baseline : 0


Based 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 500
Total received are : 2700 but returned 200. Remaining quantity to received are 500

2. 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
Go to Top of Page

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 Shortage
CX1006 1000 900 0 100
CX1006 1000 100 0 0
CX1006 2000 1200 200 1000
CX1006 2000 500 0 500
CX1006 3000 3000 0 0


Purchase order (PO) table : POR1
Received Qty table : PDN1
Return Qty table : RPD1


Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 05:41:38
so what should be your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 MVP
http://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) Shortage
1 CX1006 1 1000 900 0 100
2 CX1006 1 1000 100 0 0
3 CX1006 2 2000 1200 200 1000
4 CX1006 2 2000 500 0 300
5 CX1006 3 3000 3000 0 0

The 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
Go to Top of Page

AndroBast
Starting Member

6 Posts

Posted - 2013-03-12 : 21:27:40
quote:
Originally posted by AndroBast


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) Shortage
1 CX1006 1 1000 900 0 100
2 CX1006 1 1000 100 0 0
3 CX1006 2 2000 1200 200 1000
4 CX1006 2 2000 500 0 300
5 CX1006 3 3000 3000 0 0

The 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=1


but 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. TIA


Andro
Go to Top of Page
   

- Advertisement -