Dates antérieures

Version imprimable

01/05/2019, 08h58
cimandef

Dates antérieures

Bonjour à tous

Je sollicite votre aide pour une requête qui doit m'extraire des données en remontant à partir de la date du jour , les 5 jours antérieurs.
Dans ma requête jusqu'à présent j'avais cela :

Code:

where demande.datenreg between to_char(current_date, 'YYMMDD')-5 and to_char(current_date,'YYMMDD')

cela fonctionnait à merveille je précise et vous allez comprendre pourquoi ma demande, requête créé donc le 13/04/2019.

Aujourd'hui 1 Mai , la même requête me ramène que les données du 1er Mai mais non les données du 26-27-28-29-30 avril comme attendu !

Merci pour votre retour.

Cordialement.

Pourquoi convertir la date en chaine ?
Code:

1 2 3 4 5 SELECT SYSDATE AS current_date, SYSDATE + 1 AS plus_1_day, SYSDATE + 1/24 AS plus_1_hours, SYSDATE - interval '5' day from dual;
et voir ici (selon la version) : https://docs.oracle.com/cd/E41183_01/DR/Dateadd.html

Rebonjour et merci pour votre retour.
Je vous mets la requête complète qui fonctionnait au mois d'Avril
Je la place ou ? le select Sysdate SVP

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
select 
 SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2) AS "DATE"
,SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2)||'-'||SUBSTR(DEMANDE.NODEMX,7,1)||'-'||SUBSTR(DEMANDE.NODEMX,8,4) AS DOSSIER
,PATIENT.NOM || ' ' || PATIENT.PRENOM AS PATIENT 
,DEMANDE.NOHOSP AS HOSPITALISATION
,prescript.ipresc As MÉDECIN
,v_hd_activitenetteparacte.codecorrespondant as "CORRESPONDANT"
,v_hd_activitenetteparacte.lettrecle As "LETTRE CLE"
,sum(nbb)As "TOTAL B"
,sum(ca/100) As "MONTANT DOSSIER"
, listagg( v_hd_activitenetteparacte.acte,'-') within group (order by acte desc) As "CODE AFFINÉ"
 
from v_hd_activitenetteparacte
join demande on demande.nodem= v_hd_activitenetteparacte.IDDOSSIER
and  V_HD_ACTIVITENETTEPARACTE.version=demande.VERSION
Join patient on demande.nopat=patient.nopat
join appresc on appresc.nodem=demande.nodem
 join prescript on prescript.cpresc=appresc.cpresc
 and appresc.nordre='O'
 
 
where demande.datenreg between to_char(current_date, 'YYMMDD')-10 and to_char(current_date,'YYMMDD') 
and v_hd_activitenetteparacte.CODECORRESPONDANT in ('CDSC','CDOR','CDLP')
and demande.clabo='1'
 
group by
SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2)
,SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2)||'-'||SUBSTR(DEMANDE.NODEMX,7,1)||'-'||SUBSTR(DEMANDE.NODEMX,8,4) 
, PATIENT.NOM || ' ' || PATIENT.PRENOM 
,v_hd_activitenetteparacte.CODECORRESPONDANT
,v_hd_activitenetteparacte.LETTRECLE
,DEMANDE.NOHOSP 
,PRESCRIPT.IPRESC
 
order by
PATIENT.NOM || ' ' || PATIENT.PRENOM

Bonjour,

Merci d'utiliser les balises de codes lorsque vous en postez.

Que donne cette requête ?
Code:

1 2 3 4 5 select datenreg, COUNT(*) from demande where datenreg between TRUNC(sysdate)-10 and TRUNC(sysdate) group by datenreg order by datenreg

02/05/2019, 10h06
skuatamad

Vous utilisez très mal le type de donnée date, ce qui conduit à de multiples conversions implicites :
to_char(current_date, 'YYMMDD') renvoie une chaine de caractère 190501 au 1er mai.
to_char(current_date, 'YYMMDD')-5 convertit implicitement 190501 en nombre et retire 5 => 190496 => Gros problème, n'est-ce pas !

