Discussion :

[facteur]

Une petite précision.
Je ne sais pas si c'est important...
La sous-requête ci-dessous (celle qui pose problème) est à l'origine une sous-requête SQL que j'ai modifiée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 AS c3, 0 AS c4, 0 AS c5, Sum(CounterValue) AS c6, 0 AS c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partid = p.id
 
            WHERE (CounterTypeId = 82) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) AND (CounterTimeStamp<=#4/25/2014 11:0:0#)
 
 
            GROUP BY DisplayName

La ligne 5 est à l'origine écrite ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE( dbo_vwPartCountsHistory.CounterTypeId=83/*OverSizel*/)  AND.....

[al1_24]

Que CounterTypeId passe de 83 à 82 entre les requêtes, c'est une erreur dans le message... ou dans la requête ?

[facteur]

Bonjour Al 1,

Que CounterTypeId passe de 83 à 82 entre les requêtes

n 'est pas une erreur, ce sont deux sous-requêtes différentes : l'une avec le critère 83 et la deuxième avec critère 82.

J'ai apporté des modifications à cette requête complexe : Entre les sous-requêtes, j'ai remplacé l'expression UNION par UNION ALL.
J'ai une légère amélioration .... Sur les deux sous-requêtes qui posaient problèm , je n'en ai plus qui m'affichent un résultat aberrant 1,45207490870997E+257:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
UNION ALL
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, Sum(Countervalue) AS c6, 0 AS c7, 0 AS c8, 0 AS c9
 
           FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 82) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) AND( CounterTimeStamp<=#4/25/2014 11:0:0#)
 
      GROUP BY p.displayname

Voici la requête dans son ensemble :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SELECT DisplayName, Sum(tbl.c1) AS keyboard, Sum(tbl.c2) AS Scanner, Sum(tbl.c3) AS HHScanner, Sum(tbl.c4) AS Stray, Sum(tbl.c5) AS Fragile, Sum(tbl.c6) AS Cylindrical, Sum(tbl.c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c3)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total
 
FROM [SELECT p.DisplayName, count(*) AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
                 FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
 
               where (dbo_vwItemEventHistory.ItemEventTypeID = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND (id.InductionMode='Keyboard')
                 AND id.dischargePartId <> 3645
 
               GROUP BY p.displayname
 
 
 UNION ALL
 
                SELECT p.DisplayName, 0 AS c1, count (*) AS c2, 0 As c3 , 0 AS c4, 0 As c5, 0 AS c6, 0 As c7 ,0 AS c8, 0 AS c9
 
               FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id on dbo_vwItemEventHistory.itemID = id.itemID) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
              where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='scanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.displayname
 
 UNION ALL
 
               SELECT p.Displayname, 0 AS c1, 0 AS c2,  count (*) As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
              FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
               where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='HHScanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.displayname
 
 
 UNION ALL
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 AS c3, count (*) AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
             FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid ) INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='Stray') 
             AND id.dischargePartId <> 3645
 
              GROUP BY p.displayname
 
 
UNION ALL
 
           SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, count (*) As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
           FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid=id.itemid)INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='fragile') 
            AND id.dischargePartId <> 3645
 
            GROUP BY p.displayname
 
 
 
