Select Latest On-hand In AX 2012 Using SQL Query

Like post in Select On-hand Per Date In AX 2012 Using SQL Query, i will share how to get latest on-hand in AX 2012 using SQL query. And this is the query :

use [AXDEV];
declare @DataAreaID varchar(10) = 'wip'
declare @ItemId varchar(20) = 'AR01010001'
declare @Size varchar(20) = '200'
Select a.ITEMID, b.PRODUCTNAME, c.UNITID, d.ITEMGROUPID, e.INVENTSIZEID, e.INVENTCOLORID,
ISNULL(e.AVAILPHYSICAL,0) as AVAILPHYSICAL,
ISNULL(e.RESERVPHYSICAL,0) as RESERVPHYSICAL,
ISNULL(e.AVAILPHYSICAL,0) - ISNULL(e.RESERVPHYSICAL,0) as TOTALAVAILABLE
from INVENTTABLE as a
left join ECORESPRODUCTTRANSLATIONS b on a.PRODUCT = b.PRODUCT
left join INVENTTABLEMODULE c on a.ITEMID = c.ITEMID and c.MODULETYPE = 0
left join INVENTITEMGROUPITEM d on a.ITEMID = d.ITEMID and a.DATAAREAID = d.ITEMDATAAREAID
inner join
(
select iSum.ITEMID, iDim.INVENTSIZEID, iDim.INVENTCOLORID,
sum(iSum.AVAILPHYSICAL) as AVAILPHYSICAL,
sum(iSum.RESERVPHYSICAL) as RESERVPHYSICAL
from INVENTSUM as iSum
inner join INVENTDIM as iDim on iDim.INVENTDIMID = iSum.INVENTDIMID
where iSum.DATAAREAID = @DataAreaID and iDim.DATAAREAID = @DataAreaID
group by iSum.ITEMID, iDim.INVENTSIZEID, iDim.INVENTCOLORID
) as e on e.ITEMID = a.ITEMID
where a.DATAAREAID = @DataAreaID and c.DATAAREAID = @DataAreaID
and e.AVAILPHYSICAL <> 0
/*and a.ITEMID = @ItemId and e.INVENTSIZEID = @Size*/
order by a.ITEMID, e.INVENTSIZEID, e.INVENTCOLORID

If you execute query above in Microsoft SQL Server Manajement Studio (MSSMS), you will see result like a picture below.

Result of query running