-- Prepare sample data
DECLARE	@tbljobDoc TABLE (jobID varchar(12), DocRefID varchar(10), PickComplete  char(1) )

INSERT	@tbljobDoc
SELECT	'A', 'docA', 'Y' UNION ALL
SELECT	'A', 'docA2', 'N' 

DECLARE	@tblItemPicked TABLE (ItemStorageID varchar(12), DocRefID varchar(10), jobID varchar(12), PickQty float )

INSERT	@tblItemPicked
SELECT	'ISO1', 'docA', 'A', 10 UNION ALL
SELECT	'ISO1', 'docA2', 'A', 3 UNION ALL
SELECT	'ISO1', 'docA2', 'A', 4 UNION ALL
SELECT	'ISO2', 'docA', 'A', 10 UNION ALL
SELECT	'ISO2', 'docA2', 'A', 3 UNION ALL
SELECT	'ISO2', 'docA2', 'A', 4 

DECLARE	@tblItemDetail TABLE (ItemStorageID varchar(12), OriginQty float, PickQty float, own char(3), client varchar(10), itemid varchar(20) )

INSERT	@tblItemDetail
SELECT	'ISO1', 50, 17, 'OWN', 'jabpb', 'rainbow' UNION ALL
SELECT	'ISO2', 50, 17, 'VMI', 'jabpb', 'rainbow'

select * from @tbljobDoc
select * from @tblItemPicked
select * from @tblItemDetail

declare @AVA float, @book float, @pick float, @VMIAVA float, @VMIBook float
SELECT 			@AVA=
			ISNULL(SUM(d.OriginQty), 0) -
			ISNULL(SUM(d.PickQty), 0),
		 	@Book=sum(coalesce(p.pickqty,0)),
			@Pick=case when (sum(coalesce(p.pickqty,0))) > 0 Then
					ISNULL(SUM(d.PickQty), 0) - sum(coalesce(p.pickqty,0))
				   Else ISNULL(SUM(d.PickQty), 0) end,
			@VMIAVA =  SUM(CASE WHEN own = 'VMI' THEN d.OriginQty ELSE 0 END) -
					SUM(CASE WHEN own = 'VMI' THEN d.PickQty ELSE 0 END)--,
			--@VMIBook = SUM(CASE WHEN OwnerStatus = 'VMI' THEN SUM(COALESCE(p.PickQty,0)) ELSE 0 END)
			
FROM	@tblItemDetail d
LEFT JOIn @tblitempicked p ON p.itemstorageid=d.itemstorageid
left JOIn @tblJobDoc j ON j.Docrefid=p.Docrefid AND j.jobID=p.JobID AND j.Pickcomplete='N'

WHERE d.ItemID = 'rainbow'
    and d.client='jabpb'

select @AVA as ava, @book as book, @pick as pick, @VMIAVA as vmiAva, @VMIBook as vmiBook

--expected result
select 66 as ava, 14 as book, 20 as pick, 33 as vmiAVA, 7 As vmiBook, 10 as vmiPick