UNION ALL
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, Sum(Countervalue) AS c6, 0 AS c7, 0 AS c8, 0 AS c9
 
           FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 82) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) AND( CounterTimeStamp<=#4/25/2014 11:0:0#)
 
      GROUP BY p.displayname

[facteur]

Bonjour,

J'ai pu arrivé à mes fins. Mais honnêtement , j'ai un peu bidouillé cette sous-requête et je ne sais pas pourquoi elle fonctionne .
J'ai séparé les deux sous-requêtes qui posaient problème et j'ai inséré un UNION ALL à la fin.
Si quelqu'un pouvait m 'expliquer pourquoi ça fonctionne, je suis preneur..

Voici la sous-requête qui fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
SELECT DisplayName, Sum(tbl.c1) AS keyboard, Sum(tbl.c2) AS Scanner, Sum(tbl.c3) AS HHScanner, Sum(tbl.c4) AS Stray, Sum(tbl.c5) AS Fragile, Sum(tbl.c6) AS Cylindrical, Sum(tbl.c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c3)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total
FROM (SELECT p.DisplayName, count(*) AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
 
 
                 FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
 
               where (dbo_vwItemEventHistory.ItemEventTypeID = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND (id.InductionMode='Keyboard')
                 AND id.dischargePartId <> 3645
 
               GROUP BY p.DisplayName
 
UNION 
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, Sum(CounterValue) AS c6, 0 AS c7, 0 AS c8, 0 AS c9
 
           FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 82) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) And (CounterTimeStamp<=#4/25/2014 11:0:0#)
 
      GROUP BY p.DisplayName
 
 UNION 
 
                SELECT p.DisplayName, 0 AS c1, count (*) AS c2, 0 As c3 , 0 AS c4, 0 As c5, 0 AS c6, 0 As c7 ,0 AS c8, 0 AS c9
 
               FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id on dbo_vwItemEventHistory.itemID = id.itemID) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
              where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='scanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.DisplayName
 
 UNION 
 
               SELECT p.DisplayName, 0 AS c1, 0 AS c2,  count (*) As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
              FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
               where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='HHScanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.DisplayName
 
 
 UNION 
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 AS c3, count (*) AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
             FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid ) INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='Stray') 
             AND id.dischargePartId <> 3645
 
              GROUP BY p.DisplayName
 
 
UNION 
 
           SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, count (*) As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
           FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid=id.itemid)INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND ( id.inductionMode='fragile') 
            AND id.dischargePartId <> 3645
 
            GROUP BY p.DisplayName
 
 
UNION  ALL
 
 
              SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, Sum(CounterValue) AS c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 83) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) and (CounterTimeStamp<=#4/25/2014 11:0:0#)
 
  GROUP BY p.DisplayName) tbl
GROUP BY DisplayName
ORDER BY DisplayName;

[aieeeuuuuu]

Par curiosité, quel résultat obtenez vous avec la requête que je vous ai proposée il y a quatre jours ?

[facteur]

J'ai fait un copier coller de votre requête... et elle m'affichait une erreur.
Pour vous répondre, je vais la réécrire en sql directement et je reviens vers vous pour vous informer du résultat.

[facteur]

Bonjour aieuu

J'ai recopié ta requête, mais elle m'affiche un message d'erreur :

erreur de la syntaxe dans la clause FROM

[facteur]

J'ai encore une question à vous poser.

Je dois insérer dans cette requête sql le critère datediff (en minutes) qui correspond à la différence entre [date debut] et [date fin].

Voici la requête sql :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
SELECT tbl.DisplayName, Sum(tbl.c1) AS keyboard, Sum(tbl.c2) AS Scanner, Sum(tbl.c3) AS HHScanner, Sum(tbl.c4) AS Stray, Sum(tbl.c5) AS Fragile, Sum(tbl.c6) AS Cylindrical, Sum(tbl.c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c3)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total
FROM (SELECT p.DisplayName, count(*) AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
 
 
                 FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
 
               where (dbo_vwItemEventHistory.ItemEventTypeID = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND (id.InductionMode='Keyboard')
                 AND id.dischargePartId <> 3645
 
               GROUP BY p.DisplayName
 
UNION 
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, Sum(CounterValue) AS c6, 0 AS c7, 0 AS c8, 0 AS c9
 
           FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 82) AND (CounterTimeStamp>=[date debut]) And (CounterTimeStamp<=[date fin])
 
      GROUP BY p.DisplayName
 
 UNION 
 
                SELECT p.DisplayName, 0 AS c1, count (*) AS c2, 0 As c3 , 0 AS c4, 0 As c5, 0 AS c6, 0 As c7 ,0 AS c8, 0 AS c9
 
               FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id on dbo_vwItemEventHistory.itemID = id.itemID) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
              where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='scanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.DisplayName
 
 UNION 
 
               SELECT p.DisplayName, 0 AS c1, 0 AS c2,  count (*) As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
              FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
               where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='HHScanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.DisplayName
 
 
 UNION 
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 AS c3, count (*) AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
             FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid ) INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='Stray') 
             AND id.dischargePartId <> 3645
 
              GROUP BY p.DisplayName
 
 
