Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello,
I need some help for one request.
I want to fill the field durée_travail with the sum of the value of the field temps of a another table (origine1) which has several lines with the same id but with different statut.
Table origine 1
N_mantis (id),statut,temps
11,open,2
11,work in progress,4
11, closed,6
17,open,5
17,pending customer,8
17, closed,6
Table origine 2
N_mantis (id),temps
11, 6
17,5
The field temps is the sum of only the statuts open and work in progress of the lines which have the same id (N_mantis) of the Table origine 1
Origine1
Load
N_mantis
statut,
temps
FROM
(
origine2
LOAD
N_mantis ,
categorie as Cat,
compteur as comp,
version as ver,
severite as sev,
date_changement as date1 ,
Prioritaire as prio,
[Etat en cours] as etet,
Date_fin as date2,
Statut as stat,
durée_travail
RESIDENT origine
where N_mantis <> peek('N_mantis');
I've found it ! yes
Statuts:
LOAD *, (Tempsfin + tempsdebut + jourtravail) as durée_travail ;
LOAD recno() as "Code",
id,
date_changement as Date_début ,
//calcul des heures travaillées sur le jour début
if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,
if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,
// mise en forme de la date début selon les périodes
date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnéeDate_début,
date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJourDate_début,
dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnéeDate_début,
Year (date_changement) as AnnéeDate_début,
Date_fin,
// mise en forme de la date début selon les périodes
date(monthstart(Date_fin), 'MMM-YYYY') AS MoisAnnéedatefin,
date(Daystart(Date_fin), 'DD-MMM-YYYY') as AnnéeMoisJourdatefin,
dual(week(Date_fin)&'-'&WeekYear(Date_fin), WeekStart(Date_fin)) as SemaineAnnéedatefin,
Year (Date_fin) as AnnéeDate_fin,
//calcul des heures travaillées sur le jour fin
if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,
if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,
//calcul des jours travaillés entre la date début et la date fin
NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,
Statut
FROM
(
Mantis:
LOAD
distinct id,
categorie as Catégorie,
severite as sévérité,
version,
// modification des libellés du champ prioritaire
if(Prioritaire='Basse', dual('1-Basse', 1),
if(Prioritaire ='Normale', dual('2-Normale', 2),
if(Prioritaire ='Elevée', dual('3-Elevée', 3)
)
)
) as Prioritaire,
[Etat en cours]
FROM
(
Evolution:
join (Mantis) LOAD id,
1 as Compteur_Evolution,
charge,
date_livraison,
description
FROM
(
LEFT JOIN (Mantis) LOAD
id,
sum(durée_travail) as durée_travail2
resident Statuts
where Statut = 'Affecté' or Statut = 'Nouveau' group by id;
I believe you can do this with a group by load of table 1 and a join:
First load your tables then
LEFT JOIN (origine2) LOAD
N_mantis,
sum(temps) as durée_travail
resident origine1
where statut = 'open' or statut = 'work in progress' group by N_mantis;
sum
(Tempsfin) + sum(tempsdebut) + sum(jourtravail) as durée_travail
where Statut = 'Affecté' or Statut = 'Nouveau' group by N_mantis;
I've found it ! yes
Statuts:
LOAD *, (Tempsfin + tempsdebut + jourtravail) as durée_travail ;
LOAD recno() as "Code",
id,
date_changement as Date_début ,
//calcul des heures travaillées sur le jour début
if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,
if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,
// mise en forme de la date début selon les périodes
date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnéeDate_début,
date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJourDate_début,
dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnéeDate_début,
Year (date_changement) as AnnéeDate_début,
Date_fin,
// mise en forme de la date début selon les périodes
date(monthstart(Date_fin), 'MMM-YYYY') AS MoisAnnéedatefin,
date(Daystart(Date_fin), 'DD-MMM-YYYY') as AnnéeMoisJourdatefin,
dual(week(Date_fin)&'-'&WeekYear(Date_fin), WeekStart(Date_fin)) as SemaineAnnéedatefin,
Year (Date_fin) as AnnéeDate_fin,
//calcul des heures travaillées sur le jour fin
if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,
if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,
//calcul des jours travaillés entre la date début et la date fin
NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,
Statut
FROM
(
Mantis:
LOAD
distinct id,
categorie as Catégorie,
severite as sévérité,
version,
// modification des libellés du champ prioritaire
if(Prioritaire='Basse', dual('1-Basse', 1),
if(Prioritaire ='Normale', dual('2-Normale', 2),
if(Prioritaire ='Elevée', dual('3-Elevée', 3)
)
)
) as Prioritaire,
[Etat en cours]
FROM
(
Evolution:
join (Mantis) LOAD id,
1 as Compteur_Evolution,
charge,
date_livraison,
description
FROM
(
LEFT JOIN (Mantis) LOAD
id,
sum(durée_travail) as durée_travail2
resident Statuts
where Statut = 'Affecté' or Statut = 'Nouveau' group by id;