Depuis quelques mois je travaille sur un logiciel QSE afin de gérer des informations relatives au domaine RH.
Le logiciel est créé en VB.NET et utilise beaucoup les ReportViewer pour afficher les données sous forme de graphique et tableaux. Dans un tableau par exemple, je veux afficher sur 5 ans (5 lignes) les arrêts de travail avec ou sans arrêts, les jours d'arrêts, etc.
Toutes sont données sont récupérés grâce à un DataSet qui contient une vingtaine de DataTable dans lesquelles il y a des requêtes.
Je vais prendre un exemple dans mon programme : j'ai un DataTable "DT_lieux_accidents" où j'effectue une requête pour récupérer les soins, les accidents avec arrêt, les accident sans arrêt et les jours d'arrêts (je simplifie au maximum).
La requête est sous la forme :
SELECT SUM(acc_avec_arret) as AccArret, (...) FROM ( SELECT ... une sous requête quoi. Cependant j'ai une problématique qui se pose avec les Jours d'arrêts (JA) qui peuvent être sur plusieurs années. Mon Where (celui qui englobe tout) doit être "divisé" pour agir que sur certaines colonnes.
Je dois faire un where pour les JA et un autre where pour le reste. Je tombe sur une problématique où j'essaye de cherche la solution la plus optimisée et la plus courte (au niveau caractère) car l'écriture d'une requête SQL dans un DT est limité.
Je sais pas si j'ai été très clair, si vous avez besoin d'autres infos ou peut-être des screens sur des trucs ciblés je peux vous en envoyer.
SELECT T1.AT_SIEGE, SUM(CNBJA_INTERIM_TRAVAIL) AS NBJA_INTERIM_TRAVAIL, SUM(CNBJA_INTERIM_TRAJET) AS NBJA_INTERIM_TRAJET,
SUM(CNBJA_SOCIETE_TRAVAIL) AS NBJA_SOCIETE_TRAVAIL, SUM(CNBJA_SOCIETE_TRAJET) AS NBJA_SOCIETE_TRAJET, SUM(CNBATSOIN_INTERIM_TRAVAIL)
AS NBATSOIN_INTERIM_TRAVAIL, SUM(CNBATSOIN_INTERIM_TRAJET) AS NBATSOIN_INTERIM_TRAJET, SUM(CNBATSOIN_SOCIETE_TRAVAIL)
AS NBATSOIN_SOCIETE_TRAVAIL, SUM(CNBATSOIN_SOCIETE_TRAJET) AS NBATSOIN_SOCIETE_TRAJET, SUM(CNBATSA_INTERIM_TRAVAIL)
AS NBATSA_INTERIM_TRAVAIL, SUM(CNBATSA_INTERIM_TRAJET) AS NBATSA_INTERIM_TRAJET, SUM(CNBATSA_SOCIETE_TRAVAIL)
AS NBATSA_SOCIETE_TRAVAIL, SUM(CNBATSA_SOCIETE_TRAJET) AS NBATSA_SOCIETE_TRAJET, SUM(CNBATAA_INTERIM_TRAVAIL)
AS NBATAA_INTERIM_TRAVAIL, SUM(CNBATAA_INTERIM_TRAJET) AS NBATAA_INTERIM_TRAJET, SUM(CNBATAA_SOCIETE_TRAVAIL)
AS NBATAA_SOCIETE_TRAVAIL, SUM(CNBATAA_SOCIETE_TRAJET) AS NBATAA_SOCIETE_TRAJET, SUM(CNBATDC_INTERIM_TRAVAIL)
AS NBATDC_INTERIM_TRAVAIL, SUM(CNBATDC_INTERIM_TRAJET) AS NBATDC_INTERIM_TRAJET, SUM(CNBATDC_SOCIETE_TRAVAIL)
AS NBATDC_SOCIETE_TRAVAIL, SUM(CNBATDC_SOCIETE_TRAJET) AS NBATDC_SOCIETE_TRAJET, SUM(CNBAT_INTERIM_TRAVAIL) AS NBAT_INTERIM_TRAVAIL,
SUM(CNBAT_INTERIM_TRAJET) AS NBAT_INTERIM_TRAJET, SUM(CNBAT_SOCIETE_TRAVAIL) AS NBAT_SOCIETE_TRAVAIL, SUM(CNBAT_SOCIETE_TRAJET) AS NBAT_SOCIETE_TRAJET
FROM (SELECT S.AT_SIEGE,
CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.AT_type_AT = 1)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_SOCIETE_TRAVAIL,
CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.AT_type_AT = 0)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_SOCIETE_TRAJET,
CASE WHEN (AT.id_AT_contrat = 30) AND (AT.AT_type_AT = 1)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
CASE WHEN (AT.id_AT_contrat = 30) AND (AT.AT_type_AT = 0)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_INTERIM_TRAJET
FROM T1liste_AT_siege AS S LEFT OUTER JOIN
Taccident_travail AS AT ON S.id_siege = AT.id_AT_siege LEFT OUTER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id
WHERE (TAT.AT_date_reprise >= '01/01/1950') AND (TAT.AT_date_debut <= '01/01/2079')
AND (P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42))
GROUP BY S.AT_SIEGE, AT.id_AT_contrat, TAT.AT_date_debut, TAT.AT_date_reprise, AT.AT_type_AT) AS T1 JOIN
( SELECT S.AT_SIEGE, CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAJET
FROM T1liste_AT_siege AS S LEFT OUTER JOIN
Taccident_travail AS AT ON S.id_siege = AT.id_AT_siege LEFT OUTER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id
WHERE (P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42))
AND (AT.AT_date >= '01/01/1950') AND (AT.AT_date <= '01/01/2079')
GROUP BY S.AT_SIEGE, AT.id_AT_contrat, AT.id_accident_travail, AT.id_AT_accident, AT.id_AT_lieu, TAT.AT_date_debut, TAT.AT_date_reprise, AT.AT_type_AT) AS T2 ON T1.AT_SIEGE = T2.AT_SIEGE
WHERE (@id_AG = @id_AG)
GROUP BY T1.AT_SIEGE
Je veux pouvoir faire un tableau pour sortir toutes ces données(acc. sans arrêt, acc. avec arrêt, etc.) selon le siège de lésion (type : bras, jambe, tête, etc.) sauf qu'avec ça j'ai des données bien trop supérieures à la normale. J'imagine que mon JOIN n'est pas bon ...
j'ai des données bien trop supérieures à la normale
Cela va être compliqué de t'aider sans connaître vraiment le projet ...
Quelques remarques "détails" :
Les jointures externes dans tes sous-requêtes impliquent de lister tous les sièges, même ceux n'ayant pas d'arrêt ou de personnel lié
Mettre une clause WHERE sur des colonnes de table en jointure externe annule l'effet de cette jointure, autant mettre une jointure interne
Dans tes CASE tu mets des conditions de date qui sont déjà filtrés par ta clause WHERE ... inutile non ?
Dans les deux sous-requêtes, tu fais un GROUP BY alors qu'il n'y a pas de fonction d'agrégat, quel intérêt ?
La clause WHERE @id_AG = @id_AG de la requête principale ne sert à rien, elle équivaut à écrire 1 = 1
Pour moi, les deux sous-requêtes étant identiques, je n'en vois pas l'intérêt.
Pour commencer je te propose de simplifier le problème, en repartant "du début".
Essaye la requête simplifiée suivante :
SELECT
AT.id_accident_travail,
AT.id_AT_contrat,
AT.AT_type_AT,
AT.id_AT_accident,
AT.id_nom,
AT.id_AT_siege
S.AT_SIEGE,
TAT.AT_date_debut,
TAT.AT_date_reprise
FROM
Taccident_travail AT -- On part de la liste des AT
INNER JOIN Tpersonnel P -- Forcément lié à un personnel
ON AT.id_nom = P.id
INNER JOIN T1liste_AT_siege S -- Forcément lié à un siège
ON S.id_siege = AT.id_AT_siege
INNER JOIN TAT_arret TAT -- Forcéement lié à un arrêt
ON AT.id_accident_travail = TAT.id_AT
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,38,41,42,43,47,48,49,50,51,57)
Cette requête devrait te donner tous les arrêts de travail (un accident pouvant avoir plusieurs arrêts a priori, relation 1,n) avec les données dont tu as besoin pour faire tes CASE.
Regarde le résultat de cette requête, te paraît-il cohérent ? En nombre de lignes déjà ...
L'affichage sur le report viewer s'effectue comme ceci.
Le résultat de la requête doit retourner un tableau de ce type (trop long mais tu imagines les champs)
Mettre une clause WHERE sur des colonnes de table en jointure externe annule l'effet de cette jointure, autant mettre une jointure interne
C'est justement mon objectif, si tu regardes bien la requête le champ date n'est pas le même pour ces 2 sous-requêtes la 1ère c'est AT.AT_date et la 2ème c'est TAT.AT_date_debut et TAT.AT_date_reprise qui est spécifique pour un arrêt
Dans tes CASE tu mets des conditions de date qui sont déjà filtrés par ta clause WHERE ... inutile non ?
Effectivement c'est corrigé ! J'avais oublié de l'enlever pour les priorités entre un where et un case..
Dans les deux sous-requêtes, tu fais un GROUP BY alors qu'il n'y a pas de fonction d'agrégat, quel intérêt ?
Vieux réflexe, c'est enlevé
La clause WHERE @id_AG = @id_AG de la requête principale ne sert à rien, elle équivaut à écrire 1 = 1
C'est un identifiant pour le filtrage côté code, ça pas été codé par moi je préfère le laisser , il n'a pas l'air d'influer le résultat de la requête (je le commente lors de mes tests sur SGBD et je retrouve tt le temps le même résultat).
Pour moi, les deux sous-requêtes étant identiques, je n'en vois pas l'intérêt.
la 1ére sous requête retourne les jours perdus et la 2éme les soins, les acc. avec arrêt, les acc. sans arrêt, soins et décès.
Le résultat de la requête doit retourner un tableau de ce type
J'ai bien compris, mais ne peux-tu pas tester le SQL en dehors du rapport ? Dans une console SQL ... juste pour comprendre le résultat ...
Encore deux remarques, dans tes CASE tu as la structure suivante :
AND TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950'))
AND TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950'))
On est bien d'accord que les deux premières lignes sont inutiles car redondantes avec la clause WHERE, la troisième est redondante avec et la première et la quatrième ne devrait-elle pas aussi concerner l'année 2079 (et donc redondante avec la deuxième ?) ? Si j'ai juste tu peux virer ces 4 lignes dans tous tes CASE ...
On constate aussi la structure suivant (ligne 18, 29, 40 et 51) :
DATEDIFF(
day,
(
CASE WHEN TAT.AT_date_debut < '01/01/1950'
THEN '01/01/1950'
ELSE TAT.AT_date_debut END
),
(
CASE WHEN TAT.AT_date_reprise > '31/12/1950'
THEN DATEADD( day, 1,'31/12/1950')
ELSE TAT.AT_date_reprise END
)
)
Je l'ai un peu simplifiée car tu mets du CONVERT inutile je pense
Je comprend que tu comptes le nombre de jours d'arrêt (entre début et reprise).
Si la date de début est < 01/01/1950 on force à 01/01/1950, pourquoi ? les jours d'arrêt avant 1950 ne sont pas comptés ? De plus il sont déjà filtrés dans la clause WHERE ...
Par contre, si la date de reprise est > 31/12/1950 on force à 01/01/1951 ??? pourquoi ?
Ce dernier point peut grandement impacter ton résultat !
Kadoow a écrit:
si tu regardes bien la requête le champ date n'est pas le même pour ces 2 sous-requêtes la 1ère c'est AT.AT_date et la 2ème c'est TAT.AT_date_debut et TAT.AT_date_reprise qui est spécifique pour un arrêt
Oui, j'avais raté la subtilité Cela change un peu la donne en effet ...
Si l'on considère le problème en terme de données, un accident n'a pas forcément d'arrêt, mais un arrêt est forcément lié à un accident, oui ?
Du coup, on conservant les deux sous-requêtes et en considérant mes remarques plus haut sur les CASE :
SELECT
T1.AT_SIEGE,
SUM(CNBJA_INTERIM_TRAVAIL) AS NBJA_INTERIM_TRAVAIL,
SUM(CNBJA_INTERIM_TRAJET) AS NBJA_INTERIM_TRAJET,
SUM(CNBJA_SOCIETE_TRAVAIL) AS NBJA_SOCIETE_TRAVAIL,
SUM(CNBJA_SOCIETE_TRAJET) AS NBJA_SOCIETE_TRAJET,
SUM(CNBATSOIN_INTERIM_TRAVAIL) AS NBATSOIN_INTERIM_TRAVAIL,
SUM(CNBATSOIN_INTERIM_TRAJET) AS NBATSOIN_INTERIM_TRAJET,
SUM(CNBATSOIN_SOCIETE_TRAVAIL) AS NBATSOIN_SOCIETE_TRAVAIL,
SUM(CNBATSOIN_SOCIETE_TRAJET) AS NBATSOIN_SOCIETE_TRAJET,
SUM(CNBATSA_INTERIM_TRAVAIL) AS NBATSA_INTERIM_TRAVAIL,
SUM(CNBATSA_INTERIM_TRAJET) AS NBATSA_INTERIM_TRAJET,
SUM(CNBATSA_SOCIETE_TRAVAIL) AS NBATSA_SOCIETE_TRAVAIL,
SUM(CNBATSA_SOCIETE_TRAJET) AS NBATSA_SOCIETE_TRAJET,
SUM(CNBATAA_INTERIM_TRAVAIL) AS NBATAA_INTERIM_TRAVAIL,
SUM(CNBATAA_INTERIM_TRAJET) AS NBATAA_INTERIM_TRAJET,
SUM(CNBATAA_SOCIETE_TRAVAIL) AS NBATAA_SOCIETE_TRAVAIL,
SUM(CNBATAA_SOCIETE_TRAJET) AS NBATAA_SOCIETE_TRAJET,
SUM(CNBATDC_INTERIM_TRAVAIL) AS NBATDC_INTERIM_TRAVAIL,
SUM(CNBATDC_INTERIM_TRAJET) AS NBATDC_INTERIM_TRAJET,
SUM(CNBATDC_SOCIETE_TRAVAIL) AS NBATDC_SOCIETE_TRAVAIL,
SUM(CNBATDC_SOCIETE_TRAJET) AS NBATDC_SOCIETE_TRAJET,
SUM(CNBAT_INTERIM_TRAVAIL) AS NBAT_INTERIM_TRAVAIL,
SUM(CNBAT_INTERIM_TRAJET) AS NBAT_INTERIM_TRAJET,
SUM(CNBAT_SOCIETE_TRAVAIL) AS NBAT_SOCIETE_TRAVAIL,
SUM(CNBAT_SOCIETE_TRAJET) AS NBAT_SOCIETE_TRAJET
FROM
(
SELECT
S.AT_SIEGE,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
AND AT.AT_date >= '01/01/1950'
AND AT.AT_date <= '01/01/2079'
) AS T2
LEFT JOIN (
SELECT
S.AT_SIEGE,
CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day, TAT.AT_date_debut, TAT.AT_date_reprise )
ELSE 0
END AS CNBJA_SOCIETE_TRAVAIL,
CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day, TAT.AT_date_debut, TAT.AT_date_reprise )
ELSE 0
END AS CNBJA_SOCIETE_TRAJET,
CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day, TAT.AT_date_debut, TAT.AT_date_reprise )
ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day, TAT.AT_date_debut, TAT.AT_date_reprise )
ELSE 0
END AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
) AS T1
ON T1.AT_SIEGE = T2.AT_SIEGE
GROUP BY T1.AT_SIEGE
Par rapport aux identificateurs '01/01/1950' et '01/01/2079', l'un est remplacé par la date début et l'autre par la date de fin défini par l'utilisateur
Je l'ai un peu simplifiée car tu mets du CONVERT inutile je pense
Je comprend que tu comptes le nombre de jours d'arrêt (entre début et reprise).
Si la date de début est < 01/01/1950 on force à 01/01/1950, pourquoi ? les jours d'arrêt avant 1950 ne sont pas comptés ? De plus il sont déjà filtrés dans la clause WHERE ...
Par contre, si la date de reprise est > 31/12/1950 on force à 01/01/1951 ??? pourquoi ?
Ce dernier point peut grandement impacter ton résultat !
Une problématique que j'ai eu du mal à comprendre également mais je vais te prendre un exemple :
J'ai un accident avec arrêt de travail le 20/10/2017 qui dure jusqu'au 08/03/2019, je dois pouvoir récupérer 365 jours pour l'année 2018 ( le cas de l'année bissextile n'est pas géré) , les 72 jours pour l'année 2017 ou les 69 jours pour l'année 2019.
--CAS POUR ANNEE 2017
CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.AT_type_AT = 1) -- Filtre selon type contrat et type accident
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/2017'))) -- récupérer l'AT avec une date de reprise supérieure au 01/01 de l'année choisie.
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/2017'))) -- récupérer l'AT avec une date de début inférieure au 31/12 de l'année choisie.
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/2017')) -- si l'AT a commencé dans els années précédants 2017 alors mettre 01/01/2017
THEN '01/01/' + CONVERT(varchar(4), year('01/01/2017'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/2017')) -- si l'AT a été fini dans les années après 2017 alors mettre 31/12/2017
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/2017'))) -- je récupérais 364 ici c'est pour qu'il y a un DATEADD
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_SOCIETE_TRAVAIL,
Si l'on considère le problème en terme de données, un accident n'a pas forcément d'arrêt, mais un arrêt est forcément lié à un accident, oui ?
Exactement, il y a des accident de travail sans jours d’arrêts (et donc sans arrêt) et d'autre d'une durée défini par le médecin (avec arrêt).
EDIT :
J'ai bien compris, mais ne peux-tu pas tester le SQL en dehors du rapport ? Dans une console SQL ... juste pour comprendre le résultat ...
J'ai un accident avec arrêt de travail le 20/10/2017 qui dure jusqu'au 08/03/2019, je dois pouvoir récupérer 365 jours pour l'année 2018 ( le cas de l'année bissextile n'est pas géré) , les 72 jours pour l'année 2017 ou les 69 jours pour l'année 2019.
Kadoow a écrit:
Par rapport aux identificateurs '01/01/1950' et '01/01/2079', l'un est remplacé par la date début et l'autre par la date de fin défini par l'utilisateur
OK, par contre on est bien d'accord que la requête ne présentera pas le découpage par année ...
Pour reprendre l'exemple donné : si l'utilisateur saisi comme date 01/01/2017 et 31/12/2018, le résultat sera 437 (72+365).
Du coup je poste une dernière fois la requête corrigée avec cette notion de variable :
SELECT
T1.AT_SIEGE,
SUM(CNBJA_INTERIM_TRAVAIL) AS NBJA_INTERIM_TRAVAIL,
SUM(CNBJA_INTERIM_TRAJET) AS NBJA_INTERIM_TRAJET,
SUM(CNBJA_SOCIETE_TRAVAIL) AS NBJA_SOCIETE_TRAVAIL,
SUM(CNBJA_SOCIETE_TRAJET) AS NBJA_SOCIETE_TRAJET,
SUM(CNBATSOIN_INTERIM_TRAVAIL) AS NBATSOIN_INTERIM_TRAVAIL,
SUM(CNBATSOIN_INTERIM_TRAJET) AS NBATSOIN_INTERIM_TRAJET,
SUM(CNBATSOIN_SOCIETE_TRAVAIL) AS NBATSOIN_SOCIETE_TRAVAIL,
SUM(CNBATSOIN_SOCIETE_TRAJET) AS NBATSOIN_SOCIETE_TRAJET,
SUM(CNBATSA_INTERIM_TRAVAIL) AS NBATSA_INTERIM_TRAVAIL,
SUM(CNBATSA_INTERIM_TRAJET) AS NBATSA_INTERIM_TRAJET,
SUM(CNBATSA_SOCIETE_TRAVAIL) AS NBATSA_SOCIETE_TRAVAIL,
SUM(CNBATSA_SOCIETE_TRAJET) AS NBATSA_SOCIETE_TRAJET,
SUM(CNBATAA_INTERIM_TRAVAIL) AS NBATAA_INTERIM_TRAVAIL,
SUM(CNBATAA_INTERIM_TRAJET) AS NBATAA_INTERIM_TRAJET,
SUM(CNBATAA_SOCIETE_TRAVAIL) AS NBATAA_SOCIETE_TRAVAIL,
SUM(CNBATAA_SOCIETE_TRAJET) AS NBATAA_SOCIETE_TRAJET,
SUM(CNBATDC_INTERIM_TRAVAIL) AS NBATDC_INTERIM_TRAVAIL,
SUM(CNBATDC_INTERIM_TRAJET) AS NBATDC_INTERIM_TRAJET,
SUM(CNBATDC_SOCIETE_TRAVAIL) AS NBATDC_SOCIETE_TRAVAIL,
SUM(CNBATDC_SOCIETE_TRAJET) AS NBATDC_SOCIETE_TRAJET,
SUM(CNBAT_INTERIM_TRAVAIL) AS NBAT_INTERIM_TRAVAIL,
SUM(CNBAT_INTERIM_TRAJET) AS NBAT_INTERIM_TRAJET,
SUM(CNBAT_SOCIETE_TRAVAIL) AS NBAT_SOCIETE_TRAVAIL,
SUM(CNBAT_SOCIETE_TRAJET) AS NBAT_SOCIETE_TRAJET
FROM
(
SELECT
S.AT_SIEGE,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
AND AT.AT_date >= '01/01/1950'
AND AT.AT_date <= '01/01/2079'
) AS T2
LEFT JOIN (
SELECT
S.AT_SIEGE,
CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)
ELSE 0
END AS CNBJA_SOCIETE_TRAVAIL,
CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)
ELSE 0
END AS CNBJA_SOCIETE_TRAJET,
CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)
ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)
ELSE 0
END AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
) AS T1
ON T1.AT_SIEGE = T2.AT_SIEGE
GROUP BY T1.AT_SIEGE
Si le résultat n'est pas celui attendu, il faut faire ce que je t'ai proposé plus tôt : regarder le détail des arrêts sur une petite période (sans regrouper par siège), et voir ce qui ne va pas ... A savoir pour les accidents :
SELECT
AT.id_accident_travail,
AT.AT_date,
AT.id_AT_contrat,
AT.AT_type_AT,
AT.id_AT_accident,
AT.id_nom,
AT.id_AT_siege
S.AT_SIEGE
FROM
Taccident_travail AT
INNER JOIN Tpersonnel P
ON AT.id_nom = P.id
INNER JOIN T1liste_AT_siege S
ON S.id_siege = AT.id_AT_siege
WHERE
AT.AT_date >= '01/01/1950'
AND AT.AT_date <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,38,41,42,43,47,48,49,50,51,57)
Et pour les arrêts :
SELECT
AT.id_accident_travail,
AT.AT_date,
AT.id_AT_contrat,
AT.AT_type_AT,
AT.id_AT_accident,
AT.id_nom,
AT.id_AT_siege
S.AT_SIEGE,
TAT.AT_date_debut,
TAT.AT_date_reprise,
DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
) AS nb_jours
FROM
Taccident_travail AT -- On part de la liste des AT
INNER JOIN Tpersonnel P -- Forcément lié à un personnel
ON AT.id_nom = P.id
INNER JOIN T1liste_AT_siege S -- Forcément lié à un siège
ON S.id_siege = AT.id_AT_siege
INNER JOIN TAT_arret TAT -- Forcéement lié à un arrêt
ON AT.id_accident_travail = TAT.id_AT
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,38,41,42,43,47,48,49,50,51,57)
OK, par contre on est bien d'accord que la requête ne présentera pas le découpage par année ...
Exactement, certaines requêtes présentent le même format mais avec AT_date au lieu de at_siege pour faire ceci..
Pour reprendre l'exemple donné : si l'utilisateur saisi comme date 01/01/2017 et 31/12/2018, le résultat sera 437 (72+365).
Dernière petite chose, on ne gère que la recherche limité à 1 an d'interval max:
Comme ceci
EDIT : Je teste tes requêtes au fur et à mesure mais j'obtiens un nombre de jour d'arrêt bien trop supérieur à la normale.
Ça va être difficile de t'indiquer les données que je dois avoir, pour l'année 2016 je dois me retrouver à 1200 jours perdus environ et là +3000 avec ta requête.
EDIT2 :
SELECT CASE WHEN (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/2016'))) AND
(TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/2016'))) THEN DATEDIFF(day,
(CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/2016')) THEN '01/01/' + CONVERT(varchar(4),
year('01/01/2016')) ELSE TAT.AT_date_debut END), (CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4),
year('01/01/2016')) THEN DATEADD(day, 1, '31/12/' + CONVERT(varchar(4), year('01/01/2016'))) ELSE TAT.AT_date_reprise END))
ELSE 0 END AS CNBJA_SOCIETE_TRAVAIL, nom, AT_date_debut, AT_date_reprise
FROM Taccident_travail AS AT INNER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id
WHERE (P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42))
AND (AT.AT_type_AT = 1) AND (TAT.AT_date_reprise >= '01/01/2016' AND TAT.AT_date_debut <= '31/12/2016') AND (AT.id_AT_contrat <> 30)
voilà vraiment ce que je dois rettrouver comme données et j'ai mis les date pour que tu puisses comprendre le calcul.
Oui j'ai l'impression qu'il y a beaucoup trop de doublon :
SELECT
T1.AT_SIEGE,
SUM(CNBJA_INTERIM_TRAVAIL) AS NBJA_INTERIM_TRAVAIL,
SUM(CNBJA_INTERIM_TRAJET) AS NBJA_INTERIM_TRAJET,
SUM(CNBJA_SOCIETE_TRAVAIL) AS NBJA_SOCIETE_TRAVAIL,
SUM(CNBJA_SOCIETE_TRAJET) AS NBJA_SOCIETE_TRAJET,
SUM(CNBATSOIN_INTERIM_TRAVAIL) AS NBATSOIN_INTERIM_TRAVAIL,
SUM(CNBATSOIN_INTERIM_TRAJET) AS NBATSOIN_INTERIM_TRAJET,
SUM(CNBATSOIN_SOCIETE_TRAVAIL) AS NBATSOIN_SOCIETE_TRAVAIL,
SUM(CNBATSOIN_SOCIETE_TRAJET) AS NBATSOIN_SOCIETE_TRAJET,
SUM(CNBATSA_INTERIM_TRAVAIL) AS NBATSA_INTERIM_TRAVAIL,
SUM(CNBATSA_INTERIM_TRAJET) AS NBATSA_INTERIM_TRAJET,
SUM(CNBATSA_SOCIETE_TRAVAIL) AS NBATSA_SOCIETE_TRAVAIL,
SUM(CNBATSA_SOCIETE_TRAJET) AS NBATSA_SOCIETE_TRAJET,
SUM(CNBATAA_INTERIM_TRAVAIL) AS NBATAA_INTERIM_TRAVAIL,
SUM(CNBATAA_INTERIM_TRAJET) AS NBATAA_INTERIM_TRAJET,
SUM(CNBATAA_SOCIETE_TRAVAIL) AS NBATAA_SOCIETE_TRAVAIL,
SUM(CNBATAA_SOCIETE_TRAJET) AS NBATAA_SOCIETE_TRAJET,
SUM(CNBATDC_INTERIM_TRAVAIL) AS NBATDC_INTERIM_TRAVAIL,
SUM(CNBATDC_INTERIM_TRAJET) AS NBATDC_INTERIM_TRAJET,
SUM(CNBATDC_SOCIETE_TRAVAIL) AS NBATDC_SOCIETE_TRAVAIL,
SUM(CNBATDC_SOCIETE_TRAJET) AS NBATDC_SOCIETE_TRAJET,
SUM(CNBAT_INTERIM_TRAVAIL) AS NBAT_INTERIM_TRAVAIL,
SUM(CNBAT_INTERIM_TRAJET) AS NBAT_INTERIM_TRAJET,
SUM(CNBAT_SOCIETE_TRAVAIL) AS NBAT_SOCIETE_TRAVAIL,
SUM(CNBAT_SOCIETE_TRAJET) AS NBAT_SOCIETE_TRAJET
FROM
(
SELECT
S.AT_SIEGE,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (0, 0, 0, 0, 0)
AND AT.AT_date >= '01/01/1950'
AND AT.AT_date <= '01/01/2079'
) AS T2
LEFT JOIN (
SELECT
S.AT_SIEGE,
CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.AT_type_AT = 1)
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_SOCIETE_TRAVAIL,
CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.AT_type_AT = 0)
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_SOCIETE_TRAJET,
CASE WHEN (AT.id_AT_contrat = 30) AND (AT.AT_type_AT = 1)
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
CASE WHEN (AT.id_AT_contrat = 30) AND (AT.AT_type_AT = 0)
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (0, 0, 0, 0, 0)
) AS T1
ON T1.AT_SIEGE = T2.AT_SIEGE
WHERE (@id_AG = @id_AG)
GROUP BY T1.AT_SIEGE
Ta requête avec les champs JA que j'ai remplacé par mes CASE qui sortent exactement les bonnes données. Ca sort ça :
Et puis la requête que je faisais avant qui était bonne sauf pour les AT avec arrêt (je dois être à 30 normalement) :
SELECT AT_SIEGE, SUM(CNBJA_INTERIM_TRAVAIL) AS NBJA_INTERIM_TRAVAIL, SUM(CNBJA_INTERIM_TRAJET) AS NBJA_INTERIM_TRAJET,
SUM(CNBJA_SOCIETE_TRAVAIL) AS NBJA_SOCIETE_TRAVAIL, SUM(CNBJA_SOCIETE_TRAJET) AS NBJA_SOCIETE_TRAJET, SUM(CNBATSOIN_INTERIM_TRAVAIL)
AS NBATSOIN_INTERIM_TRAVAIL, SUM(CNBATSOIN_INTERIM_TRAJET) AS NBATSOIN_INTERIM_TRAJET, SUM(CNBATSOIN_SOCIETE_TRAVAIL)
AS NBATSOIN_SOCIETE_TRAVAIL, SUM(CNBATSOIN_SOCIETE_TRAJET) AS NBATSOIN_SOCIETE_TRAJET, SUM(CNBATSA_INTERIM_TRAVAIL)
AS NBATSA_INTERIM_TRAVAIL, SUM(CNBATSA_INTERIM_TRAJET) AS NBATSA_INTERIM_TRAJET, SUM(CNBATSA_SOCIETE_TRAVAIL)
AS NBATSA_SOCIETE_TRAVAIL, SUM(CNBATSA_SOCIETE_TRAJET) AS NBATSA_SOCIETE_TRAJET, SUM(CNBATAA_INTERIM_TRAVAIL)
AS NBATAA_INTERIM_TRAVAIL, SUM(CNBATAA_INTERIM_TRAJET) AS NBATAA_INTERIM_TRAJET, SUM(CNBATAA_SOCIETE_TRAVAIL)
AS NBATAA_SOCIETE_TRAVAIL, SUM(CNBATAA_SOCIETE_TRAJET) AS NBATAA_SOCIETE_TRAJET, SUM(CNBATDC_INTERIM_TRAVAIL)
AS NBATDC_INTERIM_TRAVAIL, SUM(CNBATDC_INTERIM_TRAJET) AS NBATDC_INTERIM_TRAJET, SUM(CNBATDC_SOCIETE_TRAVAIL)
AS NBATDC_SOCIETE_TRAVAIL, SUM(CNBATDC_SOCIETE_TRAJET) AS NBATDC_SOCIETE_TRAJET, SUM(CNBAT_INTERIM_TRAVAIL) AS NBAT_INTERIM_TRAVAIL,
SUM(CNBAT_INTERIM_TRAJET) AS NBAT_INTERIM_TRAJET, SUM(CNBAT_SOCIETE_TRAVAIL) AS NBAT_SOCIETE_TRAVAIL, SUM(CNBAT_SOCIETE_TRAJET) AS NBAT_SOCIETE_TRAJET
FROM (SELECT S.AT_SIEGE,
CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.AT_type_AT = 1)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_SOCIETE_TRAVAIL,
CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.AT_type_AT = 0)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_SOCIETE_TRAJET,
CASE WHEN (AT.id_AT_contrat = 30) AND (AT.AT_type_AT = 1)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
CASE WHEN (AT.id_AT_contrat = 30) AND (AT.AT_type_AT = 0)
AND (TAT.AT_date_reprise >= '01/01/1950' AND TAT.AT_date_debut <= '01/01/2079')
AND (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/1950')))
AND (TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/1950')))
THEN DATEDIFF(day, (CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
THEN '01/01/' + CONVERT(varchar(4), year('01/01/1950'))
ELSE TAT.AT_date_debut END),
(CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4), year('01/01/1950'))
THEN DATEADD(day, 1,'31/12/' + CONVERT(varchar(4), year('01/01/1950')))
ELSE TAT.AT_date_reprise END)) ELSE 0 END AS CNBJA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END AS CNBATSOIN_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END AS CNBATSA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END AS CNBATAA_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END AS CNBATDC_SOCIETE_TRAJET,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAVAIL,
CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_INTERIM_TRAJET,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAVAIL,
CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END AS CNBAT_SOCIETE_TRAJET
FROM T1liste_AT_siege AS S LEFT OUTER JOIN
Taccident_travail AS AT ON N.id_nature = AT.id_AT_siege LEFT OUTER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id
WHERE (TAT.AT_date_reprise >= '01/01/1950') AND (TAT.AT_date_debut <= '01/01/2079') AND (P.id_etablissement IN (0, 0, 0, 0, 0)) OR
(P.id_etablissement IN (0, 0, 0, 0, 0)) AND (AT.AT_date >= '01/01/1950') AND (AT.AT_date <= '01/01/2079')
GROUP BY S.AT_SIEGE, AT.id_AT_contrat, AT.id_accident_travail, AT.id_AT_accident, AT.id_AT_lieu, TAT.AT_date_debut, TAT.AT_date_reprise, AT.AT_type_AT) AS T
WHERE (@id_AG = @id_AG)
GROUP BY AT_SIEGE
EDIT: 1ère requête, tu penses qu'il faut faire des sous requêtes dans la sous-requête x) ? j'arrive vraiment pas à capter la priorité des éléments dans cette requête ici ...
Oui j'ai l'impression qu'il y a beaucoup trop de doublon
Bien sûr qu'il y a trop de doublon ...
Comme il n'y a pas de GROUP BY dans les sous-requêtes, chaque id_siege apparaissant plusieurs fois dans chacune, ça multiplie d'autant les possibilités ... Je n'avais même pas percuté ...
En passant les SUM dans les sous-requêtes, cela devrait être plus pertinent ... au passage vire moi ces CONVERT à tout va ... pas possible ?
SELECT
T1.AT_SIEGE,
CNBJA_INTERIM_TRAVAIL AS NBJA_INTERIM_TRAVAIL,
CNBJA_INTERIM_TRAJET AS NBJA_INTERIM_TRAJET,
CNBJA_SOCIETE_TRAVAIL AS NBJA_SOCIETE_TRAVAIL,
CNBJA_SOCIETE_TRAJET AS NBJA_SOCIETE_TRAJET,
CNBATSOIN_INTERIM_TRAVAIL AS NBATSOIN_INTERIM_TRAVAIL,
CNBATSOIN_INTERIM_TRAJET AS NBATSOIN_INTERIM_TRAJET,
CNBATSOIN_SOCIETE_TRAVAIL AS NBATSOIN_SOCIETE_TRAVAIL,
CNBATSOIN_SOCIETE_TRAJET AS NBATSOIN_SOCIETE_TRAJET,
CNBATSA_INTERIM_TRAVAIL AS NBATSA_INTERIM_TRAVAIL,
CNBATSA_INTERIM_TRAJET AS NBATSA_INTERIM_TRAJET,
CNBATSA_SOCIETE_TRAVAIL AS NBATSA_SOCIETE_TRAVAIL,
CNBATSA_SOCIETE_TRAJET AS NBATSA_SOCIETE_TRAJET,
CNBATAA_INTERIM_TRAVAIL AS NBATAA_INTERIM_TRAVAIL,
CNBATAA_INTERIM_TRAJET AS NBATAA_INTERIM_TRAJET,
CNBATAA_SOCIETE_TRAVAIL AS NBATAA_SOCIETE_TRAVAIL,
CNBATAA_SOCIETE_TRAJET AS NBATAA_SOCIETE_TRAJET,
CNBATDC_INTERIM_TRAVAIL AS NBATDC_INTERIM_TRAVAIL,
CNBATDC_INTERIM_TRAJET AS NBATDC_INTERIM_TRAJET,
CNBATDC_SOCIETE_TRAVAIL AS NBATDC_SOCIETE_TRAVAIL,
CNBATDC_SOCIETE_TRAJET AS NBATDC_SOCIETE_TRAJET,
CNBAT_INTERIM_TRAVAIL AS NBAT_INTERIM_TRAVAIL,
CNBAT_INTERIM_TRAJET AS NBAT_INTERIM_TRAJET,
CNBAT_SOCIETE_TRAVAIL AS NBAT_SOCIETE_TRAVAIL,
CNBAT_SOCIETE_TRAJET AS NBAT_SOCIETE_TRAJET
FROM
(
SELECT
S.AT_SIEGE,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
AND AT.AT_date >= '01/01/1950'
AND AT.AT_date <= '01/01/2079'
GROUP BY S.AT_SIEGE
) AS T2
LEFT JOIN (
SELECT
S.AT_SIEGE,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN DATEADD( day, 1, '01/01/2079' ) ELSE TAT.AT_date_reprise END
)
ELSE 0
END ) AS CNBJA_SOCIETE_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN DATEADD( day, 1, '01/01/2079' ) ELSE TAT.AT_date_reprise END
)
ELSE 0
END ) AS CNBJA_SOCIETE_TRAJET,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN DATEADD( day, 1, '01/01/2079' ) ELSE TAT.AT_date_reprise END
)
ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN DATEADD( day, 1, '01/01/2079' ) ELSE TAT.AT_date_reprise END
)
ELSE 0
END ) AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
GROUP BY S.AT_SIEGE
) AS T1
ON T1.AT_SIEGE = T2.AT_SIEGE
ffectivement j'avais complètement zappé la vrai fonctionn du GROUP BY ...
Concernant les CONVERT, tu vas trouver ça dégueulasse et moi aussi tkt mais mon responsable avec ce convert gère une action utilisateur pour éviter que si la personne mette 20/03/2016 ça mette 01/01/2016 et les gens qui vont inverser... Je lui ai demandé de faire ça côté code mais j'ai pas le temps mon contrat se termine bientôt.
Alors j'ai testé la requête est quasiment bonne (on s'y rapproche ...) sauf pour les jours perdus qui sont diminués mais si je remplace par mes CASE CONVERT on me demande des champs dans le group by en supplément forcément ...
Tu peux aussi me donner une solution sans les convert je me débrouillerai avec mon responsable
Par contre j'ai du nul au niveau des AT_siege bizarre ?
Il manque une ptite parenthèse aussi :p.
EDIT :
Alors j'ai testé plusieurs solutions je retrouve la bonne donnée de jour d'arrêt avec cette requête et seulement celle-ci :
SELECT CASE WHEN (TAT.AT_date_reprise >= '01/01/' + CONVERT(varchar(4), YEAR('01/01/2016'))) AND
(TAT.AT_date_debut <= '31/12/' + CONVERT(varchar(4), YEAR('01/01/2016'))) THEN DATEDIFF(day,
(CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/2016')) THEN '01/01/' + CONVERT(varchar(4),
year('01/01/2016')) ELSE TAT.AT_date_debut END), (CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4),
year('01/01/2016')) THEN DATEADD(day, 1, '31/12/' + CONVERT(varchar(4), year('01/01/2016'))) ELSE TAT.AT_date_reprise END))
ELSE 0 END AS CNBJA_SOCIETE_TRAVAIL, nom, AT_date_debut, AT_date_reprise
FROM Taccident_travail AS AT INNER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id
WHERE (P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42))
AND (AT.AT_type_AT = 1) AND (TAT.AT_date_reprise >= '01/01/2016' AND TAT.AT_date_debut <= '31/12/2016') AND (AT.id_AT_contrat <> 30)
Si je déplace ce qu'il y a dans le where avant le then cela me sors des des données sans jour d'arrêt.
Ca sort :
45 lignes
SELECT CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
AND (TAT.AT_date_reprise >= '01/01/2016')
AND (TAT.AT_date_debut <= '31/12/2016')
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN DATEADD( day, 1, '31/12/2016' ) ELSE TAT.AT_date_reprise END
)
ELSE 0
END AS CNBJA_SOCIETE_TRAVAIL, nom, TAT.AT_date_debut, TAT.AT_date_reprise
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/2016'
AND TAT.AT_date_debut <= '31/12/2016'
AND P.id_etablissement IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,38,41,42,43,47,48,49,50,51,57)
Me sort :
Comme tu vois il y a beaucoup de données à 0... j'ai plus l'impression que c'est du doublon (59lignes)
Merci pour ton aide encore.
EDIT2 : Bon cette requête fonctionne il faudrait que je fasse une sous requête pour chaque champ de JA ?
SELECT DATEDIFF(day,
(CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/2016')) THEN '01/01/' + CONVERT(varchar(4),
year('01/01/2016')) ELSE TAT.AT_date_debut END), (CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4),
year('01/01/2016')) THEN DATEADD(day, 1, '31/12/' + CONVERT(varchar(4), year('01/01/2016'))) ELSE TAT.AT_date_reprise END))
AS CNBJA_SOCIETE_TRAVAIL, nom, AT_date_debut, AT_date_reprise
FROM Taccident_travail AS AT INNER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id
WHERE (P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42))
AND (TAT.AT_date_reprise >= '01/01/2016' AND TAT.AT_date_debut <= '31/12/2016') AND (AT.AT_type_AT = 1) AND (AT.id_AT_contrat <> 30)
pour éviter que si la personne mette 20/03/2016 ça mette 01/01/2016 et les gens qui vont inverser
Le mieux étant quand même de faire directement saisir l'année à l'utilisateur alors ... enfin c'est un autre sujet
Kadoow a écrit:
les jours perdus qui sont diminués
Oui, je pense que c'est à cause du DATEADD que j'ai oublié dans le ELSE ... ou plus simplement en rajoutant un +1 après le DATEDIFF
Kadoow a écrit:
j'ai du nul au niveau des AT_siege bizarre ?
Oui, car dans le SELECT on prend T1.AT_SIEGE qui est dans la table des arrêts ... donc si un siège n'a pas d'arrêt => NULL ... Juste mettre T2 à la place
Au passage, un petit COALESCE (ou NVL si tu préfères) pour "masquer" les NULL.
Au final nouvelle proposition :
SELECT
T2.AT_SIEGE,
COALESCE( CNBJA_INTERIM_TRAVAIL, 0 ) AS NBJA_INTERIM_TRAVAIL,
COALESCE( CNBJA_INTERIM_TRAJET, 0 ) AS NBJA_INTERIM_TRAJET,
COALESCE( CNBJA_SOCIETE_TRAVAIL, 0 ) AS NBJA_SOCIETE_TRAVAIL,
COALESCE( CNBJA_SOCIETE_TRAJET, 0 ) AS NBJA_SOCIETE_TRAJET,
COALESCE( CNBATSOIN_INTERIM_TRAVAIL, 0 ) AS NBATSOIN_INTERIM_TRAVAIL,
COALESCE( CNBATSOIN_INTERIM_TRAJET, 0 ) AS NBATSOIN_INTERIM_TRAJET,
COALESCE( CNBATSOIN_SOCIETE_TRAVAIL, 0 ) AS NBATSOIN_SOCIETE_TRAVAIL,
COALESCE( CNBATSOIN_SOCIETE_TRAJET, 0 ) AS NBATSOIN_SOCIETE_TRAJET,
COALESCE( CNBATSA_INTERIM_TRAVAIL, 0 ) AS NBATSA_INTERIM_TRAVAIL,
COALESCE( CNBATSA_INTERIM_TRAJET, 0 ) AS NBATSA_INTERIM_TRAJET,
COALESCE( CNBATSA_SOCIETE_TRAVAIL, 0 ) AS NBATSA_SOCIETE_TRAVAIL,
COALESCE( CNBATSA_SOCIETE_TRAJET, 0 ) AS NBATSA_SOCIETE_TRAJET,
COALESCE( CNBATAA_INTERIM_TRAVAIL, 0 ) AS NBATAA_INTERIM_TRAVAIL,
COALESCE( CNBATAA_INTERIM_TRAJET, 0 ) AS NBATAA_INTERIM_TRAJET,
COALESCE( CNBATAA_SOCIETE_TRAVAIL, 0 ) AS NBATAA_SOCIETE_TRAVAIL,
COALESCE( CNBATAA_SOCIETE_TRAJET, 0 ) AS NBATAA_SOCIETE_TRAJET,
COALESCE( CNBATDC_INTERIM_TRAVAIL, 0 ) AS NBATDC_INTERIM_TRAVAIL,
COALESCE( CNBATDC_INTERIM_TRAJET, 0 ) AS NBATDC_INTERIM_TRAJET,
COALESCE( CNBATDC_SOCIETE_TRAVAIL, 0 ) AS NBATDC_SOCIETE_TRAVAIL,
COALESCE( CNBATDC_SOCIETE_TRAJET, 0 ) AS NBATDC_SOCIETE_TRAJET,
COALESCE( CNBAT_INTERIM_TRAVAIL, 0 ) AS NBAT_INTERIM_TRAVAIL,
COALESCE( CNBAT_INTERIM_TRAJET, 0 ) AS NBAT_INTERIM_TRAJET,
COALESCE( CNBAT_SOCIETE_TRAVAIL, 0 ) AS NBAT_SOCIETE_TRAVAIL,
COALESCE( CNBAT_SOCIETE_TRAJET, 0 ) AS NBAT_SOCIETE_TRAJET
FROM
(
SELECT
S.AT_SIEGE,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
AND AT.AT_date >= '01/01/1950'
AND AT.AT_date <= '01/01/2079'
GROUP BY S.AT_SIEGE
) AS T2
LEFT JOIN (
SELECT
S.AT_SIEGE,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAJET,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
GROUP BY S.AT_SIEGE
) AS T1
ON T1.AT_SIEGE = T2.AT_SIEGE
Qu'est-ce-que ça dit ?
Après il serait bien de créer des vues sur la base de données pour pouvoir simplifier l'écriture de la requête générant le rapport ...
SELECT CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END AS CNBJA_SOCIETE_TRAVAILL, nom, TAT.AT_date_debut, TAT.AT_date_reprise
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/2016'
AND TAT.AT_date_debut <= '31/12/2016'
AND P.id_etablissement IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,38,41,42,43,47,48,49,50,51,57)
Le Case que tu me proposes sors des données fausses ... :
Par exemple ici je récupère 686 jours d'arrêt pour 1 an d'interval , étrange ...
Si je déplace ce qu'il y a dans le where avant le then cela me sors des des données sans jour d'arrêt
C'est normal, les arrêts ne correspondant pas aux filtres contrat et type sont quand même sélectionnés, mais le CASE leur met 0 ... Ce ne sont pas des doublons ...
Pour moi, la requête proposée précédemment est la bonne :
SELECT
T2.AT_SIEGE,
COALESCE( CNBJA_INTERIM_TRAVAIL, 0 ) AS NBJA_INTERIM_TRAVAIL,
COALESCE( CNBJA_INTERIM_TRAJET, 0 ) AS NBJA_INTERIM_TRAJET,
COALESCE( CNBJA_SOCIETE_TRAVAIL, 0 ) AS NBJA_SOCIETE_TRAVAIL,
COALESCE( CNBJA_SOCIETE_TRAJET, 0 ) AS NBJA_SOCIETE_TRAJET,
COALESCE( CNBATSOIN_INTERIM_TRAVAIL, 0 ) AS NBATSOIN_INTERIM_TRAVAIL,
COALESCE( CNBATSOIN_INTERIM_TRAJET, 0 ) AS NBATSOIN_INTERIM_TRAJET,
COALESCE( CNBATSOIN_SOCIETE_TRAVAIL, 0 ) AS NBATSOIN_SOCIETE_TRAVAIL,
COALESCE( CNBATSOIN_SOCIETE_TRAJET, 0 ) AS NBATSOIN_SOCIETE_TRAJET,
COALESCE( CNBATSA_INTERIM_TRAVAIL, 0 ) AS NBATSA_INTERIM_TRAVAIL,
COALESCE( CNBATSA_INTERIM_TRAJET, 0 ) AS NBATSA_INTERIM_TRAJET,
COALESCE( CNBATSA_SOCIETE_TRAVAIL, 0 ) AS NBATSA_SOCIETE_TRAVAIL,
COALESCE( CNBATSA_SOCIETE_TRAJET, 0 ) AS NBATSA_SOCIETE_TRAJET,
COALESCE( CNBATAA_INTERIM_TRAVAIL, 0 ) AS NBATAA_INTERIM_TRAVAIL,
COALESCE( CNBATAA_INTERIM_TRAJET, 0 ) AS NBATAA_INTERIM_TRAJET,
COALESCE( CNBATAA_SOCIETE_TRAVAIL, 0 ) AS NBATAA_SOCIETE_TRAVAIL,
COALESCE( CNBATAA_SOCIETE_TRAJET, 0 ) AS NBATAA_SOCIETE_TRAJET,
COALESCE( CNBATDC_INTERIM_TRAVAIL, 0 ) AS NBATDC_INTERIM_TRAVAIL,
COALESCE( CNBATDC_INTERIM_TRAJET, 0 ) AS NBATDC_INTERIM_TRAJET,
COALESCE( CNBATDC_SOCIETE_TRAVAIL, 0 ) AS NBATDC_SOCIETE_TRAVAIL,
COALESCE( CNBATDC_SOCIETE_TRAJET, 0 ) AS NBATDC_SOCIETE_TRAJET,
COALESCE( CNBAT_INTERIM_TRAVAIL, 0 ) AS NBAT_INTERIM_TRAVAIL,
COALESCE( CNBAT_INTERIM_TRAJET, 0 ) AS NBAT_INTERIM_TRAJET,
COALESCE( CNBAT_SOCIETE_TRAVAIL, 0 ) AS NBAT_SOCIETE_TRAVAIL,
COALESCE( CNBAT_SOCIETE_TRAJET, 0 ) AS NBAT_SOCIETE_TRAJET
FROM
(
SELECT
S.AT_SIEGE,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
AND AT.AT_date >= '01/01/1950'
AND AT.AT_date <= '01/01/2079'
GROUP BY S.AT_SIEGE
) AS T2
LEFT JOIN (
SELECT
S.AT_SIEGE,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAJET,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/1950' THEN '01/01/1950' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '01/01/2079' THEN '01/01/2079' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN T1liste_AT_siege AS S
ON S.id_siege = AT.id_AT_siege
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/1950'
AND TAT.AT_date_debut <= '01/01/2079'
AND P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
GROUP BY S.AT_SIEGE
) AS T1
ON T1.AT_SIEGE = T2.AT_SIEGE
Kudbuloken a écrit il y a 11h :
En passant par des vues, je me demande si ce ne serait pas nettement plus simple.
Benzouye avait écrit il y a 14h :
Après il serait bien de créer des vues sur la base de données pour pouvoir simplifier l'écriture de la requête générant le rapport ...
Concernant les vues je suis d'accord avec vous, mais je n'ai aps le temps de me poser dessus et j'en ai parlé à mon responsable pour son dev. plus tard...
J'ai un accident avec arrêt de travail le 20/10/2017 qui dure jusqu'au 08/03/2019, je dois pouvoir récupérer 365 jours pour l'année 2018 ( le cas de l'année bissextile n'est pas géré) , les 72 jours pour l'année 2017 (si je fais une recherche entre 01/01/2017 et 31/12/2017) ou les 69 jours pour l'année 2019 (si je fais une recherche entre 01/01/2019 et 31/12/2019).
Benzouye ton CASE ne gère pas cette condition j'ai l'impression, dans tout les cas le nombre de jour perdu est inférieur à ce que je devrais retrouver. Je retrouve les bonne valeur en spécifiant toutes les conditions dans le WHERE like this :
SELECT DATEDIFF(day,
(CASE WHEN TAT.AT_date_debut < '01/01/' + CONVERT(varchar(4), year('01/01/2016')) THEN '01/01/' + CONVERT(varchar(4),
year('01/01/2016')) ELSE TAT.AT_date_debut END), (CASE WHEN TAT.AT_date_reprise > '31/12/' + CONVERT(varchar(4),
year('01/01/2016')) THEN DATEADD(day, 1, '31/12/' + CONVERT(varchar(4), year('01/01/2016'))) ELSE TAT.AT_date_reprise END))
AS CNBJA_SOCIETE_TRAVAIL, nom, AT_date_debut, AT_date_reprise
FROM Taccident_travail AS AT INNER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id
WHERE (P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42))
AND (TAT.AT_date_reprise >= '01/01/2016' AND TAT.AT_date_debut <= '31/12/2016') AND (AT.AT_type_AT = 1) AND (AT.id_AT_contrat <> 30)
Si tu fais un DATEADD au THEN, il faut que tu fasses de même au ELSE ... D'où le fait que j'ai mis +1 au DATEDIFF dans ma proposition et retiré complètement le DATEADD ...
Kadoow a écrit:
ton CASE ne gère pas cette condition j'ai l'impression
Pourtant si ... Mon CASE est le suivant :
CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2017' THEN '01/01/2017' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2017' THEN '31/12/2017' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAVAIL,
Si la date de début est inférieure au 01/01/2017 alors on force au 1er janvier sinon on met la date de début.
Si la date de reprise est supérieur au 31/12/2017 alors au force au 31 décembre sinon on met la date de reprise.
Donc si tu mets bien des variables sur 01/01/2017 et 31/12/2017 en fonction des saisies utilisateur, je ne vois pas de souci.
Pour 2017 - Un accident avec arrêt du 25/12/2016 au 27/01/2017 doit faire 26 jours et non 27 c'est pour ça que j'ai mis le DATEADD ici car j'ajoute un jour que pour ce cas là.
Pour 2017 - Un accident avec arrêt du 02/12/2017 au 14/01/2018 doit faire 29 jours
La date début est toujours incluse dans le calcul et la dernière date n'est jamais incluse.
Et je viens de me rendre compte de l'erreur, mon programme n'affiche pas les 366 jours perdus et donc je me retrouve avec 800 et quelques au lieu de 1100 et quelques! Les 366 jours perdus ne sont même pas affichés avec son AT_siege (membres inférieurs inexistant si tu regardes mes screens d'avant)
Un accident avec arrêt du 25/12/2016 au 27/01/2017 doit faire 26 jours et non 27 c'est pour ça que j'ai mis le DATEADD ici car j'ajoute un jour que pour ce cas là
Oui j'ai bien compris, d'où ma proposition de supprimer DATEADD et de faire +1 sur le DATEDIFF total ...
Kadoow a écrit:
je viens de me rendre compte de l'erreur, mon programme n'affiche pas les 366 jours perdus et donc je me retrouve avec 800 et quelques au lieu de 1100 et quelques
Rien compris ... pour 2017, un arrêt commençant le 25/12/2016 et terminant le 15/01/2018 doit compter pour 365 jours ... et selon moi la requête sélectionne cet arrêt et l'affiche ... Je ne comprends pas ce que tu dis "mon programme n'affiche pas les 366 jours" ... A mon avis, à force de bricoler la requête tu as dû casser un truc ...
Rien compris ... pour 2017, un arrêt commençant le 25/12/2016 et terminant le 15/01/2018 doit compter pour 365 jours ... et selon moi la requête sélectionne cet arrêt et l'affiche ... Je ne comprends pas ce que tu dis "mon programme n'affiche pas les 366 jours" ... A mon avis, à force de bricoler la requête tu as dû casser un truc ...
Je faisais mes calculs sur 2016 depuis le début je sais pas pourquoi t'as déboulé sur 2017 ... et il compte 366 jours car c'est une année bissextile c'est pour ça... je prends cette année car j'ai l'exemple de l'accident avec arrêt sur plus d'une année ... Sur 2017 je n'ai pas cet exemple ...
Ta requête ne sort pas les 366 jours prévu ... je n'ai rien "bricolé"
Et pourtant quand elle est "extériorisée" ça sort les bonnes données x) :
EDIT :
Merci pour ton investissement et ton aide Benzouye !
Je faisais mes calculs sur 2016 depuis le début je sais pas pourquoi t'as déboulé sur 2017
Juste parce que tu l'as évoqué :
Kadoow a écrit:
Pour 2017 - Un accident avec arrêt du 25/12/2016 au 27/01/2017 doit faire 26 jours
Kadoow a écrit:
J'ai juste inversé les 2 sous requêtes
Attention ! Tu ne fais que renverser le problème !
Kadoow a écrit:
Ta requête ne sort pas les 366 jours prévu [...] Et pourtant quand elle est "extériorisée" ça sort les bonnes données
Cela signifie que pour le siège "Membres inférieurs" il n'y a pas d'accident enregistrés avec une date sur 2016. La première sous-requête (celle qui compte les accidents) ne remonte pas ce siège.
Pour pallier à cela, il faudrait partir de la liste des sièges et y lier en jointure externe les deux sous-requêtes. On peut alors se passer de la table siege dans les sous-requêtes. Par contre je ne sais pas quel colonne de cette table affiche le libellé attendu. J'ai imaginé qu'elle s'appelait libelle_siege ...
Si l'on reste sur l'année 2016, cela donnerait donc :
SELECT
S.libelle_siege,
COALESCE( CNBJA_INTERIM_TRAVAIL, 0 ) AS NBJA_INTERIM_TRAVAIL,
COALESCE( CNBJA_INTERIM_TRAJET, 0 ) AS NBJA_INTERIM_TRAJET,
COALESCE( CNBJA_SOCIETE_TRAVAIL, 0 ) AS NBJA_SOCIETE_TRAVAIL,
COALESCE( CNBJA_SOCIETE_TRAJET, 0 ) AS NBJA_SOCIETE_TRAJET,
COALESCE( CNBATSOIN_INTERIM_TRAVAIL, 0 ) AS NBATSOIN_INTERIM_TRAVAIL,
COALESCE( CNBATSOIN_INTERIM_TRAJET, 0 ) AS NBATSOIN_INTERIM_TRAJET,
COALESCE( CNBATSOIN_SOCIETE_TRAVAIL, 0 ) AS NBATSOIN_SOCIETE_TRAVAIL,
COALESCE( CNBATSOIN_SOCIETE_TRAJET, 0 ) AS NBATSOIN_SOCIETE_TRAJET,
COALESCE( CNBATSA_INTERIM_TRAVAIL, 0 ) AS NBATSA_INTERIM_TRAVAIL,
COALESCE( CNBATSA_INTERIM_TRAJET, 0 ) AS NBATSA_INTERIM_TRAJET,
COALESCE( CNBATSA_SOCIETE_TRAVAIL, 0 ) AS NBATSA_SOCIETE_TRAVAIL,
COALESCE( CNBATSA_SOCIETE_TRAJET, 0 ) AS NBATSA_SOCIETE_TRAJET,
COALESCE( CNBATAA_INTERIM_TRAVAIL, 0 ) AS NBATAA_INTERIM_TRAVAIL,
COALESCE( CNBATAA_INTERIM_TRAJET, 0 ) AS NBATAA_INTERIM_TRAJET,
COALESCE( CNBATAA_SOCIETE_TRAVAIL, 0 ) AS NBATAA_SOCIETE_TRAVAIL,
COALESCE( CNBATAA_SOCIETE_TRAJET, 0 ) AS NBATAA_SOCIETE_TRAJET,
COALESCE( CNBATDC_INTERIM_TRAVAIL, 0 ) AS NBATDC_INTERIM_TRAVAIL,
COALESCE( CNBATDC_INTERIM_TRAJET, 0 ) AS NBATDC_INTERIM_TRAJET,
COALESCE( CNBATDC_SOCIETE_TRAVAIL, 0 ) AS NBATDC_SOCIETE_TRAVAIL,
COALESCE( CNBATDC_SOCIETE_TRAJET, 0 ) AS NBATDC_SOCIETE_TRAJET,
COALESCE( CNBAT_INTERIM_TRAVAIL, 0 ) AS NBAT_INTERIM_TRAVAIL,
COALESCE( CNBAT_INTERIM_TRAJET, 0 ) AS NBAT_INTERIM_TRAJET,
COALESCE( CNBAT_SOCIETE_TRAVAIL, 0 ) AS NBAT_SOCIETE_TRAVAIL,
COALESCE( CNBAT_SOCIETE_TRAJET, 0 ) AS NBAT_SOCIETE_TRAJET
FROM
T1liste_AT_siege AS S
LEFT JOIN (
SELECT
AT.id_AT_siege,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
AND AT.AT_date >= '01/01/2016'
AND AT.AT_date <= '31/12/2016'
GROUP BY AT.id_AT_siege
) AS N
ON S.id_siege = N.id_AT_siege
LEFT JOIN (
SELECT
AT.id_AT_siege,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAJET,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/2016'
AND TAT.AT_date_debut <= '31/12/2016'
AND P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
GROUP BY AT.id_AT_siege
) AS J
ON S.id_siege = J.id_AT_siege
Je reviens vers toi concernant ta solution qui fonctionne du tonnerre mais je me retrouve avec des lignes complètement vide, tu n'as pas une méthode pour "cacher" en SQL ?
Et oui ... si un siège de lésion n'a ni accident ni arrêt sur la période ... il est listé quand même ...
Pour s'affranchir de ces lignes "vides", il faudrait encapsuler tout ça dans une requête et faire une clause WHERE ...
SELECT *
FROM (
SELECT
S.libelle_siege,
COALESCE( CNBJA_INTERIM_TRAVAIL, 0 ) AS NBJA_INTERIM_TRAVAIL,
COALESCE( CNBJA_INTERIM_TRAJET, 0 ) AS NBJA_INTERIM_TRAJET,
COALESCE( CNBJA_SOCIETE_TRAVAIL, 0 ) AS NBJA_SOCIETE_TRAVAIL,
COALESCE( CNBJA_SOCIETE_TRAJET, 0 ) AS NBJA_SOCIETE_TRAJET,
COALESCE( CNBATSOIN_INTERIM_TRAVAIL, 0 ) AS NBATSOIN_INTERIM_TRAVAIL,
COALESCE( CNBATSOIN_INTERIM_TRAJET, 0 ) AS NBATSOIN_INTERIM_TRAJET,
COALESCE( CNBATSOIN_SOCIETE_TRAVAIL, 0 ) AS NBATSOIN_SOCIETE_TRAVAIL,
COALESCE( CNBATSOIN_SOCIETE_TRAJET, 0 ) AS NBATSOIN_SOCIETE_TRAJET,
COALESCE( CNBATSA_INTERIM_TRAVAIL, 0 ) AS NBATSA_INTERIM_TRAVAIL,
COALESCE( CNBATSA_INTERIM_TRAJET, 0 ) AS NBATSA_INTERIM_TRAJET,
COALESCE( CNBATSA_SOCIETE_TRAVAIL, 0 ) AS NBATSA_SOCIETE_TRAVAIL,
COALESCE( CNBATSA_SOCIETE_TRAJET, 0 ) AS NBATSA_SOCIETE_TRAJET,
COALESCE( CNBATAA_INTERIM_TRAVAIL, 0 ) AS NBATAA_INTERIM_TRAVAIL,
COALESCE( CNBATAA_INTERIM_TRAJET, 0 ) AS NBATAA_INTERIM_TRAJET,
COALESCE( CNBATAA_SOCIETE_TRAVAIL, 0 ) AS NBATAA_SOCIETE_TRAVAIL,
COALESCE( CNBATAA_SOCIETE_TRAJET, 0 ) AS NBATAA_SOCIETE_TRAJET,
COALESCE( CNBATDC_INTERIM_TRAVAIL, 0 ) AS NBATDC_INTERIM_TRAVAIL,
COALESCE( CNBATDC_INTERIM_TRAJET, 0 ) AS NBATDC_INTERIM_TRAJET,
COALESCE( CNBATDC_SOCIETE_TRAVAIL, 0 ) AS NBATDC_SOCIETE_TRAVAIL,
COALESCE( CNBATDC_SOCIETE_TRAJET, 0 ) AS NBATDC_SOCIETE_TRAJET,
COALESCE( CNBAT_INTERIM_TRAVAIL, 0 ) AS NBAT_INTERIM_TRAVAIL,
COALESCE( CNBAT_INTERIM_TRAJET, 0 ) AS NBAT_INTERIM_TRAJET,
COALESCE( CNBAT_SOCIETE_TRAVAIL, 0 ) AS NBAT_SOCIETE_TRAVAIL,
COALESCE( CNBAT_SOCIETE_TRAJET, 0 ) AS NBAT_SOCIETE_TRAJET
FROM
T1liste_AT_siege AS S
LEFT JOIN (
SELECT
AT.id_AT_siege,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END ) AS CNBATSA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END ) AS CNBATAA_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END ) AS CNBATDC_SOCIETE_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_INTERIM_TRAJET,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAVAIL,
SUM( CASE WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0 THEN 1 ELSE 0 END ) AS CNBAT_SOCIETE_TRAJET
FROM
Taccident_travail AS AT
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
AND AT.AT_date >= '01/01/2016'
AND AT.AT_date <= '31/12/2016'
GROUP BY AT.id_AT_siege
) AS N
ON S.id_siege = N.id_AT_siege
LEFT JOIN (
SELECT
AT.id_AT_siege,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat <> 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_SOCIETE_TRAJET,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 1
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0 END AS CNBJA_INTERIM_TRAVAIL,
SUM( CASE
WHEN AT.id_AT_contrat = 30 AND AT.AT_type_AT = 0
THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END
)+1
ELSE 0
END ) AS CNBJA_INTERIM_TRAJET
FROM
TAT_arret AS TAT
INNER JOIN Taccident_travail AS AT
ON AT.id_accident_travail = TAT.id_AT
INNER JOIN Tpersonnel AS P
ON AT.id_nom = P.id
WHERE
TAT.AT_date_reprise >= '01/01/2016'
AND TAT.AT_date_debut <= '31/12/2016'
AND P.id_etablissement IN (3,4,5,7,9,10,12,13,14,15,17,18,19,20,22,28,30,38,43,49,57,8,11,16,21,23,24,27,47,48,50,51,6,29,41,42)
GROUP BY AT.id_AT_siege
) AS J
ON S.id_siege = J.id_AT_siege
) AS T
WHERE (
NBJA_INTERIM_TRAVAIL+
NBJA_INTERIM_TRAJET+
NBJA_SOCIETE_TRAVAIL+
NBJA_SOCIETE_TRAJET+
NBATSOIN_INTERIM_TRAVAIL+
NBATSOIN_INTERIM_TRAJET+
NBATSOIN_SOCIETE_TRAVAIL+
NBATSOIN_SOCIETE_TRAJET+
NBATSA_INTERIM_TRAVAIL+
NBATSA_INTERIM_TRAJET+
NBATSA_SOCIETE_TRAVAIL+
NBATSA_SOCIETE_TRAJET+
NBATAA_INTERIM_TRAVAIL+
NBATAA_INTERIM_TRAJET+
NBATAA_SOCIETE_TRAVAIL+
NBATAA_SOCIETE_TRAJET+
NBATDC_INTERIM_TRAVAIL+
NBATDC_INTERIM_TRAJET+
NBATDC_SOCIETE_TRAVAIL+
NBATDC_SOCIETE_TRAJET+
NBAT_INTERIM_TRAVAIL+
NBAT_INTERIM_TRAJET+
NBAT_SOCIETE_TRAVAIL+
NBAT_SOCIETE_TRAJET
) > 0
SELECT *
FROM(
SELECT TAG.agence, TAG.societe, TAG.id_SO_societe,
COALESCE(T1.CNBATSOIN_INTERIM_ATELIER , 0) AS NBATSOIN_INTERIM_ATELIER,
COALESCE(T1.CNBATSOIN_INTERIM_BUREAU , 0) AS NBATSOIN_INTERIM_BUREAU,
COALESCE(T1.CNBATSOIN_INTERIM_DEPOT , 0) AS NBATSOIN_INTERIM_DEPOT,
COALESCE(T1.CNBATSOIN_INTERIM_CHANTIER , 0) AS NBATSOIN_INTERIM_CHANTIER,
COALESCE(T1.CNBATSOIN_INTERIM_CIRCULATION , 0) AS NBATSOIN_INTERIM_CIRCULATION,
COALESCE(T1.CNBATSOIN_INTERIM_TRAJET , 0) AS NBATSOIN_INTERIM_TRAJET,
COALESCE(T1.CNBATSOIN_INTERIM_INCONNU , 0) AS NBATSOIN_INTERIM_INCONNU,
COALESCE(T1.CNBATSOIN_INTERIM_GALVANISATION , 0) AS NBATSOIN_INTERIM_GALVANISATION,
COALESCE(T1.CNBATSOIN_INTERIM_FABRICATION , 0) AS NBATSOIN_INTERIM_FABRICATION,
COALESCE(T1.CNBATSOIN_INTERIM_MISSION , 0) AS NBATSOIN_INTERIM_MISSION,
COALESCE(T1.CNBATSOIN_SOCIETE_ATELIER , 0) AS NBATSOIN_SOCIETE_ATELIER,
COALESCE(T1.CNBATSOIN_SOCIETE_BUREAU , 0) AS NBATSOIN_SOCIETE_BUREAU,
COALESCE(T1.CNBATSOIN_SOCIETE_DEPOT , 0) AS NBATSOIN_SOCIETE_DEPOT,
COALESCE(T1.CNBATSOIN_SOCIETE_CHANTIER , 0) AS NBATSOIN_SOCIETE_CHANTIER,
COALESCE(T1.CNBATSOIN_SOCIETE_CIRCULATION , 0) AS NBATSOIN_SOCIETE_CIRCULATION,
COALESCE(T1.CNBATSOIN_SOCIETE_TRAJET , 0) AS NBATSOIN_SOCIETE_TRAJET,
COALESCE(T1.CNBATSOIN_SOCIETE_INCONNU , 0) AS NBATSOIN_SOCIETE_INCONNU,
COALESCE(T1.CNBATSOIN_SOCIETE_GALVANISATION , 0) AS NBATSOIN_SOCIETE_GALVANISATION,
COALESCE(T1.CNBATSOIN_SOCIETE_FABRICATION , 0) AS NBATSOIN_SOCIETE_FABRICATION,
COALESCE(T1.CNBATSOIN_SOCIETE_MISSION , 0) AS NBATSOIN_SOCIETE_MISSION,
COALESCE(T1.CNBATSA_INTERIM_ATELIER , 0) AS NBATSA_INTERIM_ATELIER,
COALESCE(T1.CNBATSA_INTERIM_BUREAU , 0) AS NBATSA_INTERIM_BUREAU,
COALESCE(T1.CNBATSA_INTERIM_DEPOT , 0) AS NBATSA_INTERIM_DEPOT,
COALESCE(T1.CNBATSA_INTERIM_CHANTIER , 0) AS NBATSA_INTERIM_CHANTIER,
COALESCE(T1.CNBATSA_INTERIM_CIRCULATION , 0) AS NBATSA_INTERIM_CIRCULATION,
COALESCE(T1.CNBATSA_INTERIM_TRAJET , 0) AS NBATSA_INTERIM_TRAJET,
COALESCE(T1.CNBATSA_INTERIM_INCONNU , 0) AS NBATSA_INTERIM_INCONNU,
COALESCE(T1.CNBATSA_INTERIM_GALVANISATION , 0) AS NBATSA_INTERIM_GALVANISATION,
COALESCE(T1.CNBATSA_INTERIM_FABRICATION , 0) AS NBATSA_INTERIM_FABRICATION,
COALESCE(T1.CNBATSA_INTERIM_MISSION , 0) AS NBATSA_INTERIM_MISSION,
COALESCE(T1.CNBATSA_SOCIETE_ATELIER , 0) AS NBATSA_SOCIETE_ATELIER,
COALESCE(T1.CNBATSA_SOCIETE_BUREAU , 0) AS NBATSA_SOCIETE_BUREAU,
COALESCE(T1.CNBATSA_SOCIETE_DEPOT, 0) AS NBATSA_SOCIETE_DEPOT,
COALESCE(T1.CNBATSA_SOCIETE_CHANTIER , 0) AS NBATSA_SOCIETE_CHANTIER,
COALESCE(T1.CNBATSA_SOCIETE_CIRCULATION, 0) AS NBATSA_SOCIETE_CIRCULATION,
COALESCE(T1.CNBATSA_SOCIETE_TRAJET , 0) AS NBATSA_SOCIETE_TRAJET,
COALESCE(T1.CNBATSA_SOCIETE_INCONNU, 0) AS NBATSA_SOCIETE_INCONNU,
COALESCE(T1.CNBATSA_SOCIETE_GALVANISATION , 0) AS NBATSA_SOCIETE_GALVANISATION,
COALESCE(T1.CNBATSA_SOCIETE_FABRICATION, 0) AS NBATSA_SOCIETE_FABRICATION,
COALESCE(T1.CNBATSA_SOCIETE_MISSION , 0) AS NBATSA_SOCIETE_MISSION,
COALESCE(T1.CNBATAA_INTERIM_ATELIER, 0) AS NBATAA_INTERIM_ATELIER,
COALESCE(T1.CNBATAA_INTERIM_BUREAU , 0) AS NBATAA_INTERIM_BUREAU,
COALESCE(T1.CNBATAA_INTERIM_DEPOT , 0) AS NBATAA_INTERIM_DEPOT,
COALESCE(T1.CNBATAA_INTERIM_CHANTIER , 0) AS NBATAA_INTERIM_CHANTIER,
COALESCE(T1.CNBATAA_INTERIM_CIRCULATION, 0) AS NBATAA_INTERIM_CIRCULATION,
COALESCE(T1.CNBATAA_INTERIM_TRAJET , 0) AS NBATAA_INTERIM_TRAJET,
COALESCE(T1.CNBATAA_INTERIM_INCONNU, 0) AS NBATAA_INTERIM_INCONNU,
COALESCE(T1.CNBATAA_INTERIM_GALVANISATION , 0) AS NBATAA_INTERIM_GALVANISATION,
COALESCE(T1.CNBATAA_INTERIM_FABRICATION, 0) AS NBATAA_INTERIM_FABRICATION,
COALESCE(T1.CNBATAA_INTERIM_MISSION , 0) AS NBATAA_INTERIM_MISSION,
COALESCE(T1.CNBATAA_SOCIETE_ATELIER, 0) AS NBATAA_SOCIETE_ATELIER,
COALESCE(T1.CNBATAA_SOCIETE_BUREAU , 0) AS NBATAA_SOCIETE_BUREAU,
COALESCE(T1.CNBATAA_SOCIETE_DEPOT, 0) AS NBATAA_SOCIETE_DEPOT,
COALESCE(T1.CNBATAA_SOCIETE_CHANTIER , 0) AS NBATAA_SOCIETE_CHANTIER,
COALESCE(T1.CNBATAA_SOCIETE_CIRCULATION , 0) AS NBATAA_SOCIETE_CIRCULATION,
COALESCE(T1.CNBATAA_SOCIETE_TRAJET , 0) AS NBATAA_SOCIETE_TRAJET,
COALESCE(T1.CNBATAA_SOCIETE_INCONNU, 0) AS NBATAA_SOCIETE_INCONNU,
COALESCE(T1.CNBATAA_SOCIETE_GALVANISATION , 0) AS NBATAA_SOCIETE_GALVANISATION,
COALESCE(T1.CNBATAA_SOCIETE_FABRICATION, 0) AS NBATAA_SOCIETE_FABRICATION,
COALESCE(T1.CNBATAA_SOCIETE_MISSION , 0) AS NBATAA_SOCIETE_MISSION,
COALESCE(T1.CNBATDC_INTERIM_ATELIER, 0) AS NBATDC_INTERIM_ATELIER,
COALESCE(T1.CNBATDC_INTERIM_BUREAU , 0) AS NBATDC_INTERIM_BUREAU,
COALESCE(T1.CNBATDC_INTERIM_DEPOT, 0) AS NBATDC_INTERIM_DEPOT,
COALESCE(T1.CNBATDC_INTERIM_CHANTIER , 0) AS NBATDC_INTERIM_CHANTIER,
COALESCE(T1.CNBATDC_INTERIM_CIRCULATION, 0) AS NBATDC_INTERIM_CIRCULATION,
COALESCE(T1.CNBATDC_INTERIM_TRAJET , 0) AS NBATDC_INTERIM_TRAJET,
COALESCE(T1.CNBATDC_INTERIM_INCONNU, 0) AS NBATDC_INTERIM_INCONNU,
COALESCE(T1.CNBATDC_INTERIM_GALVANISATION , 0) AS NBATDC_INTERIM_GALVANISATION,
COALESCE(T1.CNBATDC_INTERIM_FABRICATION, 0) AS NBATDC_INTERIM_FABRICATION,
COALESCE(T1.CNBATDC_INTERIM_MISSION , 0) AS NBATDC_INTERIM_MISSION,
COALESCE(T1.CNBATDC_SOCIETE_ATELIER, 0) AS NBATDC_SOCIETE_ATELIER,
COALESCE(T1.CNBATDC_SOCIETE_BUREAU , 0) AS NBATDC_SOCIETE_BUREAU,
COALESCE(T1.CNBATDC_SOCIETE_DEPOT, 0) AS NBATDC_SOCIETE_DEPOT,
COALESCE(T1.CNBATDC_SOCIETE_CHANTIER , 0) AS NBATDC_SOCIETE_CHANTIER,
COALESCE(T1.CNBATDC_SOCIETE_CIRCULATION, 0) AS NBATDC_SOCIETE_CIRCULATION,
COALESCE(T1.CNBATDC_SOCIETE_TRAJET , 0) AS NBATDC_SOCIETE_TRAJET,
COALESCE(T1.CNBATDC_SOCIETE_INCONNU, 0) AS NBATDC_SOCIETE_INCONNU,
COALESCE(T1.CNBATDC_SOCIETE_GALVANISATION , 0) AS NBATDC_SOCIETE_GALVANISATION,
COALESCE(T1.CNBATDC_SOCIETE_FABRICATION , 0) AS NBATDC_SOCIETE_FABRICATION,
COALESCE(T1.CNBATDC_SOCIETE_MISSION , 0) AS NBATDC_SOCIETE_MISSION,
COALESCE(T2.CNBJA_INTERIM_ATELIER , 0) AS NBJA_INTERIM_ATELIER,
COALESCE(T2.CNBJA_INTERIM_BUREAU , 0) AS NBJA_INTERIM_BUREAU,
COALESCE(T2.CNBJA_INTERIM_DEPOT , 0) AS NBJA_INTERIM_DEPOT,
COALESCE(T2.CNBJA_INTERIM_CHANTIER , 0) AS NBJA_INTERIM_CHANTIER,
COALESCE(T2.CNBJA_INTERIM_CIRCULATION , 0) AS NBJA_INTERIM_CIRCULATION,
COALESCE(T2.CNBJA_INTERIM_TRAJET , 0) AS NBJA_INTERIM_TRAJET,
COALESCE(T2.CNBJA_INTERIM_INCONNU , 0) AS NBJA_INTERIM_INCONNU,
COALESCE(T2.CNBJA_INTERIM_GALVANISATION , 0) AS NBJA_INTERIM_GALVANISATION,
COALESCE(T2.CNBJA_INTERIM_FABRICATION , 0) AS NBJA_INTERIM_FABRICATION,
COALESCE(T2.CNBJA_INTERIM_MISSION , 0) AS NBJA_INTERIM_MISSION,
COALESCE(T2.CNBJA_SOCIETE_ATELIER , 0) AS NBJA_SOCIETE_ATELIER,
COALESCE(T2.CNBJA_SOCIETE_BUREAU , 0) AS NBJA_SOCIETE_BUREAU,
COALESCE(T2.CNBJA_SOCIETE_DEPOT , 0) AS NBJA_SOCIETE_DEPOT,
COALESCE(T2.CNBJA_SOCIETE_CHANTIER , 0) AS NBJA_SOCIETE_CHANTIER,
COALESCE(T2.CNBJA_SOCIETE_CIRCULATION , 0) AS NBJA_SOCIETE_CIRCULATION,
COALESCE(T2.CNBJA_SOCIETE_TRAJET , 0) AS NBJA_SOCIETE_TRAJET,
COALESCE(T2.CNBJA_SOCIETE_INCONNU , 0) AS NBJA_SOCIETE_INCONNU,
COALESCE(T2.CNBJA_SOCIETE_GALVANISATION , 0) AS NBJA_SOCIETE_GALVANISATION,
COALESCE(T2.CNBJA_SOCIETE_FABRICATION , 0) AS NBJA_SOCIETE_FABRICATION,
COALESCE(T2.CNBJA_SOCIETE_MISSION , 0) AS NBJA_SOCIETE_MISSION,
COALESCE(T1.CNBAT_INTERIM_ATELIER , 0) AS NBAT_INTERIM_ATELIER,
COALESCE(T1.CNBAT_INTERIM_BUREAU , 0) AS NBAT_INTERIM_BUREAU,
COALESCE(T1.CNBAT_INTERIM_DEPOT , 0) AS NBAT_INTERIM_DEPOT,
COALESCE(T1.CNBAT_INTERIM_CHANTIER , 0) AS NBAT_INTERIM_CHANTIER,
COALESCE(T1.CNBAT_INTERIM_CIRCULATION , 0) AS NBAT_INTERIM_CIRCULATION,
COALESCE(T1.CNBAT_INTERIM_TRAJET , 0) AS NBAT_INTERIM_TRAJET,
COALESCE(T1.CNBAT_INTERIM_INCONNU , 0) AS NBAT_INTERIM_INCONNU,
COALESCE(T1.CNBAT_INTERIM_GALVANISATION , 0) AS NBAT_INTERIM_GALVANISATION,
COALESCE(T1.CNBAT_INTERIM_FABRICATION , 0) AS NBAT_INTERIM_FABRICATION,
COALESCE(T1.CNBAT_INTERIM_MISSION , 0) AS NBAT_INTERIM_MISSION,
COALESCE(T1.CNBAT_SOCIETE_ATELIER , 0) AS NBAT_SOCIETE_ATELIER,
COALESCE(T1.CNBAT_SOCIETE_BUREAU , 0) AS NBAT_SOCIETE_BUREAU,
COALESCE(T1.CNBAT_SOCIETE_DEPOT , 0) AS NBAT_SOCIETE_DEPOT,
COALESCE(T1.CNBAT_SOCIETE_CHANTIER , 0) AS NBAT_SOCIETE_CHANTIER,
COALESCE(T1.CNBAT_SOCIETE_CIRCULATION , 0) AS NBAT_SOCIETE_CIRCULATION,
COALESCE(T1.CNBAT_SOCIETE_TRAJET , 0) AS NBAT_SOCIETE_TRAJET,
COALESCE(T1.CNBAT_SOCIETE_INCONNU , 0) AS NBAT_SOCIETE_INCONNU,
COALESCE(T1.CNBAT_SOCIETE_GALVANISATION , 0) AS NBAT_SOCIETE_GALVANISATION,
COALESCE(T1.CNBAT_SOCIETE_FABRICATION , 0) AS NBAT_SOCIETE_FABRICATION,
COALESCE(T1.CNBAT_SOCIETE_MISSION , 0) AS NBAT_SOCIETE_MISSION
FROM
(SELECT id_agence, id_SO_societe, /*CENSURE*/ AS societe
FROM Tagence
WHERE (pour_AT_stat = 1)) AS TAG
LEFT JOIN (SELECT P.id_etablissement AS id_etab,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END ) AS CNBATSOIN_INTERIM_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_INTERIM_MISSION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 40 THEN 1 ELSE 0 END) AS CNBATSOIN_SOCIETE_MISSION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_INTERIM_MISSION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 30 THEN 1 ELSE 0 END) AS CNBATSA_SOCIETE_MISSION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_INTERIM_MISSION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 20 THEN 1 ELSE 0 END) AS CNBATAA_SOCIETE_MISSION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_INTERIM_MISSION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 5 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 10 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 20 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 30 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 40 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 50 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 60 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 70 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 80 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 90 AND AT.id_AT_accident = 10 THEN 1 ELSE 0 END) AS CNBATDC_SOCIETE_MISSION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 5 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 10 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 20 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 30 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 40 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 50 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 60 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 70 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 80 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat = 30 AND AT.id_AT_lieu = 90 THEN 1 ELSE 0 END) AS CNBAT_INTERIM_MISSION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 5 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_ATELIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 10 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_BUREAU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 20 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_DEPOT,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 30 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_CHANTIER,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 40 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_CIRCULATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 50 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_TRAJET,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 60 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_INCONNU,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 70 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_GALVANISATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 80 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_FABRICATION,
SUM(CASE WHEN AT.id_AT_contrat <> 30 AND AT.id_AT_lieu = 90 THEN 1 ELSE 0 END) AS CNBAT_SOCIETE_MISSION
FROM Taccident_travail AS AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id INNER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT
WHERE (P.id_etablissement IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,38,41,42,43,47,48,49,50,51,57)) AND
(AT.AT_date >= '01/01/2016') AND (AT.AT_date <= '31/12/2016')
GROUP BY P.id_etablissement)
AS T1 ON TAG.id_agence = T1.id_etab LEFT JOIN
(SELECT P.id_etablissement AS id_etab,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 5) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_ATELIER,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 10) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_BUREAU,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND(AT.id_AT_lieu = 20) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_DEPOT,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 30) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_CHANTIER,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 40) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_CIRCULATION,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 50) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_TRAJET,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 60) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_INCONNU,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 70) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_GALVANISATION,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 80) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_FABRICATION,
SUM(CASE WHEN (AT.id_AT_contrat <> 30) AND (AT.id_AT_lieu = 90) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_SOCIETE_MISSION,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 5) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_ATELIER,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 10) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_BUREAU,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 20) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_DEPOT,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 30) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_CHANTIER,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 40) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_CIRCULATION,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 50) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_TRAJET,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 60) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_INCONNU,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 70) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_GALVANISATION,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 80) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_FABRICATION,
SUM(CASE WHEN (AT.id_AT_contrat = 30) AND (AT.id_AT_lieu = 90) THEN DATEDIFF( day,
CASE WHEN TAT.AT_date_debut < '01/01/2016' THEN '01/01/2016' ELSE TAT.AT_date_debut END,
CASE WHEN TAT.AT_date_reprise > '31/12/2016' THEN '31/12/2016' ELSE TAT.AT_date_reprise END)
ELSE 0
END) AS CNBJA_INTERIM_MISSION
FROM Taccident_travail AS AT INNER JOIN
Tpersonnel AS P ON AT.id_nom = P.id INNER JOIN
TAT_arret AS TAT ON AT.id_accident_travail = TAT.id_AT
WHERE (P.id_etablissement IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,38,41,42,43,47,48,49,50,51,57)) AND
(TAT.AT_date_reprise >= '01/01/2016') AND (TAT.AT_date_debut <= '31/12/2016')
GROUP BY P.id_etablissement) AS T2 ON TAG.id_agence = T2.id_etab
) AS T --WHERE (@id_AG = @id_AG)
WHERE (NBATSOIN_INTERIM_ATELIER+
NBATSOIN_INTERIM_BUREAU+
NBATSOIN_INTERIM_DEPOT+
NBATSOIN_INTERIM_CHANTIER+
NBATSOIN_INTERIM_CIRCULATION+
NBATSOIN_INTERIM_TRAJET+
NBATSOIN_INTERIM_INCONNU+
NBATSOIN_INTERIM_GALVANISATION+
NBATSOIN_INTERIM_FABRICATION+
NBATSOIN_INTERIM_MISSION+
NBATSOIN_SOCIETE_ATELIER+
NBATSOIN_SOCIETE_BUREAU+
NBATSOIN_SOCIETE_DEPOT+
NBATSOIN_SOCIETE_CHANTIER+
NBATSOIN_SOCIETE_CIRCULATION+
NBATSOIN_SOCIETE_TRAJET+
NBATSOIN_SOCIETE_INCONNU+
NBATSOIN_SOCIETE_GALVANISATION+
NBATSOIN_SOCIETE_FABRICATION+
NBATSOIN_SOCIETE_MISSION+
NBATSA_INTERIM_ATELIER+
NBATSA_INTERIM_BUREAU+
NBATSA_INTERIM_DEPOT+
NBATSA_INTERIM_CHANTIER+
NBATSA_INTERIM_CIRCULATION+
NBATSA_INTERIM_TRAJET+
NBATSA_INTERIM_INCONNU+
NBATSA_INTERIM_GALVANISATION+
NBATSA_INTERIM_FABRICATION+
NBATSA_INTERIM_MISSION+
NBATSA_SOCIETE_ATELIER+
NBATSA_SOCIETE_BUREAU+
NBATSA_SOCIETE_DEPOT+
NBATSA_SOCIETE_CHANTIER+
NBATSA_SOCIETE_CIRCULATION+
NBATSA_SOCIETE_TRAJET+
NBATSA_SOCIETE_INCONNU+
NBATSA_SOCIETE_GALVANISATION+
NBATSA_SOCIETE_FABRICATION+
NBATSA_SOCIETE_MISSION+
NBATAA_INTERIM_ATELIER+
NBATAA_INTERIM_BUREAU+
NBATAA_INTERIM_DEPOT+
NBATAA_INTERIM_CHANTIER+
NBATAA_INTERIM_CIRCULATION+
NBATAA_INTERIM_TRAJET+
NBATAA_INTERIM_INCONNU+
NBATAA_INTERIM_GALVANISATION+
NBATAA_INTERIM_FABRICATION+
NBATAA_INTERIM_MISSION+
NBATAA_SOCIETE_ATELIER+
NBATAA_SOCIETE_BUREAU+
NBATAA_SOCIETE_DEPOT+
NBATAA_SOCIETE_CHANTIER+
NBATAA_SOCIETE_CIRCULATION+
NBATAA_SOCIETE_TRAJET+
NBATAA_SOCIETE_INCONNU+
NBATAA_SOCIETE_GALVANISATION+
NBATAA_SOCIETE_FABRICATION+
NBATAA_SOCIETE_MISSION+
NBATDC_INTERIM_ATELIER+
NBATDC_INTERIM_BUREAU+
NBATDC_INTERIM_DEPOT+
NBATDC_INTERIM_CHANTIER+
NBATDC_INTERIM_CIRCULATION+
NBATDC_INTERIM_TRAJET+
NBATDC_INTERIM_INCONNU+
NBATDC_INTERIM_GALVANISATION+
NBATDC_INTERIM_FABRICATION+
NBATDC_INTERIM_MISSION+
NBATDC_SOCIETE_ATELIER+
NBATDC_SOCIETE_BUREAU+
NBATDC_SOCIETE_DEPOT+
NBATDC_SOCIETE_CHANTIER+
NBATDC_SOCIETE_CIRCULATION+
NBATDC_SOCIETE_TRAJET+
NBATDC_SOCIETE_INCONNU+
NBATDC_SOCIETE_GALVANISATION+
NBATDC_SOCIETE_FABRICATION+
NBATDC_SOCIETE_MISSION+
NBJA_INTERIM_ATELIER+
NBJA_INTERIM_BUREAU+
NBJA_INTERIM_DEPOT+
NBJA_INTERIM_CHANTIER+
NBJA_INTERIM_CIRCULATION+
NBJA_INTERIM_TRAJET+
NBJA_INTERIM_INCONNU+
NBJA_INTERIM_GALVANISATION+
NBJA_INTERIM_FABRICATION+
NBJA_INTERIM_MISSION+
NBJA_SOCIETE_ATELIER+
NBJA_SOCIETE_BUREAU+
NBJA_SOCIETE_DEPOT+
NBJA_SOCIETE_CHANTIER+
NBJA_SOCIETE_CIRCULATION+
NBJA_SOCIETE_TRAJET+
NBJA_SOCIETE_INCONNU+
NBJA_SOCIETE_GALVANISATION+
NBJA_SOCIETE_FABRICATION+
NBJA_SOCIETE_MISSION+
NBAT_INTERIM_ATELIER+
NBAT_INTERIM_BUREAU+
NBAT_INTERIM_DEPOT+
NBAT_INTERIM_CHANTIER+
NBAT_INTERIM_CIRCULATION+
NBAT_INTERIM_TRAJET+
NBAT_INTERIM_INCONNU+
NBAT_INTERIM_GALVANISATION+
NBAT_INTERIM_FABRICATION+
NBAT_INTERIM_MISSION+
NBAT_SOCIETE_ATELIER+
NBAT_SOCIETE_BUREAU+
NBAT_SOCIETE_DEPOT+
NBAT_SOCIETE_CHANTIER+
NBAT_SOCIETE_CIRCULATION+
NBAT_SOCIETE_TRAJET+
NBAT_SOCIETE_INCONNU+
NBAT_SOCIETE_GALVANISATION+
NBAT_SOCIETE_FABRICATION+
NBAT_SOCIETE_MISSION) > 0
J'ai gardé le même format qui marche pour d'autre requêtes mais pas pour celle-ci j'ai aucune idée pourquoi ... toutes les requêtes sont les mêmes ... Je pensais au niveau de la JOINTURE que ça chiait mais l'id_agence (pk ) me semble bon
EDIT 2 : j'ai enlevé la JOINTURE sur TAT_arret qui contient tous els arrêts, on s'en sert pas et les at sans arrêt ne sont pas liés à cette table
- Edité par Kadoow 17 juillet 2019 à 16:30:48
Optimisation de requête
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
Je veux pouvoir faire un tableau pour sortir toutes ces données(acc. sans arrêt, acc. avec arrêt, etc.) selon le siège de lésion (type : bras, jambe, tête, etc.) sauf qu'avec ça j'ai des données bien trop supérieures à la normale. J'imagine que mon JOIN n'est pas bon ...