UNION 
 
           SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, count (*) As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
           FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid=id.itemid)INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='fragile') 
            AND id.dischargePartId <> 3645
 
            GROUP BY p.DisplayName
 
 
UNION  ALL
 
 
              SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, Sum(CounterValue) AS c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 83) AND (CounterTimeStamp>=[date debut]) and (CounterTimeStamp<=[date fin])
 
  GROUP BY p.DisplayName) tbl
GROUP BY tbl.DisplayName
ORDER BY tbl.DisplayName;

[facteur]

Bonjour,

J'ai enfin réussi à créer ma requête sql..ouf..
Mais j'ai un autre problème ... Je souhaiterais transférer ma requête sql en query et là j'ai un message d'erreur :

[Microsoft][Pilote ODBC Microsoft Access] Trop peu de paramètres. 4 attendu.

J'en aie marre

J'ai encore besoin d 'un coup de main...

Voici ma requête sql

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
PARAMETERS [date debut] DateTime, [date fin] DateTime;
SELECT tbl.DisplayName, Sum(tbl.c1) AS keyboard, Sum(tbl.c2) AS Scanner, Sum(tbl.c3) AS HHScanner, Sum(tbl.c4) AS Stray, Sum(tbl.c5) AS Fragile, Sum(tbl.c6) AS Cylindrical, Sum(tbl.c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c3)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total, CLng(total*60/(DateDiff("n",[date debut],[date fin]))) AS nombre_colis_heure, [date debut] AS date_debut, [date fin] AS date_fin
FROM (SELECT p.DisplayName, count(*) AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
 
 
                 FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
 
               where (dbo_vwItemEventHistory.ItemEventTypeID = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND (id.InductionMode='Keyboard')
                 AND id.dischargePartId <> 3645
 
               GROUP BY p.DisplayName
 
UNION 
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, Sum(CounterValue) AS c6, 0 AS c7, 0 AS c8, 0 AS c9
 
           FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 82) AND (CounterTimeStamp>=[date debut]) And (CounterTimeStamp<=[date fin])
 
      GROUP BY p.DisplayName
 
 UNION 
 
                SELECT p.DisplayName, 0 AS c1, count (*) AS c2, 0 As c3 , 0 AS c4, 0 As c5, 0 AS c6, 0 As c7 ,0 AS c8, 0 AS c9
 
               FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id on dbo_vwItemEventHistory.itemID = id.itemID) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
              where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='scanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.DisplayName
 
 UNION 
 
               SELECT p.DisplayName, 0 AS c1, 0 AS c2,  count (*) As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
              FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
               where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='HHScanner') 
             AND id.dischargePartId <> 3645
 
             GROUP BY p.DisplayName
 
 
 UNION 
 
            SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 AS c3, count (*) AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
             FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid ) INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId = 5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='Stray') 
             AND id.dischargePartId <> 3645
 
              GROUP BY p.DisplayName
 
 
UNION 
 
           SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, count (*) As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
           FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid=id.itemid)INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=[date debut]) And (dbo_vwItemEventHistory.EventTime<=[date fin]) AND ( id.inductionMode='fragile') 
            AND id.dischargePartId <> 3645
 
            GROUP BY p.DisplayName
 
 
UNION  ALL
 
 
              SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, Sum(CounterValue) AS c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partId = p.ID
 
             where (CounterTypeId = 83) AND (CounterTimeStamp>=[date debut]) and (CounterTimeStamp<=[date fin])
 
  GROUP BY p.DisplayName) tbl
GROUP BY tbl.DisplayName
ORDER BY tbl.DisplayName

;