Créer une liste de dates en sql sous Oracle

Publié le par seekaftersomething.over-blog.com


Cette requête ne fonctionne que sous Oracle.

Vous est-il déjà arrivé de devoir lister pour une période donnée toutes les lignes d'une tables et en plus d'afficher les jours où il n'y a rien.


Prenons un exemple pour être plus clair, jai une table "COMMANDE" qui possède les champs suivants:
  • ID
  • DATE_LIVRAISON
  • QUANTITE
Bon pour l'exemple cela ne sera pas une table très normalisée

Mon besoin est de récupérer un tableau du 1er janvier 2010 au 31 janvier 2010 avec pour première colonne la date de livraison et la somme des quantités à cette date. Jusque là, pas de problème.

Mais si dans mon tableau je n'ai que 20 dates distinctes de livraison, là ce n'est plus aussi simple.

Pour éviter les bidoullages, genre je crée une table qui contient plein de date ou je remplacerais les dates manquantes depuis mon code (Java ou autre), il est plus simple de créer une table vituelle contenant ces dates.

Pour cela, j'ai besoin d'une table qui a autant de lignes que je souhaites avoir de dates. Appelons cette table "ALL_DETAIL", par défaut vous pouvez prendre une table système comme ALL_TABLES.

Requête pour lister toutes les dates du 01/01/2010 au 31/01/2010 :

 SELECT TO_DATE('01/01/2010', 'DD/MM/YYYY') + rownum -1 FROM ALL_DETAIL WHERE TO_DATE('01/01/2010', 'DD/MM/YYYY') + rownum - 1 <= TO_DATE('31/01/2010', 'DD/MM/YYYY'); 

Explication :
Dans oracle, rownum affiche le numéro de la ligne retourné. Admettons que la table ALL_DETAIL contiennent 1000 lignes, si l'on fait - SELECT rownum FROM ALL_DETAIL ORDER BY 1 - on obtiendra une colonne et 1000 lignes qui vont de 1 à 1000.

Donc dans le requête en-dessus TO_DATE('01/01/2010', 'DD/MM/YYYY') + rownum permet d'ajouter un jour à la date. Le -1 permet de commencer au 1er janvier 2010 étant donner que rownum commence à 1.

Pour stopper le nombre de dates retourner, il faut ajouter la condition:
TO_DATE('01/01/2010', 'DD/MM/YYYY') + rownum - 1  <= TO_DATE('31/01/2010', 'DD/MM/YYYY')

Lorsque la rownum sera à 32, la condition sera fausse. D'où l'importance du nombre de lignes dans la table.


Maintenant que nous avons une table virtuelle de date, il ne reste plus qu'à faire une jointure pour avoir le rapport demandé dans l'énoncé.

SELECT TABLE_DATE.DATE_J, SUM(QUANTITE) FROM
(
SELECT (TO_DATE('01/01/2010', 'DD/MM/YYYY') + rownum -1) DATE_J
FROM ALL_DETAIL
WHERE TO_DATE('01/01/2010', 'DD/MM/YYYY') + rownum - 1  <= TO_DATE('31/01/2010', 'DD/MM/YYYY') ) AS TABLE_DATE
LEFT JOIN COMMANDE
ON TABLE_DATE.DATE_J = COMMANDE.DATE_LIVRAISON
GROUP BY TABLE_DATE.DATE_J;



Pour cumuler les quantités et mettre 0 s'il n'y a plus de valeur, il faut compléter la requête avec :

SELECT DATE, NVL(QTE,0) FROM (
 SELECT TABLE_DATE.DATE_J, SUM(QUANTITE) QTEFROM
 ....
GROUP BY TABLE_DATE.DATE_J );


Et voilà....

Publié dans SQL

Commenter cet article

Laurent 28/10/2016 13:46

Cela m'a permit également de définir les jours ouvré

Laurent 28/10/2016 13:46

Cela m'a permit également de définir les jours ouvré

bertrand 19/11/2012 15:17

En effet, cette requête est pratique pour générer un calendrier.

On peut aussi utiliser le "connect by level" sous Oracle avec une requête du style :

select to_date('01/01/2010','DD/MM/YYYY')-1 + level as DateRange
from dual
where (to_date('01/01/2010','DD/MM/YYYY')-1+level)