-- DECLARE @trip_id int = 113063;
-- DECLARE @trip_id int = 167358;
-- DECLARE @trip_id int = 113063;
-- DECLARE @trip_id int = 166082;
DECLARE @last_import_date datetime = (select max(date_create)
                                      from warehouse_expiring_material);

DECLARE @info_xml table(zakaz_id int, NAK_ID varchar(max), waybill_1c_id varchar(max));
INSERT INTO @info_xml

Select distinct isnull(o.ZAKAZ_ID, cs.zakaz_hat_id)    zakaz_id,
                isnull(o.NAK_ID, cs.waybill_number) as waybill_number,
                cs.waybill_1c_id
from (select o.ZAKAZ_ID, o.NAK_ID
      FROM tc_trip_zakaz tcz
             LEFT JOIN zakaz_order_id_arc o ON o.ZAKAZ_ID = Tcz.zakaz_hat_id
      where tcz.tc_trip_id = @trip_id) o FULL
       OUTER JOIN (select cs.zakaz_hat_id, cs.waybill_number, cs.waybill_1c_id
                   from tc_trip_zakaz tcz
                          LEFT JOIN co_shipping cs ON cs.zakaz_hat_id = tcz.zakaz_hat_id
                   WHERE tcz.tc_trip_id = @trip_id) cs ON cs.zakaz_hat_id = o.ZAKAZ_ID and cs.waybill_number = o.NAK_ID


If (OBJECT_ID('tempdb..#for_sort') IS NOT NULL)
  Drop Table #for_sort
select row_number() over (order by consignee_id, np) rn, -- для сортировки по накладным    смотри ниже
       p.*
    INTO #for_sort
