1 2 3
| SELECT ebons.datliv AS [date livraison], Year([date livraison]) AS année, Month([date livraison]) AS mois, [mois] & " " & [année] AS [année mois], DatePart("ww",[date livraison],2,3) AS [n° semaine], [n° semaine] & " " & [année] AS [année semaine], ebons.datach AS dateachat, especes.libesp AS espèces, [new variétés].marché, [new variétés].[famille produit 2], [new variétés].[famille produit], [new variétés].libvar AS variétés, clients.nomcli AS clients, fourn.nomfrn AS fournisseurs, lbons.qte AS quantités, ebons.mttrsp AS transport, ebons.chep AS logistique, lbons.pnet AS [poids net], lbons.puach AS [prix d'achat unitaire], ebons.remifou AS [remise fournisseur], IIf([modufach]="B",[pbrut]*[puach]*(1-[remifou]/100),IIf([modufach]="N",[pnet]*[puach]*(1-[remifou]/100),IIf([modufach]="C",[qte]*[puach]*(1-[remifou]/100),[qte]*[puach]*[uv1con]*(1-[remifou]/100)))) AS mtach, IIf([modufven]="K",[mtach]*[pctcom]/100,IIf([modufven]="B",[pbrut]*[puven],IIf([modufven]="N",[pnet]*[puven],IIf([modufven]="C",[qte]*[puven],[qte]*[puven]*[uv1con])))) AS mtcom, lbons.puven AS [prix de vente unitaire], IIf([modufven]="B",[pbrut]*[puven],IIf([modufven]="N",[pnet]*[puven],IIf([modufven]="C",[qte]*[puven],[qte]*[puven]*[uv1con]))) AS mtven, [mtven]*(100-[remicli])/100 AS vennet, ufac.moduf AS modufach, ufac_1.moduf AS modufven, IIf([comneg]="N",[vennet],[mtach]+[mtcom]) AS [ventes totales], [mtach] AS achats, [ventes totales]-[achats] AS [marge brute]
FROM fourn INNER JOIN ((clients INNER JOIN ebons ON clients.codcli = ebons.codcli) INNER JOIN ((((produits INNER JOIN condi ON produits.codcon = condi.codcon) INNER JOIN especes ON produits.codesp = especes.codesp) INNER JOIN ((lbons INNER JOIN ufac ON lbons.unach = ufac.coduf) INNER JOIN ufac AS ufac_1 ON lbons.unven = ufac_1.coduf) ON produits.codpro = lbons.codpro) INNER JOIN [new variétés] ON produits.codvar = [new variétés].codvar) ON ebons.nobon = lbons.nobon) ON fourn.codfrn = ebons.codfou
ORDER BY ebons.datach; |
Partager