Dynamics Community Forum Thread Details

Need to run this query please help..

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

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

Quick Links

Leaderboard