Skip to Content

Obsolete Items

Hi All,

i have the ff. requirements

need to know non moving items or Obsolete Items (considering all In and Outs of the Items)in a flexible given date range and the Onhand as of the end of date range example, selection date range is Jan 1, 2007 - Jan 1, 2013 items with no transaction within this date range will be displayed together with its OnHand as of Jan 1, 2013.

as of now this is all i have created -

this my actual command created in Crystal Reports

select 
T0.ItemCode , 
T0.ItemName, 
T0.InvntryUom, 
(SELECT count(ItemCode)
  FROM OINM T2 
  WHERE (T2.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T2.ItemCode= T0.ItemCode) as 'OINM_Count'
from OITM T0
where (SELECT count(ItemCode)
  FROM OINM T1 
  WHERE (T1.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T1.ItemCode= T0.ItemCode)=0
order by OINM_Count asc

in the above query i cannot get the onhand as of the enddate.

please help.

Fidel

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Dec 02, 2013 at 07:40 AM

    Hi Avelino,

    This query retrieves the real InStockQty at the end of your date range. The query before is retriving the quantity from the OITM.

    select T0.ItemCode , T0.ItemName, T0.InvntryUom, (SELECT count(ItemCode) FROM OINM T2 WHERE (T2.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T2.ItemCode= T0.ItemCode) as 'OINM_Count', (select SUM(T2.InQty - T2.OutQty) from oinm T2 Where T2.ItemCode = T0.Itemcode and T2.DocDate <= {?EndDate}) as InStockQty, {?EndDate} from OITM T0

    where (SELECT count(ItemCode) FROM OINM T1 WHERE (T1.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T1.ItemCode= T0.ItemCode)=0 12.order by OINM_Count asc

    Check this one

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Agusto Silva,

      thanks to your response.

      the 1st reply was feching the actual and current onhand, but the 2nd reply Nailed it! there were just a little syntax error by i manage to resolve it.... im posting my actual Crystal command here

      select    
      T0.ItemCode ,    
      T0.ItemName,    
      T0.InvntryUom,    
      (SELECT count(ItemCode)
        FROM OINM T2      
        WHERE (T2.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T2.ItemCode= T0.ItemCode) as 'OINM_Count',  
      (select SUM(T2.InQty - T2.OutQty) 
        from oinm T2 
        Where T2.ItemCode = T0.Itemcode and T2.DocDate <= {?EndDate}) as InStockQty  
      from OITM T0  
      
      
      
      
      where (SELECT count(ItemCode)     
        FROM OINM T1    
        WHERE (T1.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T1.ItemCode= T0.ItemCode)=0   
      order by OINM_Count asc
      

      thank you very much..

  • Posted on Dec 02, 2013 at 07:33 AM

    Hi Avelino,

    Try this query

    select T0.ItemCode , T0.ItemName, T0.InvntryUom, (SELECT count(ItemCode) FROM OINM T2

    WHERE (T2.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T2.ItemCode= T0.ItemCode) as 'OINM_Count',T0.OnHand, {?EndDate} from OITM T0

    where (SELECT count(ItemCode) FROM OINM T1 WHERE (T1.DocDate BETWEEN {?StartDate} AND {?EndDate}) and T1.ItemCode= T0.ItemCode)=0 order by OINM_Count asc

    Hope it helps

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.