Si vous voulez remonter les données du jour courant modifier votre between en :

Code:

where demande.datenreg >= trunc(current_date) - 5 and demande.datenreg < trunc(current_date) + 1 -- si les données du jour courant sont à exclure retirer le +1

Idem dans votre SELECT, vous faites des SUBSTR en fonction de l'affichage de la date dépend de votre paramétrage de session.

Modifez :

Code:

SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2)

en :

Code:

to_char(DEMANDE.DATENREG,'dd/mm/yyyy')

Et d'une manière générale, sauf si particulièrement imposé par le métier, travailler avec l'année sur 4 caractères et non 2.

Sur ce forum on est nombreux à insister sur la "présentation" du code (même si on n'est pas tous d'accord sur l'implémentation)
J'en fais partie.
Lorsqu'une requête est longue, il est important de faciliter la relecture du code, donc sa maintenance.

En tenant compte des contributions je te propose une façon de réécrire ta requête.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SELECT   temp."DATE" 
        ,temp.dossier 
        ,temp.patient 
        ,temp.hospitalisation 
        ,temp.MÉDECIN 
        ,temp.correspondant 
        ,temp."LETTRE CLE" 
        ,Sum(temp.nbb)                                            AS "TOTAL B" 
        ,Sum(temp.ca/100)                                         AS "MONTANT DOSSIER" 
        ,Listagg(temp.acte,'-') within GROUP (ORDER BY acte DESC) AS "CODE AFFINÉ"
FROM (
        SELECT   to_char(d.datenreg, 'YYYYMMDD')    AS "DATE" 
                ,concat_ws('-',to_char(d.datenreg, 'YYYYMMDD'),substr(d.nodemx,7,1),substr(d.nodemx,8,4) AS dossier 
                ,p.nom||' '|| p.prenom              AS patient 
                ,d.nohosp                           AS hospitalisation 
                ,pr.ipresc                          AS MÉDECIN 
                ,vha.codecorrespondant              AS "CORRESPONDANT" 
                ,vha.lettrecle                      AS "LETTRE CLE" 
                ,???.nbb                            AS nbb 
                ,???.ca                             AS ca 
                ,vha.acte                           AS acte 
        FROM v_hd_activitenetteparacte vha
            JOIN demande d
                ON  d.nodem  =vha.iddossier
                AND d.version=vha.version
            JOIN patient p
                ON d.nopat=p.nopat
            JOIN appresc a
                ON a.nodem=d.nodem
            JOIN prescript pr
                ON pr.cpresc=a.cpresc 
        WHERE d.datenreg BETWEEN d.datenreg-10 AND CURRENT_DATE
              AND vha.codecorrespondant IN ('CDSC','CDOR','CDLP')
              AND d.clabo='1'
              AND a.nordre='O' 
     ) temp
GROUP BY 
         temp."DATE" 
        ,temp.dossier 
        ,temp.patient 
        ,temp.hospitalisation 
        ,temp.MÉDECIN 
        ,temp.correspondant 
        ,temp."LETTRE CLE" 
ORDER BY temp.patient

Il faut remplacer les ??? par le bon l'alias.

Rappel : On n'est pas tous d'accord sur CE formalisme mais on est d'accord sur le fait d'en adopter.

Fait la comparaison entre le code initial et celui-ci et tires en tes conclusions.

02/05/2019, 12h58
cimandef
Bonjour

Merci à tous pour vos retours et avant tout : mille excuses pour les balises oubliées !
j'ai modifié donc mon between de cette façon et j'ai des résultats, grâce à vos remarques.
Code:

1 2 where to_date(SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2),'DD/MM/YY') between TO_DATE(sysdate-5,'DD/MM/YY')and TO_DATE(sysdate,'DD/MM/YY')
@ M.Priori
la présentation du code est effectivement plus claire, je vais donc m'en inspirer pour le futur , même si ton code me dit:
ORA 00907 parenthèse de droite manquante .

Merci encore pour l'aide et les pistes de réflexions.
Cordialement
02/05/2019, 13h05
skuatamad
Citation:
Envoyé par cimandef

j'ai modifié donc mon between de cette façon et j'ai des résultats, grâce à vos remarques.

Code:

1 2 where to_date(SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2),'DD/MM/YY') between TO_DATE(sysdate-5,'DD/MM/YY')and TO_DATE(sysdate,'DD/MM/YY')
Non, c'est pire que tout !
Et ça n'est absolument pas ce qu'on vous a proposé !
02/05/2019, 20h27
Michel.Priori
skuatamad a raison !

