DECLARE @Company nvarchar(4)
DECLARE @Partition bigint

SELECT @Partition  = Par.RECID FROM dbo.PARTITIONS AS Par WHERE Par.PARTITIONKEY = 'initial'
SET @Company='USMF'

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 = @Company and PARTITION = @Partition 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 = @Company and PARTITION = @Partition 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 = @Company and PARTITION = @Partition 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 = @Company and PARTITION = @Partition 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 = @Company and INVENTTABLE.PARTITION = @Partition
Order by inventtable.ITEMID