Requête pour les rappel sur 5 jour

**Mbrillon** · 19/04/2007, 20h24

Bonjour,

j'essaye depuis 1 semaine de trouver une solution, mais rien à y faire. Je veux trouver la liste de tous les rappel qui on eu leux dans les 5 jour suivant l'incident original.

Je m'explique, lorsque quelqu'un appel et que le problème n'est pas résolu après le première appel (première enregistement) je dois compté combien de ce genre de problème il y a eu.

Je voudrais que à chaque fois qu'on trouve un problème dans la table pouvoir regarder les 5 jours qui suivent et vérifier si il y a eu des problèmes semblable. Si non je continue au prochain jour. Si oui alors je recommence ma recherche après le dernier jour ou il y a eu un problème similar

J'ai essayer une requète hierachique, mais ceci me disait qu'il y avait des référence cyclique.

J'ai essayer des sous requètes, mais dans ce cas là je me retrouvais avec des doublons dans la sous requêtes et je ne pouvais pas trouver dans la requête principal, les problème qui se retrouvais aussi dans la sous requête (chose que je ne veux pas)

Avec un LEFT OUTER JOIN, je pouvais faire un select distinct, mais le problème est que encore là je ne peux pas retiré de la table principal tous les résultat que l'on retrouve dans la table secondaire.

Est-ce que quelque pourrais m'aider.

**phili_b** · 19/04/2007, 20h43

avec des exemples, une structure de table, les requêtes utilisées ça pourrait faciliter les choses

**Mbrillon** · 19/04/2007, 20h49

Ok

La table s'appel PROBLEMS

Je veux resortir les champ PROBLEM_ID, REPORT_DATE

Il faut que je sorte tous les problèmes qui on le même champ SYSTEM, COMPONENT, ITEM et IS_INVENTORY_ID. Il faut aussi que la REPORT_DATE soit égale ou supérieur ou problème original, mais ne dépasse par REPORT_DATE + 5

Je rattache cette table à une autre simplement pour avoir le nom de l'équipement au lieux d'un numéro, la table s'appel IS_INVENTORY, je ressort le champ ASSET_TAG et je rattache les deux table avec le champ IS_INVENTORY_ID

voici un aperçu d'une des requète hierachique avec laquelle j'avais le plus d'espoire

SELECT "PROBLEMS"."PROBLEM_ID", "PROBLEMS"."REPORT_DATE", "IS_INVENTORY"."ASSET_TAG", LEVEL

FROM "EXAV"."PROBLEMS" "PROBLEMS" INNER JOIN "EXAV"."IS_INVENTORY" ON

"PROBLEMS"."IS_INVENTORY_ID" ="IS_INVENTORY"."IS_INVENTORY_ID"

WHERE "PROBLEMS"."PROBLEM_CODE" <> 'ANNULE'
AND "PROBLEMS"."SYSTEM" IN ('PF MIC', 'PF FCDQ')
AND "PROBLEMS"."REPORT_DATE" >= SYSDATE -5
AND "PROBLEMS"."REPORT_DATE"< SYSDATE
CONNECT BY "PROBLEMS"."IS_INVENTORY_ID" = PRIOR "PROBLEMS"."IS_INVENTORY_ID"
AND "PROBLEMS"."SYSTEM" = PRIOR "PROBLEMS"."SYSTEM"
AND "PROBLEMS"."COMPONENT" = PRIOR "PROBLEMS"."COMPONENT"
AND "PROBLEMS"."ITEM" = PRIOR "PROBLEMS"."ITEM"
AND "PROBLEMS"."PROBLEM_ID" <> PRIOR "PROBLEMS"."PROBLEM_ID"
AND "PROBLEMS"."REPORT_DATE" >= PRIOR "PROBLEMS"."REPORT_DATE"
AND "PROBLEMS"."REPORT_DATE" < (PRIOR "PROBLEMS"."REPORT_DATE"+5)

Merci pour votre aide

**phili_b** · 19/04/2007, 23h30

