bonjour, j'aurais bien aimé avoir un rapport sur:
la consommation essence + huile pour chaque avion, cumulée pour la période définie ainsi que mensuellement sur l'année choisie.
Merci bcp.

Seb VITTOZ - Admin CAPAM

Les grands esprits se rencontrent ... je l'ai demandé (par année) dimanche, et ai eu la réponse hier. Voir mon post ici :
http://forums.openflyers.org/read.php?14,4485,4489#msg-4489

Bonne journée,

Sadoche
Beta testeur OF
Quiberon Air Club

Merci!
je vais être pénible jusqu'au bout, serait-il possible de l'avoir par mois également?
Ja sais...j'abuse mais je fais avancer le schmilblick...

Seb VITTOZ - Admin CAPAM

salut,

Je ne peux pas tester car mon club n'utilise pas cette fonctionnalité mais :

SELECT aircraft_type.name AS aircraft_type, tank.label AS tank_label, SUM( quantity ) AS total_qty
FROM tank
LEFT JOIN aircraft_type ON ( aircraft_type.id = tank.aircraft_type_id )
LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
WHERE flight.start_date LIKE '%YYYY-MM%'
GROUP BY aircraft_type_id, tank_type_id

Il faut remplacer YYYY-MM par le mois voulu. Ex, Février 2008 donnerait 2008-02

@+
Antoine

Voilà ... :


SELECT
aircraft_type_name, tank_label,
SUM(IF (month_num=1, quantity, 0)) AS Janu,
SUM(IF (month_num=2, quantity, 0)) AS Febr,
SUM(IF (month_num=3, quantity, 0)) AS Marc,
SUM(IF (month_num=4, quantity, 0)) AS Apri,
SUM(IF (month_num=5, quantity, 0)) AS Mai,
SUM(IF (month_num=6, quantity, 0)) AS June,
SUM(IF (month_num=7, quantity, 0)) AS July,
SUM(IF (month_num=8, quantity, 0)) AS Augu,
SUM(IF (month_num=9, quantity, 0)) AS Sept,
SUM(IF (month_num=10, quantity, 0)) AS Octo,
SUM(IF (month_num=11, quantity, 0)) AS Nove,
SUM(IF (month_num=12, quantity, 0)) AS Dece,
SUM(quantity) AS total
FROM
(
SELECT aircraft_type.id AS aircraft_type_id, aircraft_type.name AS aircraft_type_name, tank.id AS tank_id, tank.label AS tank_label, CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity, MONTH(flight.start_date) AS month_num
FROM tank
LEFT JOIN aircraft_type ON ( aircraft_type.id = tank.aircraft_type_id )
LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
WHERE YEAR(flight.start_date) = $year
) AS my_table
GROUP BY aircraft_type_id, tank_id

Je n'avais pas vu!!
merci du coup de main.


jcheng a écrit:
-------------------------------------------------------
> Voilà ... :
>
>
> SELECT
> aircraft_type_name, tank_label,
> SUM(IF (month_num=1, quantity, 0)) AS Janu,
> SUM(IF (month_num=2, quantity, 0)) AS Febr,
> SUM(IF (month_num=3, quantity, 0)) AS Marc,
> SUM(IF (month_num=4, quantity, 0)) AS Apri,
> SUM(IF (month_num=5, quantity, 0)) AS Mai,
> SUM(IF (month_num=6, quantity, 0)) AS June,
> SUM(IF (month_num=7, quantity, 0)) AS July,
> SUM(IF (month_num=8, quantity, 0)) AS Augu,
> SUM(IF (month_num=9, quantity, 0)) AS Sept,
> SUM(IF (month_num=10, quantity, 0)) AS Octo,
> SUM(IF (month_num=11, quantity, 0)) AS Nove,
> SUM(IF (month_num=12, quantity, 0)) AS Dece,
> SUM(quantity) AS total
> FROM
> (
> SELECT aircraft_type.id AS aircraft_type_id,
> aircraft_type.name AS aircraft_type_name, tank.id
> AS tank_id, tank.label AS tank_label, CAST(
> flight_tank_qty.quantity AS DECIMAL(10,2) ) AS
> quantity, MONTH(flight.start_date) AS month_num
> FROM tank
> LEFT JOIN aircraft_type ON ( aircraft_type.id
> = tank.aircraft_type_id )
> LEFT JOIN flight_tank_qty ON ( tank.id =
> flight_tank_qty.tank_id )
> LEFT JOIN flight ON (flight.id =
> flight_tank_qty.flight_id)
> WHERE YEAR(flight.start_date) = $year
> ) AS my_table
> GROUP BY aircraft_type_id, tank_id

Seb VITTOZ - Admin CAPAM