Skip to Content

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

Mar 23, 2017 at 01:43 AM


avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

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
10 |10000 characters needed characters left characters exceeded