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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
| SELECT ETEMPS.NoEnregistrement, ETEMPS.DateTravail,
case when datepart(MONTH,ETEMPS.DateTravail)='01' and 'Semaine_modifie'='53' then datepart(year,ETEMPS.DateTravail)-1 else datepart(year,ETEMPS.DateTravail) end as année,
datepart(weekday,ETEMPS.DateTravail) Jours_C, datename(weekday,ETEMPS.DateTravail) Jours_N, datepart(iso_week,ETEMPS.DateTravail) Semaine,
case when datepart(weekday,ETEMPS.DateTravail)='7' and (((ETEMPS.HeureDebutExecuto*60)+ETEMPS.MinuteDebutExecuto)>1245 OR ((ETEMPS.HeureDebutCalage*60)+ETEMPS.MinuteDebutCalage)>1245) AND (datepart(iso_week,ETEMPS.DateTravail)='52' OR datepart(iso_week,ETEMPS.DateTravail)='53') then '1'
when datepart(weekday,ETEMPS.DateTravail)='7' and (((ETEMPS.HeureDebutExecuto*60)+ETEMPS.MinuteDebutExecuto)>1245 OR ((ETEMPS.HeureDebutCalage*60)+ETEMPS.MinuteDebutCalage)>1245) AND (datepart(iso_week,ETEMPS.DateTravail)<>'52' OR datepart(iso_week,ETEMPS.DateTravail)<>'53') then datepart(iso_week,ETEMPS.DateTravail)+1 Else datepart(iso_week,ETEMPS.DateTravail) END as Semaine_modifie
, case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END as CodeArticle_modifié, case
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,1)='N' then '3'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='XS' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='XF' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='XM' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,3)='XL5' then '5'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,4)='LNEI' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,3)='XLZ' then '12'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,3)='FPZ' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,1)='E' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,1)='S' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,1)='F' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,1)='M' then '1'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='3' then '3'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='4' then '4'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='Y' then '32'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='N' then '16'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='Z' then '12'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='X' then '18'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='M' then '9'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='W' then '48'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='V' then '24'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='S' then '20'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='T' then '40'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='L' then '21'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='R' then '46'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='Q' then '30'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='5' then '5'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='6' then '6'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='8' then '8'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='9' then '9'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='K' then '10'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='P' then '22'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),10,1)='J' then '52'
Else '99999' END as Multiple,
CASE when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='SP' then 'Fab Pochons'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,1)='X' then 'Croquettes'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='LP' then 'Emb Pochons'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='FP' then 'Emb Pochons'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='MP' then 'Emb Pochons'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='F0' then 'Etiquettage'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='F1' then 'Etiquettage'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='L0' then 'Etiquettage'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='L1' then 'Etiquettage'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='M0' then 'Etiquettage'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='M1' then 'Etiquettage'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='S0' then 'Fab Barquettes'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='S1' then 'Fab Barquettes'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='SB' then 'Fab Barquettes'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='SR' then 'Fab Barquettes'
when SUBSTRING((case when SUBSTRING(ARTICLE.CodeArticle,1,1)='Z' then SUBSTRING(ARTICLE.CodeArticle,2,12) Else ARTICLE.CodeArticle END),1,2)='SG' then 'Fab Barquettes'
ELSE '99999' END as Secteur, ETEMPS.TypeLctimprod AS 'Type', ETEMPS.CodeLanctimprod AS 'Code_Lct', ARTICLE.CodeArticle, ARTICLE.NbrePiecesColis, ETEMPS.CodePoste AS 'Poste', ETEMPS.CodeOperateur AS 'Ligne', OPERGAM.Designation1 AS 'Désignation_Opération', LCTC.CodeOperation AS 'Code_Op', OPERGAM.VarNumUtil4 AS 'Cadence_SILOG', ETEMPS.DureeExecution, ETEMPS.DureeReglage, ETEMPS.DureeCalage, ETEMPS.HeureDebutExecuto, ETEMPS.MinuteDebutExecuto, ETEMPS.HeureFinExecuto, ETEMPS.MinuteFinExecuto, ETEMPS.HeureDebutReglage, ETEMPS.MinuteDebutReglage, ETEMPS.HeureFinReglage, ETEMPS.MinuteFinReglage, ETEMPS.HeureDebutCalage, ETEMPS.MinuteDebutCalage, ETEMPS.HeureFinCalage, ETEMPS.MinuteFinCalage, ETEMPS.HeuresExecuto, ETEMPS.MinutesExecuto, ETEMPS.HeuresReglage, ETEMPS.MinutesReglage, ETEMPS.HeuresCalage, ETEMPS.MinutesCalage, ETEMPS.VarAlphaUtil AS 'Equipe', ETEMPS.VarNumUtil, ETEMPS.VarNumUtil2, ETEMPS.VarNumUtil3, ETEMPS.VarNumUtil4, ETEMPS.VarNumUtil8, ETEMPS.VarNumUtil9, ETEMPS.Remarque1, ETEMPS.Remarque2
FROM LCTE
inner join LCTC on LCTE.CodeLancement = LCTC.CodeLancement
inner join OPERGAM on LCTC.CodeOperation = OPERGAM.CodeOperation
inner join ETEMPS on ETEMPS.CodeLanctimprod = LCTE.CodeLancement
inner join ARTICLE on LCTE.CodeArticle = ARTICLE.CodeArticle
where ETEMPS.DateTravail like '%2016%'
order by ETEMPS.DateTravail |
Partager