Pbm de requête : Utilisation d'une requête récursive ?

Version imprimable

20/05/2016, 10h54
akli_agha

Pbm de requête : Utilisation d'une requête récursive ?

Bonjour,

Voilà, j'ai une table COTIS avec comme champs : ID_EMP, MOIS_COTIS et MONTANT. Elle contient les cotisations mensuelles des employés.

La requête que je n'arrive pas à mettre en oeuvre est la suivante :

Savoir si un employé à cotisé pour les mois : JANVIER, FEVRIER, MARS ET AVRIL en ayant comme entrés l'Id de l'employé, le mois de débu (Janvier)) et le mois de la fin (Avril)

Merci d'avance,

W7, D7, FB2.5

Bonjour,

Cela aurait été sympa (et plus rapide) de nous fournir un script de création de tables et un jeu d'essai
comme par exemple ceci (perfectible car pas d'index)

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
 
CREATE TABLE Employes
( ID_EMP INTEGER NOT NULL,
  NOM VARCHAR(20)
);
CREATE TABLE COTISATIONS
(ID_EMP INTEGER NOT NULL,
 MOIS SMALLINT NOT NULL,
 MONTANT NUMERIC(15,2)
);
 
COMMIT;
INSERT INTO EMPLOYES VALUES (1,'toto tout');
INSERT INTO EMPLOYES VALUES (2,'tata partiel');
INSERT INTO EMPLOYES VALUES (3,'titi rien');
 
INSERT INTO COTISATIONS VALUES(1,1,10); -- paiement en 2 fois
INSERT INTO COTISATIONS VALUES(1,1,10); -- 2° paiement
INSERT INTO COTISATIONS VALUES(1,2,20);
INSERT INTO COTISATIONS VALUES(1,3,20);
INSERT INTO COTISATIONS VALUES(1,4,20);
INSERT INTO COTISATIONS VALUES(2,1,20);
-- pas de paiement de l'employé 2 mois 2      
INSERT INTO COTISATIONS VALUES(2,3,10); -- paiement en 2 fois
INSERT INTO COTISATIONS VALUES(2,3,10); -- 2° paiement
INSERT INTO COTISATIONS VALUES(2,4,20);

Comme j'aime bien scinder les problèmes voici ma solution, utilisant à la fois une requête récursive et une CTE

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
-- Liste des employes n'ayant pas payé de cotisation entre les mois 1 et 4 
WITH 
   -- récupérer les mois voulus (il suffiras de remplacer par des paramètres)
   RECURSIVE MONTHS(M) AS (select 1 AS M from RDB$DATABASE   -- 1 à remplacer par paramètre mois de début
                                    union all 
                                    select m+1 from MONTHS 
                                    where m < 4                                      -- 4 à remplacer par paramètre mois de fin 
                             ),
  -- récupérer les couples mois/employé (tous les employés pour tous les mois) 
    EMPMOIS AS (SELECT MONTHS.M,E.ID_EMP FROM MONTHS FULL JOIN EMPLOYES E ON 1=1)
-- Obtenir les employés n'ayant rien versé      
SELECT E.M,E.ID_EMP,C.MONTANT FROM EMPMOIS E LEFT JOIN COTISATIONS C ON E.ID_EMP=C.ID_EMP AND E.M=C.MOIS
WHERE MONTANT IS NULL

Cette requête est perfectible, je suis sûr que l'on peut faire plus "condensé", mais elle fonctionne

Salut à tous.

Citation:

Envoyé par akli_agha

Savoir si un employé à cotisé pour les mois : JANVIER, FEVRIER, MARS ET AVRIL en ayant comme entrés l'Id de l'employé, le mois de début (Janvier)) et le mois de fin (Avril).

Ce que tu demandes est assez facile à faire.
Si un employé donné a cotisé durant quatre mois, de janvier (1) à avril (4), normalement, le nombre de lignes dans la table sera de quatre.

pour ce faire, j'ai fait une procédure stockée avec les paramètres suivants :
--> identifiant de l'employé
--> début du mois en nombre
--> fin du mois en nombre

J'ai mis aussi une contrainte, la clef primaire est constitué du couple (id_emp ; mois_cotis).

L'employé '1' a cotisé durant 5 mois sans interruption.
L'employé '2' a des trous durant la même période.

S'il n'y a pas de trous, le résultat sera 'T' pour TRUE, c'est-à-dire VRAI.
S'il y a des trous, le résultat sera 'F' pour FALSE, c'est-à-dire FAUX.

Voici ce que cela donne :

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET ISO8859_1;
 
-- ============================
-- Création de la table 'cotis'
-- ============================
 
CREATE TABLE cotis (
  ID_EMP      INTEGER       NOT NULL,
  MOIS_COTIS  SMALLINT      NOT NULL,
  MONTANT     DECIMAL(15,2) NOT NULL,
  PRIMARY KEY (ID_EMP,MOIS_COTIS)
);
 
-- ======================
-- Insertion dans 'cotis'
-- ======================
 
insert into cotis (id_emp, mois_cotis, montant) values (1, 1, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 2, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 3, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 4, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 5, 100.00);
 
