Bonjour tlm,
je voudrais voir s'il y a la possibilité d’optimiser ce code
je saisi qu'il est un peut tiré par les cheveux mais il 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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
Select
final.ID_Invoice,
final.ID_Customer,
final.VIN,
final.Amount,
final.Description,
final.ID_Date,
final.Sommme,
final.ID_Agent,
(Sommme* quantity) as Amount_By_VIN
 
from
 
(SELECT sub.[Document No_] as ID_Invoice,
  sub.[Customer No_] as ID_Customer,
  sub.VIN,
  sub.Amount,
  sub.Description,
  sub.[Posting Date] as ID_Date,
  bit.Sommme,
  sub.[Salesperson Code] as ID_Agent,
 
  CASE 
    WHEN sub.[Document No_] = (SELECT min(b.[Document No_]) FROM dbo.[$Sales Invoice Line] b LEFT JOIN
      (SELECT  bb.[Applies-to Doc_ No_]
      FROM IN_test.dbo.[$Sales Credit Memo Line] aa, dbo.[$Sales Credit Memo Header] bb 
      WHERE aa.[Document No_]=bb.No_ AND 
      aa.[Document No_] like '%vh%'AND 
      aa.[Sell-to Customer No_] <> ''AND
      bb.[Posting Description] like '%avoir%'AND
      aa.Description NOT LIKE '%tva%') d
      ON b.[Document No_]=d.[Applies-to Doc_ No_]
      WHERE
      sub.VIN=b.vin
      AND d.[Applies-to Doc_ No_] IS NULL)   
    THEN '1'
    ELSE '0'
  END AS Quantity
 
 
FROM
------------------------------
(SELECT
  k.VIN,
  sum(k.Amount) as Sommme
  FROM
    dbo.[$Sales Invoice Line] k 
    LEFT JOIN
     (SELECT  h.[Applies-to Doc_ No_]
      FROM IN_test.dbo.[$Sales Credit Memo Line] g, dbo.[$Sales Credit Memo Header] h 
      WHERE g.[Document No_]=h.No_ AND 
      g.[Document No_] like '%vh%'AND 
      g.[Sell-to Customer No_] <> ''AND
      h.[Posting Description] like '%avoir%'AND
      g.Description NOT LIKE '%tva%') dd
    ON k.[Document No_]=dd.[Applies-to Doc_ No_]
 WHERE
 
  dd.[Applies-to Doc_ No_] IS NULL
  AND k.Description NOT LIKE '%TVA%'
  AND k.[Document No_] like '%vh%' 
group by k.VIN) bit,
---------------------------------
 
 
  (SELECT
  a.[Document No_],
  b.[Customer No_],
  a.VIN,
  a.Amount,
  a.Description,
  c.[Posting Date],
  c.[Salesperson Code]
 
 
  FROM
    dbo.[$Sales Invoice Header] c,
    dbo.$Vehicle b,
    dbo.[$Sales Invoice Line] a 
    LEFT JOIN
     (SELECT  m.[Applies-to Doc_ No_]
      FROM IN_test.dbo.[$Sales Credit Memo Line] k, dbo.[$Sales Credit Memo Header] m 
      WHERE k.[Document No_]=m.No_ AND 
      k.[Document No_] like '%vh%'AND 
      k.[Sell-to Customer No_] <> ''AND
      m.[Posting Description] like '%avoir%'AND
      k.Description NOT LIKE '%tva%') dd
    ON a.[Document No_]=dd.[Applies-to Doc_ No_]
 
 
 WHERE
 
 a.VIN  = b.VIN 
 
  AND a.[Document No_]= c.No_
  AND dd.[Applies-to Doc_ No_] IS NULL
  AND a.Description NOT LIKE '%TVA%') SUB 
 
 
WHERE sub.vin= bit.vin
 
GROUP BY
  sub.[Document No_],
  sub.[Customer No_],
  sub.VIN,
  sub.Amount,
  sub.Description,
  sub.[Posting Date],
  sub.[Salesperson Code],
  bit.Sommme) final
Merci pour votre aide