Untitled
unknown
plain_text
3 years ago
1.8 kB
3
Indexable
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
Editor is loading...