insert into cotis (id_emp, mois_cotis, montant) values (2, 1, 150.00);
insert into cotis (id_emp, mois_cotis, montant) values (2, 4, 150.00);
insert into cotis (id_emp, mois_cotis, montant) values (2, 5, 150.00);
 
-- =================
-- Vidage de 'cotis'
-- =================
 
select * from cotis;
 
      ID_EMP MOIS_COTIS               MONTANT
============ ========== =====================
           1          1                100.00
           1          2                100.00
           1          3                100.00
           1          4                100.00
           1          5                100.00
           2          1                150.00
           2          4                150.00
           2          5                150.00
 
 
-- ==================
-- Creation Procédure
-- ==================
 
SET TERM $;
create procedure test(emp integer,
                      deb smallint,
                      fin smallint)
returns (result char(01))
as
  declare diff smallint;
  declare calc smallint;
begin
  diff = fin - deb + 1;
 
  select count(distinct mois_cotis)
  from   cotis
  where  id_emp = :emp
    and  mois_cotis between :deb and :fin
  group by id_emp
  into calc;
 
  if (diff = calc) then result = 'T'; else result = 'F';
  suspend;
end$
SET TERM ;$
 
-- ===================
-- Execution procédure
-- ===================
 
select * from test(1, 1, 4);
 
RESULT
======
T
 
select * from test(2, 1, 4);
 
RESULT
======
F
 
 
exit;
 
Appuyez sur une touche pour continuer...

Le résultat que nous proposons dépend essentiellement de tes explications.
Une simplification de ton problème n'est pas la garantie d'avoir le résultat que tu recherches.

@+

22/05/2016, 07h00
SergioMaster

Bonjour,

en fait ce qui me chiffonne le plus dans cette table COTIS c'est l'indication d'un MOIS au lieu d'une date certainement beaucoup plus appropriée dés qu'il va s'agir d'une utilisation sur la durée.
23/05/2016, 09h35
Artemus24

Bonjour SergioMaster.

Oui, en effet, tu as raison. Cette notion de mois est assez floue.
Il faudrait que le mois soit composé de l'année + mois, par exemple "201605".

@+
30/05/2016, 18h13
akli_agha

Excusez les amis, j'ai été cloué un bon bout de temps .

Au fait, j'ai mis mois mais en réalité ce sont des cotisations par année !
Je n'ai pas cru que vous alliez y prêter attention !

Désolé !
31/05/2016, 07h14
SergioMaster

Bonjour,

Citation:

Envoyé par akli_agha

Je n'ai pas cru que vous alliez y prêter attention !

ne pas y prêter attention alors que c'était l'occasion de tester une requête récursive ? c'est mal me connaitre

Citation:

Envoyé par akli_agha

Au fait, j'ai mis mois mais en réalité ce sont des cotisations par année !

cela ne change pas grand chose, la seule chose qui à mon sens serait pertinente c'est d'indiquer si par employé il n'y a qu'une seule cotisation par critère (par an donc).

l'hypothèse pour moi était 0,n cotisations par employé par mois (ou année) et je reste persuadé qu'une requête récursive plus compacte est possible, mais sans réponse à ma première proposition j'avais laissé tomber
31/05/2016, 13h47
akli_agha

Merci Sergio.

Tant mieux pour votre ténacité !

Pour les cotisations, c'est 0, 1 cotisation par année. Soit l'employé paie soit le contraire.

Et merci pour tout.
31/05/2016, 17h10
Artemus24

Salut à tous.

Citation:

Envoyé par SergioMaster"

cela ne change pas grand chose, la seule chose qui à mon sens serait pertinente c'est d'indiquer si par employé il n'y a qu'une seule cotisation par critère (par an donc).

En fait, la question est mal posée.
Imaginons qu'une personne trouvant le montant de cette cotisation un peu trop élevé, paye en plusieurs fois.
La question est de savoir, si à la date d'échéance d'une période, cette personne s'est acquittée de la totalité de sa cotisation.

Ensuite, il faut vérifier si cette personne s'est acquittée de toutes ses cotisations et ce, sur plusieurs périodes.
Ce que j'ai cherché à faire est de vérifier s'il y a, par exemple sur quatre périodes, bien quatre cotisations.
Autrement dit, une cotisation par période, et s'il y a quatre période, il y a quatre cotisations.

@ akli_agha : j'aimerai savoir si nos solutions sont satisfaisantes ou pas ?
Et en quoi on peut l'améliorer pour répondre à votre attente.

@+
31/05/2016, 17h22
akli_agha

Bien évidemment que les que les solutions proposées sont satisfaisantes et je vous en remercie !

Pour revenir à notre soucis, l'employé paie en totalité sa cotisation. Il ne peut pas la payer en plusieurs tranches

Encore une fois, meric.

Bonjour,

Citation:

Pour revenir à notre soucis

le tien ;)

Citation:

l'employé paie en totalité sa cotisation. Il ne peut pas la payer en plusieurs tranches

