Код

Declare @QParam__filter_2_9_0 numeric(9,0), @OKParam_0 varchar(99)
set @QParam__filter_2_9_0 = -10000000
set @OKParam_0 = 'Деликатесная в/к    84   '

select top 20
	N.KeySort as КлючПорядка,
	N.id1s as ИД,
	N.PLU as PLU,
	N.Descr as Наименование,
	N.Unit as ЕдИзм,
	N.isUnit as Штучная,
	isNULL(CASE
				WHEN N.isUnit = 1 THEN  TBS.ОсталосьКоличество
				ELSE  TBS.ОсталосьВес
			END,0 ) as ОсталосьИтого
from Nomenclature as N (nolock)
left  join
(
	Select
		T.ИдНом,
		SUM(T.НабраноВес) as НабраноВес,
		SUM(T.НабраноКоличество) as НабраноКоличество,
		SUM(T.ЗаказаноВес) as ЗаказаноВес,
		SUM(T.ЗаказаноКоличество) as ЗаказаноКоличество,
		SUM(T.ЗаказаноКоличество) - SUM(T.НабраноКоличество) as ОсталосьКоличество,
		SUM(T.ЗаказаноВес) - SUM(T.НабраноВес) as ОсталосьВес
	From dbo.GetTrafficByStatusByRequest(101) as T
	group by T.ИдНом


) as TBS  on TBS.ИдНом = N.id1s
where (N.KeySort > @OKParam_0)
order by N.KeySort

 

Код

select top 20
	N.KeySort as КлючПорядка,
	N.id1s as ИД,
	N.PLU as PLU,
	N.Descr as Наименование,
	N.Unit as ЕдИзм,
	N.isUnit as Штучная,
	isNULL(CASE
				WHEN N.isUnit = 1 THEN  TBS.ОсталосьКоличество
				ELSE  TBS.ОсталосьВес
			END,0 ) as ОсталосьИтого
from Nomenclature as N (nolock )
left join
(
	Select
		T.ИдНом,
		SUM(T.НабраноВес) as НабраноВес,
		SUM(T.НабраноКоличество) as НабраноКоличество,
		SUM(T.ЗаказаноВес) as ЗаказаноВес,
		SUM(T.ЗаказаноКоличество) as ЗаказаноКоличество,
		SUM(T.ЗаказаноКоличество - T.НабраноКоличество) as ОсталосьКоличество,
		SUM(T.ЗаказаноВес - T.НабраноВес) as ОсталосьВес
	From dbo.GetTrafficByStatusByRequest(101) as T
	group by T.ИдНом
	having SUM(T.ЗаказаноВес - T.НабраноВес) >0
) as TBS  on TBS.ИдНом = N.id1s
where (N.KeySort > @OKParam_0)
--and  TBS.ОсталосьВес  > @QParam__filter_2_9_0
order by N.KeySort