Below is the code. The beginning of 2016 on hand values are off. I'm also trying to get the amount purchased and sold in 2016. I broke it into positive and negative values like you mentioned. Thanks in advance
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'
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
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
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
group by ITEMID)EOHI
ON EOHI.ITEMID = INVENTTABLE.ITEMID
join ECORESPRODUCT
on inventtable.PRODUCT = ecoresproduct.RECID
join ECORESPRODUCTTRANSLATION
on ECORESPRODUCTTRANSLATION.PRODUCT = ecoresproduct.RECID
where ( BOHI.BegOnHand <> 0) or ( P.QtyPurchased <> 0) or( s.QTYSOLD <> 0) or (EOHI.EndOnHand <> 0 )
Order by inventtable.ITEMID