Pas simple.
A première vue j'aurais tendance à utiliser une table Calendrier, comme cela on éviterait peut-être CONNECT BY.

Je ne peux pas l'affirmer à 100% mais j'ai l'impression qu'il y a trop de choses dans le CONNECT BY et qu'il ne faudrait faire la jointure avec la table IS_INVENTORY qu'une fois que le reste est résolu, ça complique les choses pour rien cette jointure.

**Mbrillon** · 20/04/2007, 14h01

Une table Calendrier??

Pour le lien a IS_INVENTORY, c'est pas nécessaire imédiatement c'est certain, mais je dois l'avoir éventuellement.

**McM** · 21/04/2007, 13h38

Est ce que cette requête convient à ton besoin ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM problems p
WHERE p.problem_code    <> 'ANNULE'
AND p.SYSTEM            IN ('PF MIC', 'PF FCDQ')
AND p.report_date       BETWEEN SYSDATE - 5 AND SYSDATE
AND (SELECT MIN(p1.report_date)
     FROM problems p1
     WHERE p1.SYSTEM          = p.SYSTEM
     AND p1.component         = p.component
     AND p1.item              = p.item
     AND p1.is_inventory_id   = p.is_inventory_id
     AND p1.problem_id        <> p.problem_id
     AND ) BETWEEN p.report_date - 5 AND p.report_date

**Mbrillon** · 23/04/2007, 17h09

No unfortunately not.

I'm looking at data from the last year 2006-03 to 2007-03.

-> Basically I need all problems
-- then for each problem I need to verify if within 5 days of that
problem there is any number of problems with the same PROBLEM
SYSTEM, ITEM as the first.
-- If there is none then I continue listing the problems
-> I then need to count the total number of problems (not counting the ones that fall under the "within 5 days" rule and count the number of problems that do have similar problems within 5 days.

The idea behind this is to find all problems that would not be properly resolved on first call.

Thanks for any help you could give me

**McM** · 23/04/2007, 18h49

A small test case would be nice.
Thanks

Exemple

DATE PROBLEM SYSTEM ITEM
01/01/2007 1 1 1
04/01/2007 1 1 1
05/01/2007 1 1 2
11/01/2007 1 1 2
12/01/2007 1 1 2
13/01/2007 1 1 1
14/01/2007 1 1 1
15/01/2007 1 1 1

**Mbrillon** · 23/04/2007, 21h14

Ok here is what I would like
The level field is what I was thinking about originally for a hiearchic table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
Problem ID      Date          System      Component       Item       Level
1        2006-03-01               1            1            5          1
2        2006-03-01               1            1            5          2
3        2006-03-04               1            1            5          2
4        2006-03-04               1            3            4          1
5        2006-03-09               1            1            5          1
6        2006-03-23               1            4            7          1
7        2006-03-27               1            4            7          2

**salim11** · 23/04/2007, 21h30

Salut,

Messieurs, on est dans un forum francophone.

**Mbrillon** · 23/04/2007, 21h46

Désolé, j'ai poster dans tellement de forum que des fois je me mélanger

Désolé tous le monde

**phili_b** · 23/04/2007, 22h02

ça donnerait quelque chose en oracle comme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM problems p1
inner join CALENDRIER c
ON (p1.report_date-c.jour)/5=trunc((p1.report_date-c.jour)/5)
inner JOIN problems p2 on 
 p1.SYSTEM          = p.SYSTEM
     AND p2.component         = p1.component
     AND p2.item              = p1.item
     AND p2.is_inventory_id   = p1.is_inventory_id
     AND p2.problem_id        <> p1.problem_id
    AND p2.report_date=c.jour
WHERE p1.problem_code    <> 'ANNULE'
AND p1.SYSTEM            IN ('PF MIC', 'PF FCDQ')

(attention je n'ai pas vérifié de près la jointure entre p1 et p2)

Explication
(tests et explication en SQL server car je n'ai pas oracle sous la main)

1)
Présentation de l'utilisation de la table calendrier avec affichage tout les 5 jours (autour du 1er janvier 2005 pour ne pas dérouler tout le calendrier):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select jour
from calendrier c
where jour between 
convert(datetime,'20041215',112) 
and convert(datetime,'20050115',112)-- <- only for test
and 
 cast((datediff(hour,convert(datetime,'20050101',112),c.jour)/24) as decimal)/5=
 cast((datediff(hour,convert(datetime,'20050101',112),c.jour)/24) as int)/5
 
jour                        
--------------------------- 
2004-12-17 00:00:00.000
2004-12-22 00:00:00.000
2004-12-27 00:00:00.000
2005-01-01 00:00:00.000
2005-01-06 00:00:00.000
2005-01-11 00:00:00.000
 
(6 ligne(s) affectée(s))

2)début de l'explication avec la table pubs.dbo.sales de sql server

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select s1.ord_date ,c.jour
from sales  s1
inner join calendrier c 
on  cast((datediff(hour,ord_date,c.jour)/24) as decimal)/5=
(datediff(hour,ord_date,c.jour)/24)/5
where stor_id=6380
 
1994-09-14 00:00:00.000     1995-08-20 00:00:00.000
1994-09-14 00:00:00.000     1995-08-25 00:00:00.000
1994-09-14 00:00:00.000     1995-08-30 00:00:00.000
1994-09-14 00:00:00.000     1995-09-04 00:00:00.000
1994-09-14 00:00:00.000     1995-09-09 00:00:00.000
1994-09-14 00:00:00.000     1995-09-14 00:00:00.000
1994-09-14 00:00:00.000     1995-09-19 00:00:00.000
1994-09-14 00:00:00.000     1995-09-24 00:00:00.000

3)avec les éléments suivants:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE [dbo].[calendrier] ([jour] [datetime] NOT NULL ) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[calendrier] WITH NOCHECK ADD 
	CONSTRAINT [PK_calendrier] PRIMARY KEY  NONCLUSTERED 
	([jour]	)  ON [PRIMARY] 
