Trouver les lignes suivant une date d'expiration

[nesswaw]

Bonjour,

J'aimerais faire une requête, mais je sais pas si cela est réalisable, je m'explique.

J'ai une table mysql nommée "facture":

- idFacture => id unique
- fk_idContrat => id du contrat, lié à la table "contrat"
- dateStart
- dateEnd

Voici un exemple de ce que peut contenir cette table (dans l'ordre des champs décrit plus haut):

1, 1, 09-05-2011, 08-05-2012
2, 1, 09-05-2012, 08-05-2013
3, 1, 09-05-2013, 08-05-2014
4, 2, 12-05-2011, 11-05-2012
5, 2, 12-05-2012, 11-05-2013

J'aimerais faire une requête qui me sort les factures dont la date de fin est déjà passé OU sera passé dans 40 jours, et cela pour chaque contrat.

Donc il faut déjà que je trouve la plus grande date de fin pour chaque contrat et après tester si la date arrive à expiration?

Déjà comment trouver la plus grande date? Je dois faire des sous-requêtes?

Merci d'avance

[Nheo_]

Bonjour,

Cela devrait t'aider :

Code mysql :

1
2
3
4
 
SELECT idFacture FROM facture
           WHERE (TO_DAYS(dateEnd) - TO_DAYS(NOW()))<= 40
           OR TO_DAYS(dateEnd) < TO_DAYS(NOW());

En gros : tu ramène la date en jours (nombre de jours calculé depuis le "0000-00-00").

Et tu fais la différence : si ta date de fin moins ta date actuelle est inférieure à 40, alors tu retourne l'ID.

De même si ta date de fin est inférieure à la date actuelle (donc la date est passée), alors tu retourne l'id.

[nesswaw]

Bonjour,

Merci pour votre réponse, j'avais déjà aussi trouver une piste avec ça:

Code :

1
2
 
date_add(CURDATE(), interval + 40 day)

Votre requête fonctionne, mais maintenant j'aimerais qu'il me sorte la date de fin la plus grande, j'ai essayer comme ca:

Code :

1
2
3
4
 
SELECT idFacture, MAX(dateFinFacture) AS isMax FROM facture
           WHERE (TO_DAYS(isMax) - TO_DAYS(NOW()))<= 40
           OR TO_DAYS(isMax) < TO_DAYS(NOW());

Mais il me renvoie qu'il ne trouve pas la colonne isMax...on ne peut pas servir les colonne "virtuel" dans un WHERE?

[Nheo_]

Je ne suis pas sur d'avoir compris. Tu veux qu'il te renvoit la date la plus grande PARMI les dates passées ou celles qui le seront dans 40jours ?

Si oui, il suffit de faire ceci :

Code :

1
2
3
SELECT idFacture, MAX(dateFinFacture) AS isMax FROM facture
           WHERE (TO_DAYS(dateEnd) - TO_DAYS(NOW()))<= 40
           OR TO_DAYS(dateEnd) < TO_DAYS(NOW());

Il ne faut pas remplacer dateEnd pas isMax, car en effet cela ne peux pas marcher : tu fais une condition sur le résultat de ta requête, qui n'est donc pas encore créé.

[nesswaw]

J'aimerais, pour chaque contrat (fk_idContrat) qu'il prenne la date la plus grande (date de la dernière facture) et qu'il regarde si cette date arrivera à bout dans 40 jours ou si elle est déjà à bout.

Pour récupérer les dates avec les 40 jours c'est ok, mais pour les plus grande je suis coincé.

Faut-il pas d'abord qu'il trouve la plus grande date pour chaque contrat via une sous-requête?

Merci

[CinePhil]

Quelle est la date de fin maxi pour chaque contrat ?

Code :

1
2
3
SELECT fk_idContrat, MAX(dateEnd) AS dateEnd_maxi
FROM facture
GROUP BY fk_idContrat

Maintenant, que veux tu conserver dans le résultat de ta requête ?
- les contrats dont la date de fin maxi est supérieure à aujourd'hui + 40 jours ?
- les contrats dont la date de fin maxi est inférieure à aujourd'hui + 40 jours ?
- 40 jours inclus ou exclus ?

Il suffit d'ajouter la condition adéquate dans un HAVING. Par exemple, pour le premier cas :

Code :

1
2
3
4
SELECT fk_idContrat, MAX(dateEnd) AS dateEnd_maxi
FROM facture
GROUP BY fk_idContrat
HAVING MAX(dateEnd) > DATE_ADD(CURRENT_DATE, INTERVAL 40 DAY)

[nesswaw]

Bonjour,

Désolé pour le temps de réponse, j'ai du mettre de côté ce projet...mais je le reprend en main depuis quelques jours...

Enfait je dois trouver la facture la plus récente pour chaque contrat.

J'ai essayé avec votre requête:

Code :

1
2
3
4
 
SELECT fk_idContrat, MAX(dateEnd) AS dateEnd_maxi
FROM facture
GROUP BY fk_idContrat

Ca me trouve bien les dernière date pour le champ MAX(), mais les id des facture ne corresponde pas, il me prend pour chaque contrat, l'id de la 1ère facture qu'il trouve et non le dernier...

Une idée de comment "inverser" cela?

Merci

[CinePhil]

Il faut faire une jointure de ma requête avec la table des factures pour récupérer la facture correspondant à la date trouvée et au contrat.

Code :

1
2
3
4
5
6
7
8
9
10
SELECT f.idFacture
FROM facture f
INNER JOIN
(
	SELECT fk_idContrat, MAX(dateEnd) AS dateEnd_maxi
	FROM facture
	GROUP BY fk_idContrat
) tmp 
	ON tmp fk_idContrat = f.fk_idContrat
	AND tmp.dateEnd_maxi = f.dateEnd

[nesswaw]

Chapeau sur ce coup là, j'y serais jamais arrivé, du coup ma requête donne ça:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT f.idFacture, f.dateFinFacture, c.idContrat, s.urlSite
FROM facture f
LEFT JOIN contrat c ON f.fk_idContrat = c.idContrat
LEFT JOIN site s ON c.fk_idSite = s.idSite
INNER JOIN
(
	SELECT fk_idContrat, MAX(dateFinFacture) AS dateEnd_maxi
	FROM facture
	GROUP BY fk_idContrat
) tmp 
	ON tmp.fk_idContrat = f.fk_idContrat
	AND tmp.dateEnd_maxi = f.dateFinFacture
WHERE f.dateFinFacture <= DATE_ADD(CURRENT_DATE, INTERVAL 40 DAY)
ORDER BY s.urlSite

Ca me récupère la facture pour chaque contrat qui arrive à bout dans 40 jours.

Par contre est-ce possible d'avoir une explication sur votre requête, je ne la comprend pas totalement avec cet INNER JOIN, c'est en gros une sous-requête qui va sélectionner les plus grandes dates?

Merci pour votre aide

[CinePhil]

INNER JOIN = jointure interne
LEFT [OUTER] JOIN = jointure externe (à gauche).

Pour en apprendre plus sur les jointures, voir le site de SQLPro.