Untitled

set nocount on;
set transaction isolation level read uncommitted;

select
	  row_number() over (partition by ss.row_id order by ss.row_id)					as row_nr
	, ss.row_id
	, created_at_local
	, ii.item_id
	, ii.item_desc
	, ss.lot_no
	, ss.sublot_no																	as sublot_no_higher
	, s.sublot_no																	as sublot_no_lower
	, sum(qty_prod) over (partition by s.sublot_no)									as qty_prod
	, sum(cast(isnull(sa.attr_value, 0) as float)) over (partition by ss.sublot_no) as qty_prod_secondary
	, u.Description																	as uom_description
	, u2.Description																as uom_description_secondary
from MESDB..item_prod ip with (nolock)
join MESDB..sublot s
	on ip.sublot_no = s.sublot_no
	and ip.lot_no = s.lot_no
	and ip.item_id = s.item_id
left join MESDB..sublot ss
	on ss.sublot_no = s.parent
	and ss.item_id = s.item_id
	and ss.lot_no = s.lot_no
join MESDB..item ii
	on ii.item_id = ip.item_id
join MESDB..uom u
	on ii.uom_id = u.uom_id
left outer join MESDB..sublot_attr sa
	on s.sublot_no = sa.sublot_no
	and s.item_id = sa.item_id
	and sa.lot_no = s.lot_no
	and sa.attr_id = 45 --secondary_qty,
left outer join MESDB..sublot_attr sa2
	on s.sublot_no = sa2.sublot_no
	and s.item_id = sa2.item_id
	and sa2.lot_no = s.lot_no
	and sa2.attr_id = 46 --secondary_qty,
left outer join MESDB..uom u2
	on sa2.attr_value = u2.uom_id
where ip.wo_id = @wo_id
and ip.seq_no = @seq_no
and ip.oper_id = 'Palletizing'
and ip.lot_no is not null
and ip.sublot_no is not null
and ip.sublot_no not like 'deleted'
group by ss.row_id
	   , ii.item_id
	   , ii.item_desc
	   , ss.lot_no
	   , ss.sublot_no
	   , u.Description
	   , created_at_local
	   , qty_prod
	   , sa.attr_value
	   , ss.sublot_no
	   , s.sublot_no
	   , u2.Description