select INVENTTABLE.itemid ,
                               ECORESPRODUCTTRANSLATION.NAME,
                               CAST( isnull( BOHI.BegOnHand,0) AS DECIMAL(18,2))BegOnHand ,
                               CAST( isnull(P.QtyPurchased,0)AS DECIMAL(18,2)) QtyPurchased ,
                               CAST( isnull(s.QTYSOLD,0)AS DECIMAL(18,2)) QTYSOLD ,
                               CAST( isnull( EOHI.EndOnHand,0)AS DECIMAL(18,2)) EndOnHand
from INVENTTABLE
               --Beginning  on hand in 2016
               left outer join (
                               select itemid,  isnull(SUM(qty),0)BegOnHand
                               from INVENTTRANS
                               where  DATEPHYSICAL < '2016-1-1' and DATAAREAID = 'USMF' and PARTITION = '5637144576' AND STATUSISSUE < 3 and STATUSRECEIPT < 3
                               group by ITEMID )BOHI
                                               on bohi.ITEMID = INVENTTABLE.ITEMID
               --SOLD
               left outer join (
                               select ITEMID,  isnull(SUM(QTY),0)*-1 QTYSOLD
                               from INVENTTRANS
                               where DATEPHYSICAL >= '2016-1-1' AND  DATEPHYSICAL <  '2017-1-1' and QTY  < 0 and DATAAREAID = 'USMF' and PARTITION = '5637144576' AND STATUSISSUE < 3 and STATUSRECEIPT < 3
                               GROUP BY ITEMID )S
                                               on s.ITEMID = INVENTTABLE.ITEMID
               --PURCHASED
               left outer join (
                               select ITEMID,  isnull(SUM(QTY),0) QtyPurchased
                               from INVENTTRANS
                               where  DATEPHYSICAL >= '2016-1-1' AND  DATEPHYSICAL <  '2017-1-1' and QTY  > 0 and DATAAREAID = 'USMF' and PARTITION = '5637144576' AND STATUSISSUE < 3 and STATUSRECEIPT < 3
                               GROUP BY ITEMID )P
                                               ON P.ITEMID = INVENTTABLE.ITEMID
--Ending on hand in 2016
               left outer join (
                               select itemid, ISNULL( SUM(qty),0)EndOnHand
                               from INVENTTRANS
                               where  DATEPHYSICAL < '2017-1-1' and STATUSISSUE <> 7 and DATAAREAID = 'USMF' and PARTITION = '5637144576' AND STATUSISSUE < 3 and STATUSRECEIPT < 3
                               group by ITEMID)EOHI
                                                               ON EOHI.ITEMID = INVENTTABLE.ITEMID
               join ECORESPRODUCT
                               on inventtable.PRODUCT = ecoresproduct.RECID and inventtable.PARTITION = ecoresproduct.PARTITION
               join ECORESPRODUCTTRANSLATION
                               on ECORESPRODUCTTRANSLATION.PRODUCT = ecoresproduct.RECID and ECORESPRODUCTTRANSLATION.PARTITION = ecoresproduct.PARTITION
where ( BOHI.BegOnHand <> 0) or ( P.QtyPurchased <> 0) or( s.QTYSOLD <> 0) or (EOHI.EndOnHand <> 0 ) and DATAAREAID = 'USMF' and INVENTTABLE.PARTITION = '5637144576'
Order by inventtable.ITEMID