from (select dense_rank() over (order by consignee_id) as np,
             p.*
      from (Select p.tc_trip_id,
                   CASE WHEN p.saleman_id in ('РФМОСКМЕТ', 'РФСПБЛЕНТ') THEN p.saleman_id else p.consignee_id end as consignee_id,  -- Грузополучателей Лента и Metro C&C группировать по zakaz_hat.saleman_id
                   cc.name                                                                                                                                 as consignee_name,
                   /*case when max(case when p.original_warehouse_id = 4 THEN 999 ELSE -1 END) over () = 999 THEN 4 ELSE*/ p.original_warehouse_id /*END*/ as warehouse_id, -- если в рейсе есть 4-ый Логистический склад, то все склады логистические  -- REDMINE #6046
                   p.p_brut,
                   p.p_net,
                   p.shipping_id,
                   korob,
                   p.material_name
            from (select o.ID,
                         tcz.tc_trip_id,
                         h.id                    as                                                     zakaz_id,
                         h.num,
                         h.adress_d,
                         pz.pallet_zakaz_id,
                         case
                           when tct.factory_id = 1 THEN CASE
                                                          WHEN zlw.contractor_id is not null
                                                                  then 4                    -- Логистический
                                                          WHEN cmap.warehouse_id in (14327)
                                                                  THEN 0                    -- ЖМС
                                                          WHEN cmap.warehouse_id in (14329)
                                                                  THEN 1                    -- СЧС
                                                          WHEN cmap.warehouse_id in (14326)
                                                                  THEN 2                    -- СМС
                                                          WHEN cmap.warehouse_id in (14328)
                                                                  THEN 3                    -- ТМ
                                                          WHEN cmap.warehouse_id in (19533)
                                                                  THEN 5                    -- Aromat
                                                          else lcw.warehouse_id
                             end
                           else 6
                             END                                                                        original_warehouse_id,
                         case when zlw.contractor_id is not null THEN -1 ELSE h.address_id END          city_order,
                         lm.kor * cmap.box_gross as                                                     p_brut,
                         lm.kor * cmap.box_net   as                                                     p_net,
                         lm.kor                  as                                                     korob,
                         cmap.shipping_id,
                         m.name                  as                                                     material_name,
                         m.id                    as                                                     material_id,
                         box.box_name,
                         lpp.pallet_id,
                         dbo.generateSSCC(isnull((select top 1 gln
                                                  from co_factory_gln
                                                  where id_factory = tct.factory_id
                                                  order by dt_st desc), '000000000'), lpp.pallet_id, 1) sscc,
                         lp.num                  as                                                     loading_pallet_num,
                         h.consignee_id,
                         o.NAK_ID                as                                                     ttn,
                         h.buyer_num,
                         h.saleman_id,
                         pogrt.descr,
                         ptt.name                as                                                     packing_name
                  from tc_trip_zakaz tcz
                         join tc_trip tct ON tct.id = tcz.tc_trip_id
                         join zakaz_hat h on h.id = tcz.zakaz_hat_id
                         join loading_palletz_zakaz_hat pz on pz.zakaz_id = h.id
                         join loading_palletz_pallet lpp on pz.pallet_zakaz_id = lpp.pallet_zakaz_id
                         join loading_pallet lp on lpp.pallet_id = lp.id
                         join loading_pallet_material lm on lm.pallet_id = lp.id
                         left join pogruz_type pogrt On pogrt.id = lp.pogruz_type_id
                         join co_material AS m ON m.id = lm.material_id
                         join co_material_attr_prod cmap on lm.material_id = cmap.material_id
                         join co_material_attr_details det
                           ON det.id = cmap.category and det.material_attr_id = 21 and det.factory_id IN (1, 2)
                         left join loading_category_warehouse lcw ON lcw.category_id = det.id
                         join loading_palletz_transport pt on pz.pallet_zakaz_id = pt.id
                         join dbo.co_contractor_attr_customer ca ON h.saleman_id = ca.distr_id
                         left join zakaz_logistic_warehouse zlw ON zlw.contractor_id = ca.contractor_id
                         join ver_zaivka_prod vzp
                           ON vzp.ver_id = h.ver_id and vzp.zaivka_id = h.zaivka_id and vzp.material_id = m.id
                         join prod_pogruz pp ON pp.ver_id = vzp.ver_id and pp.zaivka_id = vzp.zaivka_id and
                                                pp.material_id = vzp.material_id
                         join pogruz ON pogruz.pogruz_id = pp.pogruz_id
                         join box ON box.box_id = pogruz.box_id
                         left join packing_type ptt on ptt.id = pogruz.packing_id
                         left join zakaz_order_id_arc o ON o.ZAKAZ_ID = h.id and (case
                                                                                    when zlw.contractor_id is not null
                                                                                            THEN 18784
                                                                                    ELSE cmap.warehouse_id END) =
                                                                                 o.warehouse_id
                  where tcz.tc_trip_id = @trip_id) p
                   JOIN co_contractor_attr_customer ccac ON ccac.distr_id = p.saleman_id
                   JOIN co_contractor cc ON cc.id = ccac.contractor_id) p
             left join loading_points lp ON lp.id = p.warehouse_id) p


-- сортируем паллеты
-- сначала накладные
DECLARE @row_numbers int = (select count(*)
                            from #for_sort)
DECLARE @tbl table(original int, new int)

DECLARE @rn int, @np int, @new int, @i int = 0
declare params cursor for
  SELECT rn, np
  FROM #for_sort

open params;
fetch next from params
into @rn, @np;
while @@fetch_status = 0
  BEGIN
    set @new = (select new from @tbl where original = @np);
    IF @new > 0
      UPDATE #for_sort set np = @new where rn = @rn
    ELSE
      BEGIN
        set @i = @i + 1
        UPDATE #for_sort set np = @i where rn = @rn
        INSERT INTO @tbl VALUES (@np, @i)
      END
    fetch next from params
    into @rn, @np;
  END

close params;
deallocate params;

--select * from   #for_sort

Select min(rn)                                                                              rn,
       min(np)                                                                              np,
       consignee_id,
       consignee_name,
       shipping_id,
       material_name,
       sum(korob) as korob,
       sum(p_brut) as p_brut,
       sum(p_net) as p_net
from #for_sort fs
group by tc_trip_id, consignee_id, shipping_id, consignee_name, material_name

If (OBJECT_ID('tempdb..#for_sort') IS NOT NULL)
  Drop Table #for_sort
--select * from @tbl