On reprend.
Le type date est là pour stocker et traiter les dates convenablement.
Il existe une palanquée de fonctions qui permettent de faire toutes sortes d'opérations.

Les fonctions to_char() et substr() ne sont pas des fonctions date.
L'objectif étant de ramener le contenu sous forme de chaine (Substr = sub string = partie de chaine).

Le problème avec les colonnes de type date est que la représentation interne est systématiquement remplacée par une chaine dont la présentation dépend des paramètres de session, c'est à dire de l'utilisateur connecté.
La même date peut s'afficher différemment selon le paramètre NLS_DATE_FORMAT, paramètre qui par défaut dépend d'autres paramètres NLS.
Ici un petit exemple : http://www.dba-oracle.com/t_nls_date_format.htm

Donc quand tu écris :

Code:

to_date(SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2),'DD/MM/YY')

C'est absurde !

Prenons la formule par partie :

Quel est le type de DEMANDE.DATENREG ?
Sans avoir vu le code de la création de la colonne, elle devrait DATE
voir : https://docs.oracle.com/cd/B28359_01...e.htm#CNCPT413

Que donne SUBSTR(DEMANDE.DATENREG,5,2) ?
Selon le format indiqué, le jour sur 2 positions

Que donne SUBSTR(DEMANDE.DATENREG,3,2) ?
Selon le format indiqué, le mois sur 2 positions

Que donne SUBSTR(DEMANDE.DATENREG,1,2) ?
Selon le format indiqué, l'année sur 2 positions

Comment s'affiche une date selon tes paramètres NLS ?
Selon le format YYMMDD

Que donne SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2) ?
Une chaine de 8 caractères

Que donne to_date(SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2),'DD/MM/YY') ?
Une date
C'est à dire exactement le même type d'information que la colonne elle même.
:weird: intérêt de l'écriture ? :weird:

Pour te convaincre que les dates s'affichent comme du texte mais se comporte -presque- comme un numérique lance la requête suivante :
Code:

1 2 3 select sysdate as aujourdhui, DEMANDE.DATENREG, DEMANDE.DATENREG-1 as La_veille, DEMANDE.DATENREG-30 as trente_jours_avant , sysdate - DEMANDE.DATENREG as NB_jours_d_ecart FROM DEMANDE ;
03/05/2019, 07h03
cimandef

1 pièce(s) jointe(s)

Bonjour à tous

@ M. Priori ,Skuatamad

J’ai bien compris vos réflexions et vous avez bien compris aussi que je n’ai pas votre maitrise du langage SQL, mais croyez-moi j’apprends beaucoup au contact de personnes comme vous que je remercie d’ailleurs de m’apporter des réponses avec des requêtes optimales.

Travaillant dans un LABM ( labo Analyses Médicales ) Il m’arrive assez souvent de faire des extractions sur la base Oracle pour la facturation et la comptabilité.
Dans notre base les nodemx ou autre iddossier ( dossier du jour) sont sous cette forme B905021002 ou
B9 = 2019
05 = mois
02 = date du jour
1 = numéro du labo
002 = numéro compteur attribué au dossier

Dans l’interface métier le numéro dossier du patient apparait sous cette forme = 02/05/2019-1-002 dès que l’enregistrement est finalisé.