pas du jeu, alors que j'avais complexifié mon jeu d'essai
- ajouter une date de versement
- l'employé peut payer en plusieurs tranches
et pensais rajouter les employés n'ont pas le même montant de cotisations ;) de cette manière
pour trouver les employés en retard (n'ayant pas payé ou en partie) de cotisation entre janvier et avril

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
 
-- modifications de mon jeu d'essai
-- une clé primaire
ALTER TABLE COTISATIONS ADD 
id_rec BigInt;
-- une contrainte référentielle
alter table COTISATIONS
add constraint FK_COTISATIONS_1
foreign key (ID_EMP) 
references EMPLOYES (ID_EMP);
 
-- générateur pour la clé primaire
CREATE GENERATOR GEN_COTISATIONS_ID;
-- trigger clé
SET TERM !! ;
CREATE TRIGGER COTISATIONS_BI FOR COTISATIONS
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.id_rec IS NULL) THEN
    NEW.id_rec = GEN_ID(GEN_COTISATIONS_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_COTISATIONS_ID, 0);
    if (tmp < new.id_rec) then
      tmp = GEN_ID(GEN_COTISATIONS_ID, new.id_rec-tmp);
  END
END!!
SET TERM ; !!
ALTER TABLE COTISATIONS ADD 
DATE_VERSEMENT Date NOT NULL;
 
alter table EMPLOYES
add constraint PK_EMPLOYES_1
primary key (ID_EMP);
 
ALTER TABLE EMPLOYES ADD 
MONTANT_COTISATION Numeric(15,2) NOT NULL;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
WITH RECURSIVE MONTHS(M) AS (select 1 AS M from RDB$DATABASE
                                    union all 
                                    select m+1 from MONTHS 
                                    where m < 4
                             ),
     EMPMOIS AS (SELECT MONTHS.M,E.ID_EMP FROM MONTHS FULL JOIN EMPLOYES E ON 1=1)
 
SELECT E.M AS MOIS,E.ID_EMP AS EMPLOYE,SUM(COALESCE(C.MONTANT,0)) AS MONTANT 
FROM EMPMOIS E LEFT JOIN COTISATIONS C ON E.ID_EMP=C.ID_EMP 
                                       AND E.M=EXTRACT(MONTH FROM C.DATE_VERSEMENT)
GROUP BY 1,2
HAVING SUM(COALESCE(C.MONTANT,0))=0 --

reste que je suis persuadé que l'on peut 'compacter' ça , aussi tôt pensé aussitôt fait
voici la nouvelle partie récursive

Code:

1
2
3
4
5
WITH RECURSIVE MONTHS(MOIS,ID_EMP) AS (select 1 AS MOIS,E.ID_EMP from RDB$DATABASE full join EMPLOYES E on 1=1
                                    union all 
                                    select MOIS+1,ID_EMP from MONTHS 
                                    where MOIS < 4
                             )

@artemus on peut bien sûr mettre le code dans une procédure pour avoir les mois début et fin en paramètres

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
 
SET TERM ^ ;
 
CREATE PROCEDURE COTISATION_NON_VERSEES 
 ( DEBUT SMALLINT,
   FIN SMALLINT ) 
RETURNS 
 ( MOIS SMALLINT,
   ID_EMPLOYE SMALLINT)
AS 
BEGIN
FOR
 WITH RECURSIVE MONTHS(MOIS,ID_EMP) AS (select :DEBUT AS MOIS,E.ID_EMP from RDB$DATABASE full join EMPLOYES E on 1=1
                                    union all 
                                    select MOIS+1,ID_EMP from MONTHS 
                                    where MOIS <:FIN)
 SELECT E.MOIS,E.ID_EMP 
 FROM MONTHS E LEFT JOIN COTISATIONS C ON E.ID_EMP=C.ID_EMP 
                                       AND E.MOIS=EXTRACT(MONTH FROM C.DATE_VERSEMENT)
 GROUP BY 1,2
 HAVING SUM(COALESCE(C.MONTANT,0))=0 
 INTO :MOIS,ID_EMPLOYE
 DO SUSPEND;                            
END^
 
SET TERM ; ^

exécution

Code:

SELECT p.MOIS, p.ID_EMPLOYE FROM COTISATION_NON_VERSEES(1, 4) p

@akli_agha si à la place des mois il s'agit d'années le principe reste le même, mais si je relis ton premier message, tu y parles bien de mois
donc soit la fièvre t'as fait délirer

Citation:

j'ai été cloué un bon bout de temps

au lit ?
soit le "soucis" a été mal posé.

en tout cas cela m'a permis de manipuler pour un exemple assez concret les requêtes récursives et je t'en remercie
[Edit] c'est pour la raison ci-dessus, que je me suis permis de modifier le titre, afin que d'autres soient intéressés

quelques regrets encore, mais faciles à corriger
-les paramètres de la procédure ne sont pas contrôlés
-il n'y a pas de notion d'année pour les mois (je suis parti sur un ensemble de données d'une année et pas plus)
il faudrait donc rajouter au moins un paramètre d'entrée à la procédure (ANNEE) et une condition à la jointure avec COTISATIONS (sur l'année justement)

Mais je pense que le canevas est plus que ébauché !