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
| SELECT T1.ID, T1.Entrée, T1.[Num Client], T1.[Nom Produit], T1.Parts, T1.Prix, T1.[Prix V], T1.[Parts V],
(T1.Parts*T1.Prix*T1.[Change E]) AS MontantAchat,
(T1.[Parts V]*T1.[Prix V]*T1.[Change E]) AS MontantVente,
IIf(T1.Parts=0,0,sum(T2.Parts*T2.Prix)/sum(T2.Parts)) AS Pamp,
(T1.[Notre Commission]+T1.[Droit de Timbre]+T1.[Commission Frais de Tiers]+T1.[Commission d'émission]+T1.[Taxe Bourse et CFB]+T1.[Commission Fiduciaire]+T1.DDE+T1.[Valeur Intérêts]+T1.UBS+T1.BSI+T1.CSM+T1.[Commission Courtage])*T1.[Change E] AS FraisAchat,
(T1.[Notre Commission]+T1.[Droit de Timbre]+T1.[Commission Frais de Tiers]+T1.[Commission d'émission]+T1.[Taxe Bourse et CFB]+T1.[Commission Fiduciaire]+T1.DDE-T1.[Valeur Intérêts]+T1.UBS+T1.BSI+T1.CSM+T1.[Commission Courtage])*T1.[Change E] AS FraisVente,
IIf(T1.Parts=0,0,(T1.Prix+(FraisAchat/T1.Parts))) AS PrixAchatNet,
IIf(T1.[Parts V]=0,0,(T1.[Prix V]-(FraisVente/T1.[Parts V]))) AS PrixVenteNet,
IIf(T1.Parts=0,0,sum((T2.Parts*T2.Prix)+(T2.[Notre Commission]+T2.[Droit de Timbre]+T2.[Commission Frais de Tiers]+T2.[Commission d'émission]+T2.[Taxe Bourse et CFB]+T2.[Commission Fiduciaire]+T2.DDE+T2.[Valeur Intérêts]+T2.UBS+T2.BSI+T2.CSM+T2.[Commission Courtage])*T2.[Change E])/sum(T2.Parts)) AS PampNet,
T1.[Parts V]*(T1.[Prix V]) AS Gain,
T1.[Notre Commission], T1.[Droit de Timbre], T1.[Commission Frais de Tiers], T1.[Commission d'émission], T1.[Taxe Bourse et CFB], T1.[Commission Fiduciaire], T1.DDE, T1.[Valeur Intérêts], T1.UBS, T1.BSI, T1.CSM, T1.[Commission Courtage]
FROM Interm AS T1 INNER JOIN Interm AS T2 ON (T1.Entrée>=T2.Entrée) AND (T1.[Nom Produit]=T2.[Nom Produit]) AND (T1.[Num Client]=T2.[Num Client])
WHERE T1.[Num Client]="12345"
GROUP BY T1.ID, T1.Entrée, T1.[Num Client], T1.[Nom Produit], T1.Parts, T1.Prix, T1.[Prix V], T1.[Parts V], T1.[Change E], T1.[Notre Commission], T1.[Droit de Timbre], T1.[Commission Frais de Tiers], T1.[Commission d'émission], T1.[Taxe Bourse et CFB], T1.[Commission Fiduciaire], T1.DDE, T1.[Valeur Intérêts], T1.UBS, T1.BSI, T1.CSM, T1.[Commission Courtage]; |
Partager