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 |
Partager