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
| declare @matable TABLE
(
Num varchar(10),
Num_Acpte varchar(20),
Affaire varchar(20),
Region varchar(20),
Agence varchar(20),
Montant_cal decimal(38,2),
Montant_Acpt decimal(38,2),
Montant_HT decimal(38,2),
Montant_TTC decimal(38,2)
)
insert into @matable
(
Num,
Affaire,
Region,
Agence,
Montant_HT,
Montant_TTC
)
SELECT
T1.[Document No_] as "Num",
T1.[N° Affaire] as "Affaire",
T1.[Région] as "Region",
T1.[Responsibility Center] as "Agence",
T1.[Sales (LCY)] as "Montant_HT",
T1.[Montant CATTC] as "Montant_TTC"
FROM
dbo.[BASE$Cust_ Ledger Entry] T1
WHERE T1.[Open] = 1
AND T1.[Document Type] = 2
insert into @matable
(
Num,
Affaire,
Num_Acpte,
Region,
Agence,
Montant_Acpt
)
SELECT
T4.[No_] as "Num",
T4.[Job No_] as "Affaire",
T4.[N° facture Acompte Lié] as "Num_Acpte",
T3.[Région] as "Region",
T3.[Responsibility Center] as "Agence",
T3.[Amount Including VAT] as "Montant_Acpt"
FROM
dbo.[BASE$Sales Invoice Header] T4
inner join
dbo.[BASE$Sales Invoice Line] T3
on T4.[N° facture Acompte Lié] = T3.[Document No_]
WHERE T4.[N° facture Acompte Lié] <> ''
ORDER BY T4.[No_]
select
Num,
max(Num_Acpte) as Num_Acpte,
max(Affaire) as Affaire,
max(Region) as Region,
max(Agence)as Agence,
max(Montant_HT)as Montant_HT,
max(Montant_TTC) as Montant_TTC,
max(Montant_Acpt) as Montant_Acpt,
case(
when Montant_HT is null then '0'
when Montant_HT is not null and Montant_Acpt is null then Montant_HT
when Montant_HT is not null and Montant_Acpt is not null
then Montant_HT - Montant_Acpt
end
) as Montant_cal
from @matable
group by Num |
Partager