Dynamics Community Forum Thread Details

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

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