Skip to Content
avatar image
Former Member

Query for batch or serial number remaining stock based on specific date

Based on the date condition, I wish to find the latest remaining stock for that date. Result will show based on batch and serial number.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Mar 23, 2017 at 07:00 AM
    select t1.ItemCode, sum(t1.Quantity)as 'Remaining Stock',t0.managedby,
    Case when t0.managedby = 10000045 then CONCAT ('SN:',t4.distnumber)
    else CONCAT('BN:',t2.distnumber)
    end 'b/n or s/n'
    from oitl t0 inner join ITL1 T1 on T1.logentry=t0.logentry
    left join obtn T2 on t2.itemcode=t1.itemcode and t2.absentry=t1.mdabsentry and t2.objtype=t0.managedby
    left join OSRN t4 on t4.itemcode=t1.itemcode and t4.absentry=t1.mdabsentry and t4.objtype=t0.managedby
    where t0.createdate<='2016-08-12' 
    group by  t1.ItemCode,t0.ManagedBy,t2.DistNumber,t4.DistNumber
    order by t1.itemcode
    Add comment
    10|10000 characters needed characters exceeded