C’est pour cela que j’utilise des SUBSTR et autre To_date car pour une secrétaire c’est beaucoup plus facile de rechercher sous l’interface
02/05/2019/-1-002 DUPONT PAUL

Pièce jointe 471718

Que B905021002 DUPONT PAUL d’ailleurs on ne peut pas rechercher sous cette forme dans l’interface.

La colonne demande.datenreg est de type varchar2 comme le nodemx, comme l’heure d’enregistrement du dossier, c’est peut-être voulu par notre éditeur.

Donc en remplaçant les codes comme vous me l’avait suggéré, je n’ai aucun retour, en revanche je me suis basé sur vos réflexions pour « pondre » le code que vous M.Priori trouve absurde (qui je peux le comprendre pour des gens de votre calibre ce genre de code vous agace) code donc qui me ramène les données qui ensuite je transfère sous Excel pour notre pôle support.

Voilà , SVP ne n’en veuillez pas si je code comme un « bourrin » j’essaie de m’adapter et encore une fois d’apprendre aux contacts de personnes comme vous.

Cordialement,
03/05/2019, 09h51
Michel.Priori

Citation:

Envoyé par cimandef

La colonne demande.datenreg est de type varchar2 comme le nodemx, comme l’heure d’enregistrement du dossier, c’est peut-être voulu par notre éditeur.

Un éditeur qui nomme une colonne DATEquelquechose et qui lui colle un type varchar2, mérite une médaille.

Du point de vue du stockage on utilise autant d'octets que le chiffre entre parenthèse, et comme c'est un VARchar2() il y a 1 octet de contrôle pour savoir où la fin de la donnée.
Du point de vue du contrôle de donnée est-ce qu'on peut saisir un 31 juin ?

Tandis qu'un type date offre :
- un stockage sur 7 octets
- un contrôle des valeurs
- une bibliothèque de fonction pour manipuler ça

Une médaille. Ils le méritent bien.
03/05/2019, 10h37
cimandef

1 pièce(s) jointe(s)

Et pour finir

voila le résultat de la requête demandé plus haut

Pièce jointe 471759

Une médaille .....certainement :)

Citation:

Envoyé par cimandef

Travaillant dans un LABM ( labo Analyses Médicales ) Il m’arrive assez souvent de faire des extractions sur la base Oracle pour la facturation et la comptabilité

Je résume on demande à un laborantin de faire du code SQL sur les logiciels de qualité pour faire un export import des données d'entreprise.
Ils n'ont pas un problème de management dans ton unité ? :weird:
J'espère que tu as le temps d'assumer ton poste en plus de ces petits "extra"... :?

La logique globale m'échappe. Soit. Ce n'est pas à moi d'en juger.
mais quand même...

Revenons à la requête :
to_date() permet de transformer une chaine en type date selon le format indiqué.
On va s'en servir pour la clause Where pour la soustraction de date, et, dans la clause select pour faire une double conversion afin de la présenter dans le format attendu.
La double conversion permet de faire déclencher une erreur si le texte initial n'est pas dans le bon format 'YYMMDD' et si les valeurs ne sont pas convertissables en date (ex : '190631')
il vaut mieux une erreur en amont que de faire l'import et s’apercevoir quelques mois/années après que la donnée était fausse ; bonjour pour remonter le temps.

