1 2 3 4 5 6
| SELECT vehicules.SECTEUR, vehicules.[attrib véhicule], vehicules.[type de véhicule], vehicules.vehicule, Sum(IIf([T_Analytique].[COMPTE]="61553000",[mtht],0)) AS Cout_rep, Sum(IIf([T_Analytique].[COMPTE]="60614000",[mtht],0)) AS Cout_carbur, Sum(IIf([T_Analytique].[COMPTE]="68112000",[mtht],0)) AS Amort, Sum(IIf([T_Analytique].[COMPTE]="61630000",[mtht],0)) AS Assurances, Sum(IIf([T_Analytique].[COMPTE]="63513000",[mtht],0)) AS vignette, Sum(IIf([T_Analytique].[COMPTE]="63540000",[mtht],0)) AS carte_grise, Sum(IIf([T_Analytique].[COMPTE]="79100000",[mtht],0)) AS remb_assurance, Sum(IIf([T_Analytique].[COMPTE]="60635000",[mtht],0)) AS frais_div, ([Cout_rep]+[Cout_carbur]+[Amort]+[Assurances]+[vignette]+[carte_grise]+[remb_assurance]+[frais_div]) AS px_revient_total, Sum(IIf([T_Analytique].[COMPTE]="KMS",[mtht],0)) AS kms_total, IIf([kms_total]<>0,[px_revient_total]/[kms_total],0) AS Px_kms, vehicules.NB1, IIf([NB1]>0,IIf(nz([date de vente])<>"" And nz([date de vente])<#9/30/2005#,round((#9/30/2005#-[date de vente]+1)/365,2),0)+IIf(nz([date de vente])="" Or nz([date de vente])>#9/30/2005#,1,0),0) AS NB2, IIf([NB1]>0,IIf(Nz([date de vente])="" Or [date de vente]<=#9/30/2005#,1,0)+IIf([date de vente]>#9/30/2005#,1,0)-IIf([date de vente]<#9/30/2005#,1,0),0) AS NB3, T_Analytique.date
FROM vehicules LEFT JOIN T_Analytique ON vehicules.vehicule = T_Analytique.vehicule
WHERE (((vehicules.[date de vente])>#9/30/2004# Or (vehicules.[date de vente]) Is Null))
GROUP BY vehicules.SECTEUR, vehicules.[attrib véhicule], vehicules.[type de véhicule], vehicules.vehicule, vehicules.NB1, IIf([NB1]>0,IIf(nz([date de vente])<>"" And nz([date de vente])<#9/30/2005#,round((#9/30/2005#-[date de vente]+1)/365,2),0)+IIf(nz([date de vente])="" Or nz([date de vente])>#9/30/2005#,1,0),0), IIf([NB1]>0,IIf(Nz([date de vente])="" Or [date de vente]<=#9/30/2005#,1,0)+IIf([date de vente]>#9/30/2005#,1,0)-IIf([date de vente]<#9/30/2005#,1,0),0), T_Analytique.date
HAVING (((vehicules.SECTEUR) Is Not Null) AND ((T_Analytique.date)=#9/30/2005#))
ORDER BY vehicules.SECTEUR, vehicules.vehicule; |
Partager