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 |
Optimisation code SQL
Répondre avec citation
Partager