GO

remplissage du calendrier

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare 
  @dt_debut datetime,
  @dt_fin datetime,
  @dt_encours datetime
begin
 set @dt_debut=convert(datetime,'19900101',112)
 set @dt_fin=convert(datetime,'20100101',112)
 set  @dt_encours=@dt_debut
 
 while @dt_encours <@dt_fin
 begin
   print @dt_encours
    insert into calendrier (jour) select @dt_encours
   set @dt_encours =@dt_encours +1
 end
end

edit : précision : reste à rajouter quelque conditions entre p1 et p2

**salim11** · 23/04/2007, 22h04

salut,

Si j'ai bien compris, Si on a un probleme system=1 ,Component =1 , Item =5
qui date du 2006-03-07
alors le level sera de 3 ( c'est a dire c'est la troixième recherche que tu as fait )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Problem ID      Date          System      Component       Item       Level
1        2006-03-01               1            1            5          1
2        2006-03-01               1            1            5          2
3        2006-03-04               1            1            5          2
4        2006-03-04               1            3            4          1
5        2006-03-07               1            1            5          3
6        2006-03-09               1            1            5          1
7        2006-03-23               1            4            7          1
8        2006-03-27               1            4            7          2

**Mbrillon** · 23/04/2007, 22h24

Non je n'ai pas besoin de 3ième niveau, seulement 1 et 2 ième niveau

Pour l'explication avec une table Calendrier, j'ai jamais essayer ce genre de fonction et j'ai une peux de difficulté à comprendre comment ça fonctionne.

Pour votre information, je n'ai pas de droit d'écriture dans sur le serveur.

Merci encore pour tous votre aide

**lalystar** · 24/04/2007, 06h10

Bonjour,

Et avec un lead ? :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
select  problem_id, report_date
from
   (
      select  problem_id, report_date, lead(report_date) over(partition by  problem, system, item order by report_date) next_call
      from    PROBLEMS
   )
where next_call - report_date <= 5;

Laly.