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
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