Régler ce problème en une seule requête [Résolu]

[adelneo]

Bonjours,
J'ai quatre tables en FB 2.1 (les clés primaires son soulignées, les clés étrangères sont en gras):
Consommations(idconsommations,idvehicules,date_conso,conso_L)
Vehicules(idvehicules,idcarburants)
Carburants(idcarburants,type)
Carb_detail(idcarb_detail,idcarburants,prix,date_effet)

Les relations entre les tables sont comme suit:
Consommations---N,1---Vehicules---N,1---Carburants---1,N---Carb_detail.

Les données:
Carburants
idcarburants ____ type
1_______________Essence
2_______________Gas-Oil

Carb_detail
Idcarb_detail __ Idcarburants ________ Prix ________ Date_effet
1_______________1________________23,00_________01.01.2010
2_______________1________________25,00_________01.01.2012
3_______________1________________30,00_________01.06.2013
4_______________2________________13,70_________01.01.2010
5_______________2________________15,00_________01.01.2012
6_______________2________________16,50_________01.06.2013

Vehicule
Idvehicules _____ Idcarburants
______1________________1
______2________________2

Consommations
Idconsommations____idvehicules________Date_conso____ Conso_L
1____________________1_____________25.05.2011________ 10
2____________________1_____________12.02.2012__________7
3____________________2_____________01.12.2011________ 15
4____________________2_____________02.01.2013__________9
5____________________2_____________13.08.2013________ 14
6____________________2_____________25.10.2013________ 23


Le problème: écrire une requête qui renvoi le résultat suivant
Idvehicules, Date_conso, Conso_L , Prix, Depense
Sachant que la colonne Depense = Conso_L * Prix
et le prix choisis dépend de la date de consommation 'Date_conso' et la date d'effet du prix c à d : pour la consommation du 25.05.2011 je dois avoir le prix 23,00 puisque la date d'effet de ce prix 01.01.2010 < 25.05.2011
et la date d'effet du prix suivant 01.01.2012 > 25.05.2011.

Donc le résultat voulu sera:
Idvehicules______Date_conso____ Conso_L____ Prix________Date_effet______Depense
1______________25.05.2011________ 10_______23________01.01.2010________ 230
1______________12.02.2012_________7________25________01.01.2012________ 175
2______________01.12.2011_________15_______13.7______01.01.2010________ 205.5
2______________02.01.2013_________9________15________01.01.2012________ 135
2______________13.08.2013_________14_______16.5______01.06.2013________ 231
2______________25.10.2013_________23_______16.5______01.06.2013________ 379.5

[SergioMaster]

le plus dur semble être d'aller à la pêche au prix

Quelque chose comme :

Code :

1
2
SELECT FIRST PRIX FROM CARB_DETAIL WHERE ID_CARBURANT=(idcarburant du vehicule) AND DATE_EFFET<=date_conso 
ORDER BY DATE_EFET DESC

devrait fournir la réponse

ensuite le reste devrait être facile , après les fêtes

[adelneo]

Envoyé par SergioMaster

le plus dur semble être d'aller à la pêche au prix
Quelque chose comme :

Code :

Cette requête renvoie le prix exacte pour une date_conso donnée c à d que je dois entrer une date de consommation précise pour avoir le prix, mais ce que je veux c'est d'afficher l'ensemble des dépenses (consommation * prix) sur une grille et cette requête ne me facilite pas la tache.
Avez-vous une idée ??
Toute solution utilisant une procédure stockée ou autre manouvre sera la bienvenue

[CinePhil]

1) Quelle est la date d'effet à prendre en compte pour chaque date de consommation ?
=> On cherche la plus grande date d'effet inférieure ou égale à chaque date de consommation pour le carburant du véhicule :

Code :

1
2
3
4
5
6
SELECT co.id_vehicules, co.date_conso, co.conso_L, v.idcarburants, MAX(cd.date_effet) AS date_effet
FROM Consommations co
INNER JOIN Vehicules v ON v.idvehicules = co.idvehicules
  INNER JOIN Carb_detail cd ON cd.idcarburants = v.idcarburants
WHERE cd.date_effet <= co.date_conso
GROUP BY co.id_vehicules, co.date_conso, co.conso_L, v.idcarburants

2) Quel sont le prix et la dépense correspondants ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT tmp.id_vehicules, tmp.date_conso, tmp.conso_L, , cd1.prix, tmp.date_effet,
  conso_L * prix AS depense
FROM Carb_detail cd1
INNER JOIN
(
  SELECT co.id_vehicules, co.date_conso, co.conso_L, v.idcarburants, MAX(cd.date_effet) AS date_effet
  FROM Consommations co
  INNER JOIN Vehicules v ON v.idvehicules = co.idvehicules
    INNER JOIN Carb_detail cd ON cd.idcarburants = v.idcarburants
  WHERE cd.date_effet <= co.date_conso
  GROUP BY co.id_vehicules, co.date_conso, co.conso_L, v.idcarburants
) tmp 
  ON tmp.idcarburants = cd1.idcarburants
  AND tmp.date_effet = cd1.date_effet

[adelneo]

Merci Super Philippe, toujours là quand personne ne trouve la réponse .
Que la fête commence