Soustraction de date

Version imprimable

13/02/2009, 12h20
tutomania

Soustraction de date

Bien le bonjour à tous !

Je viens solliciter votre aide.

Je suis en train de mettre un place un outil de statistiques dans mon entreprise en PHP/Oracle.

Le projet est presque terminé, cependant je me prend sévèrement la tête sur certaines requêtes lié à des champs "date".

Voila ce que je dois faire:

Je dois calculer le délai d'ouverture d'un dossier mais en faisant une soustraction du temps ou le dossier à été mis en "Pending".

Donc pour récupérer le délai total dans un premier temps je fais un simple date_debut - date_fin, ce qui me retourne une durée en 0,45245454.

Ce résultat j'arrive à le transformer en 'minutes' ou 'heures + minutes' ou 'jour + heure + min'.

Ces deux champs sont au format DATE > DD/MM/YYYY HH24:MM:SS

Ma problèmatique réside dans le fait que je dois ensuite soustraire à cette durée une durée de pendind qui est au format DATE mais qui ressemble à ceci : 1 00:00:00 => Qui correspond à 1 journée.

La finalité est de trouver un temps total d'ouverture inférieur à une duréé prédéfini :

(date_debut - date_fin) - temps_pending < 'durée en minutes'

Je ne vous cache pas que je me prend bien la tête la dessus.
Si quelqu'un aurait une idée j'en serait extrement ravi

Merci à tous et bon week end.

----------
Tutomania
----------

La durée de votre pending est au format INTERVAL DAY TO SECOND.
Vous pouvez tout à fait convertir une différence de date à ce format là, et ensuite faire la soustraction.

Il suffit ensuite de faire la comparaison avec votre durée en minutes, convertie elle aussi en intervale.

Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 select (dt_fin - dt_deb) DAY TO SECOND - pending as duree_eff, duree_type, case when (dt_fin - dt_deb) DAY TO SECOND - pending < duree_type then 'OK' else 'KO' end u from -- ce from est pour que les formules du dessus soient plus simples à lire ( select to_date('13/02/2009 12:40:00', 'dd/mm/yyyy hh24:mi:ss') dt_fin, to_date('12/02/2009 11:22:00', 'dd/mm/yyyy hh24:mi:ss') dt_deb, INTERVAL '1 00:00:00' DAY TO SECOND pending, INTERVAL '90' MINUTE duree_type from dual )

Ah voilà j'ai retrouvé les fonctions de conversion qui me manquaient, c'est NUMTODSINTERVAL et TO_DSINTERVAL :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
    (dt_fin - dt_deb) DAY TO SECOND - TO_DSINTERVAL(pending) as duree_eff,
    NUMTODSINTERVAL(duree_num, 'MINUTE') duree_typ,
    case
      when (dt_fin - dt_deb) DAY TO SECOND - TO_DSINTERVAL(pending) < NUMTODSINTERVAL(duree_num, 'MINUTE')
      then 'OK' else 'KO'
    end u
from -- ce from est pour que les formules du dessus soient plus simples à lire
(    
    select
        to_date('13/02/2009 12:40:00', 'dd/mm/yyyy hh24:mi:ss') dt_fin,
        to_date('12/02/2009 11:22:00', 'dd/mm/yyyy hh24:mi:ss') dt_deb,
        '1 00:00:00' pending,
        90 duree_num
    from dual
)

13/02/2009, 15h50
tutomania

Merci beaucoup pour ta réponse Waldar.
J'essaye tout ça dès que possible et te reviens.

En tous les cas merci pour cette réponse rapide et claire.

Bon week end.

Rebonjour à tous, je me suis replongé dans le code et ça va pas de tout du tout :)

Bon et bien en fait c'est beaucoup plus compliqué que ça, attention les yeux on ne rigole plus la :D

Voila ce que je dois faire :

Compter le nombre d'incidents résolu dans les temps en soustrayant les délais de suspension de chaque incidents (plusieurs délai de suspension possible par incident).

Voila ce que j'ai réussi à faire pour le moment, c'est compter le nombre d'incidents dont le TOTAL_TIME - LE PENDING , le tout transformer en minutes et < à une durée donnée.
Code:

