Tuesday, May 18, 2010

How to See the Dynamics GP Items You Didn't Sell

Ever have someone that wants to see GP Items that didn't sell lately?
Here is the SQL statement:


select itemnmbr, itemdesc, Item_Sales_Aggregates.Last_Sold, Item_Sales_Aggregates.Line_Qty from iv00101 left outer join

(

select max(document_date) as Last_Sold,count(document_date) as Line_Qty, item_number from

(select sop10100.docdate as document_date, sop10200.itemnmbr as Item_Number, sop10100.soptype as sop_Type
from sop10100 join sop10200
on sop10100.sopnumbe=sop10200.sopnumbe and sop10100.soptype=sop10200.soptype

union

select sop30200.docdate as document_date, sop30300.itemnmbr as item_number, sop30200.soptype as sop_Type
from sop30200 join sop30300
on sop30200.sopnumbe=sop30300.sopnumbe and sop30200.soptype=sop30300.soptype) as B

where sop_type=3 and document_date between '1/1/2009' and '5/30/2010'
group by item_number

) as Item_Sales_Aggregates

on iv00101.itemnmbr = Item_Sales_Aggregates.item_number

where line_qty is null