La requête interne (temp) devrait donc s'écrire plutôt comme ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 SELECT to_date(d.datenreg, 'YYMMDD') AS "DATE" ,concat_ws('-',to_char(to_date(d.datenreg, 'YYMMDD'),'DD/MM/YYYY'),substr(d.nodemx,7,1),substr(d.nodemx,8,4)) AS dossier ,p.nom||' '|| p.prenom AS patient ,d.nohosp AS hospitalisation ,pr.ipresc AS MÉDECIN ,vha.codecorrespondant AS "CORRESPONDANT" ,vha.lettrecle AS "LETTRE CLE" ,???.nbb AS nbb ,???.ca AS ca ,vha.acte AS acte FROM v_hd_activitenetteparacte vha JOIN demande d ON d.nodem =vha.iddossier AND d.version=vha.version JOIN patient p ON d.nopat=p.nopat JOIN appresc a ON a.nodem=d.nodem JOIN prescript pr ON pr.cpresc=a.cpresc WHERE to_date(d.datenreg,'YYMMDD') BETWEEN to_date(d.datenreg,'YYMMDD')-10 AND CURRENT_DATE AND vha.codecorrespondant IN ('CDSC','CDOR','CDLP') AND d.clabo='1' AND a.nordre='O'
bien sûr il faut remplacer les ???. par le bon alias de table.

03/05/2019, 18h50
cimandef

1 pièce(s) jointe(s)

Voila ce que ramène la requête

Pièce jointe 471919
04/05/2019, 08h38
Michel.Priori

Oups, Concat_ws() existe bien chez Oracle mais que sur MySQL :(

Du coup faut revenir à la syntaxe lourde :

Code:

to_char(to_date(d.datenreg, 'YYMMDD'),'DD/MM/YYYY') ||'-'|| substr(d.nodemx,7,1) ||'-'|| substr(d.nodemx,8,4)
04/05/2019, 12h13
cimandef

2 pièce(s) jointe(s)
Avec ton code on débute aux premiers dossiers dans la base 2013

Pièce jointe 472116

et avec mon code certes tordu!
Code:

1 2 where to_date(SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2),'DD/MM/YY') between TO_DATE(sysdate-10,'DD/MM/YY')and TO_DATE(sysdate,'DD/MM/YY')
j'arrive à retrouver les patients d'aujourd'hui à 10 jours avant

Pièce jointe 472117

Oups encore une fois désolé.

Tu avais bien vu que le problème venais bien de la condition dans la clause Where.
En examinant les 2 on retrouve ma boulette :
Code:

1 2 3 4 5 -- la tienne where to_date(SUBSTR(DEMANDE.DATENREG,5,2)||'/'||SUBSTR(DEMANDE.DATENREG,3,2)||'/'|| SUBSTR(DEMANDE.DATENREG,1,2),'DD/MM/YY') between TO_DATE(sysdate-10,'DD/MM/YY')and TO_DATE(sysdate,'DD/MM/YY') --la mienne WHERE to_date(d.datenreg,'YYMMDD') BETWEEN to_date(d.datenreg,'YYMMDD')-10 AND CURRENT_DATE
tandis que tu filtres sur les 10 derniers jours à partir d'aujourd'hui je filtre sur les 10 jours avant la date de l'enregistrement jusqu'à aujourd'hui...

En remplaçant par :

Code:

WHERE to_date(d.datenreg,'YYMMDD') BETWEEN SYSDATE-10 AND SYSDATE

ça devrait aller mieux ;)

04/05/2019, 20h44
cimandef

C'est parfait

Merci pour les leçons sur le TO_DATE et LE TO_CHAR
06/05/2019, 09h11
CinePhil

C'est donc :resolu: ?
06/05/2019, 11h07
Waldar
Je ne suis pas tout à fait d'accord avec la solution proposée, qui va induire une conversion systématique de votre colonne.
En fonction du volume ça peut être pénalisant.
Il faut au choix :
- apposer un index sur to_date(dateenr, 'yymmdd'), mais si vous avez des dates mal renseignées ça ne passera pas.
- filtrer sur cette clause : where datenreg between to_char(sysdate - 5, 'YYMMDD') and to_char(sysdate, 'YYMMDD').
  Considérant que vos dates ont été saisies sur ce format, elles sont triables donc cette formule reste correcte.
  Vous pouvez simplement indexer la colonne dateenr.
La meilleure solution étant de convertir la colonne dans la table mais j'ai bien compris que cette option n'était pas de votre ressors.