1 2 3 4 5 6 7 8 9 10 11 select count(NUMBERPRGN) as total from (SELECT PROBSUMMARYM1.NUMBERPRGN,(nvl(to_number((EXTRACT(MONTH FROM c2.CLOSED_TOTAL)-1 )*40500)+((EXTRACT (DAY FROM c2.CLOSED_TOTAL)-1)*1350)+((EXTRACT (HOUR FROM TO_TIMESTAMP( TO_CHAR (c2.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )))*60) +EXTRACT (MINUTE FROM TO_TIMESTAMP(TO_CHAR (c2.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )),0) - -- <-- C'est un moins (-) nvl(to_number((EXTRACT(MONTH FROM c4.CLOSED_TOTAL)-1 )*40500)+ ((EXTRACT (DAY FROM c4.CLOSED_TOTAL)-1)*1350)+((EXTRACT (HOUR FROM TO_TIMESTAMP( TO_CHAR (c4.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )))*60) +EXTRACT (MINUTE FROM TO_TIMESTAMP( TO_CHAR (c4.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )),0) ) as TOT FROM servicec.PROBSUMMARYM1 left join servicec.CLOCKSM1 c2 on PROBSUMMARYM1.NUMBERPRGN = c2.KEY_CHAR and c2.NAME='Total temps passé' left join servicec.CLOCKSM1 c4 on PROBSUMMARYM1.NUMBERPRGN = c4.KEY_CHAR and c4.NAME = 'GROUPE-Pending' where PROBSUMMARYM1.CLOSE_TIME IS NOT NULL And priority_code ='4' and cly_group_suivi_hd = 'SERVICE DESK' AND PROBSUMMARYM1.CLOSE_TIME between '01/02/2009' AND '28/02/2009' and (c2.name is not null or c4.name is not null)) where tot < 840
La grosse problèmatique est qu'il peut y avoir dans la table clocksm1 plusieurs enregistrements de type pending. Il faut donc que j'additionne tous les enregistrements Like '%-Pending' pour chaque incident et le soustraire à son total_time.

Ma question est comment additionner tous les pendings pour chaque incident ?

Le casse tête de fou !!!

les deux tables sont ProbsummaryM1 pour les incidents et clocksM1 pour les délais. La liaison entre les deux tables est le champ numberprgn de Probsummary et key_char de clocksM1.

Dans clocksM1 il y a un champ qui se nomme 'name' dans lequel on va retrouver une ligne pour "Total temps passé" et une ou plusieurs lignes pour les -Pending ('%-Pending')

Exemple :

KEY_CHAR | NAME | CLOSED_TOTAL
IM10XXX | Total Temp passé | 1 00:00:00
IM10XXX | groupe_Pending | 14:00:00
IM10XXX | groupe1_Pending | 3:00:00
IM10ZZZ | groupe2_Pending | 3:00:00

Par exemple ci dessus pour IM10XXX on a un Total temps passé de 1 journée et deux entrées %-Pending (14h + 3h donc 17h)
Total Temp passé - (groupe_Pending+groupe1_Pending) = 7 heures

A l'AIDDDEEEEEE !! HELP :D

Houla, déjà on reformate un peu la requête car c'est illisible :

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
SELECT
    count(num) AS total
FROM
(
    SELECT
        p1.NUMBERPRGN,
        (nvl(to_number((EXTRACT(MONTH FROM c2.CLOSED_TOTAL)-1 )*40500)+((EXTRACT (DAY FROM c2.CLOSED_TOTAL)-1)*1350)+((EXTRACT (HOUR FROM TO_TIMESTAMP( TO_CHAR (c2.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )))*60) +EXTRACT (MINUTE FROM TO_TIMESTAMP(TO_CHAR (c2.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )),0)  
         - -- <-- C'est un moins (-)
         nvl(to_number((EXTRACT(MONTH FROM c4.CLOSED_TOTAL)-1 )*40500)+((EXTRACT (DAY FROM c4.CLOSED_TOTAL)-1)*1350)+((EXTRACT (HOUR FROM TO_TIMESTAMP( TO_CHAR (c4.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )))*60) +EXTRACT (MINUTE FROM TO_TIMESTAMP(TO_CHAR (c4.CLOSED_TOTAL, 'DD/MM/YY HH24:MI:SS'), 'DD/MM/YY HH24:MI:SS' )),0) 
        ) AS TOT
    FROM
        servicec.PROBSUMMARYM1 p1
        LEFT JOIN servicec.CLOCKSM1 c2
          ON p1.numberprgn = c2.key_char
         AND c2.NAME = 'Total temps passé'
        LEFT JOIN servicec.CLOCKSM1 c4
          ON p1.numberprgn = c4.key_char
         AND c4.name = 'GROUPE-Pending'
    WHERE
        p1.close_time IS NOT NULL 
    AND priority_code ='4'
    AND cly_group_suivi_hd = 'SERVICE DESK'
    AND p1.close_time BETWEEN '01/02/2009' AND '28/02/2009'
    and (c2.name is not null or c4.name is not null)
)
WHERE
    tot < 840

Vous faites des to_number sur des nombres. Soit, mais c'est inutile.
Vous convertissez une date en texte puis en timestamp pour faire un extract.
Extract fonctionne déjà sur les dates.

D'où sortent votre *40500 sur un mois ?
Si ce sont des minutes, ça fait des mois de 28.125 jours, ce qui est bien évidement faux, ce qui rend votre requête fausse.

CLOSED_TOTAL de la table CLOCKSM1 est de quel type, date ?
Pour éviter toute interprétation merci de copier / coller le script DDL de cette table !

20/02/2009, 19h38
tutomania

Bonsoir Waldar.

Alors voici les réponses que tu attends (enfin j'espère).

D'où sortent votre *40500 sur un mois ?
En fait c'est basé sur un Calendrier spécial et non un calendrier grégorien
une journée c'est 14h (de 7h à 21h) et non 24h.

CLOSED_TOTAL de la table CLOCKSM1 est de quel type, date ?
Nom du champ - Type
closed_total - Date/Time

Pour éviter toute interprétation merci de copier / coller le script DDL de cette table !
Je ne sais pas ce que c'est :oops:
20/02/2009, 20h01
Waldar
Ah oui mais bon, si vous n'êtes pas sur la même échelle de temps que le reste du monde ça se complique car vous vous privez de toute opération sur les dates (soustraire deux dates donne un résultat en nombre de jours).

Pouvez-vous passer la requête suivante :
Code:

1 2 3 select key_char, name, to_char(closed_date, 'dd/mm/yyyy hh24:mi:ss') from servicec.CLOCKSM1 where key_char = 'IM10XXX'
20/02/2009, 20h21
tutomania
C'etais closed_total que vous désiriez je pense :
Code:

1 2 3 SELECT key_char, name, to_char(closed_total, 'dd/mm/yyyy hh24:mi:ss') FROM servicec.CLOCKSM1 WHERE key_char = 'IM100';
Ce qui nous renvoie ceci :

KEY_CHAR NAME TO_CHAR(CLOSED_TOTA
-------------------------------------------------- -------------------------------------------------- -------------------
IM42813 Total temps passé 02/01/4000 05:04:36
IM42813 SPT 25D-Pending 01/01/4000 16:41:08
IM42813 SPT 9D-Pending 01/01/4000 02:41:08

Pour calculer le temps passé il démarre au '01/01/4000 00:00:00'
24/02/2009, 13h55
tutomania

Bonjour,

Quelqu'un aurait une petite idée de la requête à construire par hasard ?

Excellente journée à tous.

Le problème c'est votre calendrier non standard, ça empêche les solutions classiques car il faudrait un développement spécifique rien que pour ça.

Néanmoins sur votre exemple, si ça peut vous aider, il faut toujours travailler avec des valeurs "fixe", comme les jours / heures / minutes / secondes car ça ne bouge pas (contrairement à un mois).

Là je vais travailler avec des secondes :

Code:

1
2
3
4
5
6
7
8
create table PROBSUMMARYM1 (numberprgn varchar2(7));
create table CLOCKSM1 (KEY_CHAR varchar2(7), NAME varchar2(30), closed_total date);
 
insert into CLOCKSM1 values ('IM42813', 'Total temps passé', to_date('02/01/4000 05:04:36', 'dd/mm/yyyy hh24:mi:ss'));
insert into CLOCKSM1 values ('IM42813', 'SPT 25D-Pending'  , to_date('01/01/4000 16:41:08', 'dd/mm/yyyy hh24:mi:ss'));
insert into CLOCKSM1 values ('IM42813', 'SPT 9D-Pending'   , to_date('01/01/4000 02:41:08', 'dd/mm/yyyy hh24:mi:ss'));
insert into PROBSUMMARYM1 values ('IM42813');
commit;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select
    p1.NUMBERPRGN,
    (to_number(to_char(c2.closed_total, 'J'))-to_number(to_char(to_date('01/01/4000', 'dd/mm/yyyy'), 'J')))*86400 + to_number(to_char(c2.closed_total, 'SSSSS'))
     -
    nvl(c4.fin, 0) as duree
FROM
    PROBSUMMARYM1 p1
    LEFT JOIN CLOCKSM1 c2
      ON p1.numberprgn = c2.key_char
     AND c2.NAME = 'Total temps passé'        
    LEFT JOIN
    (
      select
        key_char,
        sum((to_number(to_char(closed_total, 'J'))-to_number(to_char(to_date('01/01/4000', 'dd/mm/yyyy'), 'J')))*86400 + to_number(to_char(closed_total, 'SSSSS'))) as fin
      from CLOCKSM1
      where name like '%Pending'
      group by key_char
    ) c4 ON p1.numberprgn = c4.key_char

Code:

1
2
NUMBERPRGN	DUREE
IM42813		34940

34940 secondes valant 9h 42min et 20sec.

25/02/2009, 16h41
tutomania

Bonjour waldar,

Excellent les résultats ont l'air d'etre cohérent.
Ils faut que je fasse divers tests entre les résultats et l'applications et je te reviens Asap :)

Merci énormément pour ton suivi.
A toute. :mouarf:
25/02/2009, 17h29
Waldar

Attention car j'ai utilisé des dates qui ont des journées de 24h, à mon avis il faudra que vous l'adaptiez à vos journées de 17h.
06/03/2009, 12h55
tutomania

Bon les requêtes sont en prod et fonctionnelles :)
Merci pour tout Waldar :yaisse2:
06/03/2009, 14h47
Waldar

De rien ! Comment avez-vous adapté aux journées de 17h ?
Vous enlevez 7h par jour au-delà du premier ?