Dynamics Community Forum Thread Details

I calculated total Sales, Production, Purchase... of specific items Results are accurate in SQL query.. I want to know how can i convert this into AOT query. i found ax does not support subqueries can anyone please help to calculate same in aot.

declare @startDate date;
declare @endDate date;
set @startDate = '2017-01-01';
set @endDate = '2017-01-31';

select distinct t.itemid,
Sales=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 0 and n.ITEMID =t.ITEMID and
s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate ),

Production=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 2 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate ),
Purch=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 3 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate),
InventTransaction=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 4 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate ),
InventTransfer=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 6 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate),
ProdLine=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 8 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate),
BOMLine=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 9 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate),
BOMMain=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 10 and n.ITEMID =t.ITEMID and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate),
InventCounting=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 13 and n.ITEMID =t.ITEMID and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate),
TransferOrderShip=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 21 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate ),
TransferOrderReceive=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 22 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate),
TransferOrderScrap=(select sum(s.QTY)
from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID
where n.REFERENCECATEGORY = 23 and n.ITEMID =t.ITEMID
and s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate)
from INVENTTRANS t join INVENTTRANSORIGIN o on t.INVENTTRANSORIGIN = o.RECID
where t.ITEMID >= '910091' and t.ITEMID <= '910098'
and t.DATEPHYSICAL >= @startDate and t.DATEPHYSICAL <= @endDate
order by 1

Hi!

It's might be look like this:

declare @startDate date;

declare @endDate date;

set @startDate = '2017-01-01';

set @endDate = '2017-01-31';

select distinct t.itemid,

Sales=(select sum(s.QTY)

from INVENTTRANS s join INVENTTRANSORIGIN n on s.INVENTTRANSORIGIN = n.RECID

where n.REFERENCECATEGORY = 0 and n.ITEMID =t.ITEMID and

s.DATEPHYSICAL >= @startDate and s.DATEPHYSICAL <= @endDate )

In AX:

TransDate startDate = 2017\01\01;

TransDate endDate = 2017\01\31;

InventTrans sales;

InventTransOrigin inventTransOrigin;

select sum(Qty) from sales

group by ItemId

where sales.DatePhysical >= startDate

&& sales.DatePhysical <= endDate

exists join inventTransOrigin

where inventTransOrigin.recId == sales.InventTransOrigin

&& inventTransOrigin.ReferenceCategory == InventTransType::Sales

&& inventTransOrigin.ItemId == sales.ItemId;

.......

Community site session details

Query to Calculate Sum(Qty) from inventTrans Table Based on Reference category in InventTransOrigin

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates