You’re offline. This is a read only version of the page.
We are a new store that started up a while back, i need to run this query to find items that are currently not on order and have been sold since i opened and have less then 1 in stock. What happened was the vendor i have for my retail goods sent me only a few of this and a few of that for some items. I want to possibly re-order those items but since i never set reorder points until after we were running i cant seem to get a PO generated with the items i might have sold before. I've tried under reports--items--quantity.. but i seem to not be able to parse out those items that might currently be on order.
All items sold since XXX DATE
where supplier = FOO
and onhand =< 1
and currently on order is 0
*This post is locked for comments
I have the same question (0)Try this code. Just change the suppliercode and solddate variables to the values you need them. Regards, Tim
declare @SupplierCode nvarchar(50)
declare @SoldDate datetime
set @SupplierCode = 'Walmart'
set @SoldDate = '5/1/2010'
select i.* from item i
left join (select itemid,sum(quantityordered - quantityreceivedtodate) as onorder from purchaseorderentry where purchaseorderid in (select id from purchaseorder where potype = 0 and status in (0,1)) group by itemid) o on i.id = o.itemid
left join (select te.itemid from transactionentry te left join [transaction] t on te.transactionnumber = t.transactionnumber where t.time > @SoldDate group by te.itemid) s on i.id = s.itemid
where i.supplierid = (select id from supplier where code = @SupplierCode)
and i.quantity < 1
and isnull(o.onorder,0) = 0
and isnull(s.itemid,0) > 0
Share
Report
All responses (
Answers (
1,175
