1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| With MesStatCode as
(
select 10 as statCode,'Imprimé' status_libelle
union
select 15 as statCode,'En cours de prép.' status_libelle
union
select 20 as statCode,'Préparé' status_libelle
union
select 30 as statCode,'Pesé' status_libelle
),
PlateZones As
(
Select 'Plateforme' as plate_zone,
union
select 'Zone normale' as plate_zone
)
Select PZ.plate_zone,
SC.statCode as stat_code,
SC.status_libelle as statut_cartons,
nb_cartons,
nb_pieces_a_prelever,
nb_pieces_prelevees,
avancement
from MesStatCode SC
cross join PlateZones PZ
left outer join
(
select
case when aid.pikr_nbr = '99' then 'Plateforme' else 'Zone normale' end plate_zone ,
ch.stat_code,
count(distinct ch.carton_nbr) nb_cartons,
sum(cd.to_be_pakd_units) nb_pieces_a_prelever, sum(cd.units_pakd) nb_pieces_prelevees,
avg(cd.units_pakd*100/nullif (cd.to_be_pakd_units,0) avancement
from carton_hdr ch with (nolock)
inner join carton_dtl cd with (nolock)
on ch.carton_nbr = cd.carton_nbr
inner join alloc_invn_dtl aid with (nolock)
on aid.carton_nbr = cd.carton_nbr and aid.carton_seq_nbr = cd.carton_seq_nbr
where ch.stat_code in (10,15,20,30) -- imprimé / en cours de prépa / préparé / pesé
and right(ch.pkt_ctrl_nbr,1) in ('G','P','M','Z')
group by case when aid.pikr_nbr = '99' then 'Plateforme' else 'Zone normale' end, ch.stat_code
) as T
on SC.statCode=T.stat_code
and PZ.plate_zone=T.plate_zone |
Partager