slow item movement code

am using ax 2009 i write this code to retrive slow item movment using dialog to enter from to date (period)

but its not working

the Code :

InventTable inventtable ;

InventTrans inventtrans ;

Date datefrom , dateto ;

/////////////////////////////// Dialog code ////////////////////////////

dialog dlg;

dialoggroup dlgGroup;

dialogfield dlgFielddate1 ;

dialogfield dlgFielddate2 ;

dlg = new dialog(“Item Movment”);

dlgGroup = dlg.addGroup(“Item Movment”);

dlgFielddate2 = dlg.addField(TypeID(FromDate),‘From’);

dlgFielddate1 = dlg.addField(TypeID(ToDate),‘To’);

dlg.run();

if(dlg.closedOk() && dlgFielddate2.value()&& dlgFielddate1.value() )

{

datefrom =(dlgFielddate2.value() );

dateto = (dlgFielddate1.value());

super() ;

////////////////////////////////////////// code for retrieving item not moved during specific period //////////////////////////////

while select inventTable notExists join inventTrans where ( (inventtable.ItemId == inventtrans.ItemId ) && ( inventtrans.DatePhysical>= datefrom ) && (inventtrans.DatePhysical <= dateto ))

}

can any one help me

Which part of the code doesn’t work? The dialog? The query?

the query it is returning item after the to-date

All right, so if I remove all unrelated code, your problem is that the following query doesn’t return what you expect:

select inventTable
    notExists join inventTrans
    where inventTable.ItemId == inventtrans.ItemId
    && inventTrans.DatePhysical >= datefrom
    && inventTrans.DatePhysical <= dateto;

Can you give us an example when it returns unexpected data?

firstly thanks for your help,for example i have new item (01830) which is a added at 3/14/2014

when am inserting perid (from 12/1/2011 to 9/30/2013) it return that item ( 01830 ) !

What transaction do you have for item 01830? I obviously don’t know the content of your database.

Trans Type is purchased order but really i want to return item not exist in inventtrans table during the period i insert through the dialog

I don’t believe that the problem is in AX kernel or SQL Server - you probably have different InventTrans than you think. Run a query directly against transactions too see which ones are returned. You’re the only one who can do it, because we don’t have your database.