Discussion :

[Khenshiro_o]

Bonjour,

J'ai un problème avec une requête que je n'arrive pas à faire.
Mon besoin est le suivant :

Dans un contexte métier lié à la paie je souhaiterai regrouper des montants en fonction de plusieurs critères/dates.
Les paramètres d'entrés sont une date de début @DATE_DEBUT et une date de fin @DATE_FIN qui définisse une période (une année fiscale)
J'ai besoin de récupérer des montants retro (dont la date de paie et la date d'allocation sont différentes ) qui sont antérieur à cette période mais dont la paie est calculée sur la période en cours (définit par les 2 paramètres)
Je dois également regrouper séparément les montants retro sur la période en cours si MIN(DATE_ALLOC) < DATE_DEBUT, donc si la date d'allocation la plus ancienne est antérieur à la période en cours.

La requête peut se décomposer en 2 sous requêtes je pense :

La première retourne des montants retro < DATE_DEBUT

Code :

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

1
2
3
4
5
6
7
select ID_HR,OR_HR,SUM(MONTANT_A),SUM(MONTANT_B), CRITERE A, CRITERE B
from TABLE A
where DATE_ALLOC <> DATE_PAIE
and DATE_PAIE >= @DATE_DEBUT
and DATE_PAIE <= @DATE_FIN
and DATE_ALLOC < @DATE_DEBUT
GROUP BY ID_HR,OR_HR, CRITERE_A, CRITERE_B

la deuxième retourne les montants retro sur la période en cours SEULEMENT si le MIN(DATE_ALLOC) < DATE_DEBUT

Code :

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

1
2
3
4
5
6
7
8
select ID_HR,OR_HR,SUM(MONTANT_A),SUM(MONTANT_B), CRITERE A, CRITERE B
from TABLE A
where DATE_ALLOC <> DATE_PAIE
and DATE_PAIE >= @DATE_DEBUT
and DATE_PAIE <= @DATE_FIN
and DATE_ALLOC >= @DATE_DEBUT 
AND MIN(DATE_ALLOC) < @DATE_DEBUT
GROUP BY ID_HR,OR_HR, CRITERE_A, CRITERE_B

Alors évidement la requête n'est pas correct et il n'est pas possible de mettre un MIN dans un where mais c'est juste pour illustrer le besoin.

Merci d'avance si quelqu'un peut m’éclairer.

[escartefigue]

bonsoir,

Vous devriez communiquer un exemple de jeu de données en entrée et un exemple de résultat attendu en sortie
Précisez également votre SGBD et sa version

[Khenshiro_o]

Voici un exemple avec les données en entrée

avec DATE_DEBUT = '2016-12-01' et DATE_FIN = '2017-11-30'

ID_HR	DATE_ALLOC	DATE_PAIE	MONTANT_A	SUM(MONTANT_A)
M10503	25/09/2016 00:00	25/04/2017 00:00	1200	3600
M10503	25/10/2016 00:00	25/04/2017 00:00	1200
M10503	25/11/2016 00:00	25/04/2017 00:00	1200
M10503	25/12/2016 00:00	25/04/2017 00:00	1200	4800
M10503	25/01/2017 00:00	25/04/2017 00:00	1200
M10503	25/02/2017 00:00	25/04/2017 00:00	1200
M10503	25/03/2017 00:00	25/04/2017 00:00	1200
M10503	25/04/2017 00:00	25/04/2017 00:00

Les montants doivent être sommés selon ce principe.

Le montant à 3600 est un regroupement correspondant aux montants retro antérieur à la période en cours
Le montant à 4800 est un regroupement correspondant aux montants retro sur la période en cours qui ne doivent être comptabilisés QUE si MIN(DATE_ALLOC) < '2016-12-01' .En l’occurrence ici MIN(DATE_ALLOC) = '2016-09-25' donc c'est bon.

exemple qui ne doit pas être extrait :

ID_HR	DATE_ALLOC	DATE_PAIE	MONTANT_A
M10504	25/12/2016 00:00	25/04/2017 00:00	1200
M10504	25/01/2017 00:00	25/04/2017 00:00	1200
M10504	25/02/2017 00:00	25/04/2017 00:00	1200
M10504	25/03/2017 00:00	25/04/2017 00:00	1200
M10504	25/04/2017 00:00	25/04/2017 00:00

Je suis sous oracle 12

[escartefigue]

Je ne comprends : pas quel est le critère qui permet de savoir quelles sont les lignes à regrouper ?

Dans votre exemple, vous sommez les lignes 1 à 3, c'est à dire du 25-09-2016 au 25-11-2016 inclus, pourquoi ne pas prendre également la ligne 4 elle aussi positionnée en 2016 ?

[Khenshiro_o]

Les montants sont regroupés en fonction de l'année fiscale mais en décalé.
Une "période" est définit entre le 01/12/N-1 et le 30/11/N où N est l'année choisi.
Les 3 premières lignes correspondent à la période antérieur donc 01/12/N-2 et 30/11/N-1 tandis que la 4e correspond à la période en cours le 01/12/N-1 et le 30/11/N.
C'est pour cela que j'ai besoin de les regrouper séparément.

[tbc92]

Je n'ai pas tout compris, mais une requête comme ça devrait t'inspirer un peu :

Code :

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

1
2
3
4
5
6
7
select  ID_HR,OR_HR, CRITERE_A, CRITERE_B, 
sum (  case when date_paie = date_alloc  then montant_A else 0 end ) as  montant_1, 
sum ( case when date_paie > date_alloc  then montant_A else 0 end ) as montant_retro
from table A 
WHERE   DATE_PAIE between  @DATE_DEBUT and  @DATE_FIN
and DATE_ALLOC >= @DATE_DEBUT 
group by ID_HR,OR_HR, CRITERE_A, CRITERE_B

Et sinon, tu dis : on ne peut pas avoir de min() dans la clause WHERE.
C'est vrai, mais il y a le HAVING qui permet de faire cela :

Code :

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

1
2
3
4
5
6
7
select  ID_HR,OR_HR, CRITERE_A, CRITERE_B, 
sum ( case when date_paie = date_alloc  then montant_A else 0 end  ) as  montant_1, 
sum ( case when date_paie > date_alloc  then montant_A else 0 end ) as montant_retro
from table A 
WHERE   DATE_PAIE between  @DATE_DEBUT and  @DATE_FIN
group by ID_HR,OR_HR, CRITERE_A, CRITERE_B
having min(date_alloc) >= @DATE_DEBUT

Ca revient à exécuter la requête sans la dernière ligne, puis à filtrer et afficher uniquement les lignes qui ont min(Date_Alloc) >= @Date_debut.
Bien entendu, pour que ça ait un sens, on ne met plus la condition and date_alloc >= @Date_Debut dans le Where.

[escartefigue]

Autre possibilité : créer une table donnant les dates de début de chaque période et un n° de période, opération simple via une requete récursive, puis faire une jointure avec votre table de données en cumulant par n° de période.

[Khenshiro_o]

On y est presque.

Voici un prototype avec DATE_DEBUT = '2016-12-01' et DATE_FIN = '2017-11-30'

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select SCO_ID_HR, SCO_OR_HR_PERIOD,
sum ( case when SCO_DT_ALLOC >= '2016-12-01'  then CFR_AMT_MONTANT_PRINCIPAL else 0 end ) as  montant_current_per, 
sum ( case when SCO_DT_ALLOC <   '2016-12-01'  then CFR_AMT_MONTANT_PRINCIPAL else 0 end ) as montant_previous_per
from M4SFR_AC_HR_PERIOD
where SCO_DT_PAY >= '2016-12-01'
and SCO_DT_PAY <= '2017-11-30'
and SCO_DT_PAY <> SCO_DT_ALLOC
group by SCO_ID_HR, SCO_OR_HR_PERIOD
having MIN(SCO_DT_ALLOC) < '2016-12-01'

résultat :

SCO_ID_HR	SCO_OR_HR_PERIOD	MONTANT_CURRENT_PER	MONTANT_PREVIOUS_PER
M10503	1	4800	3600
M10317	1	8040	21413,33

seul bémol, les 8040 qui ne devraient pas être remontés.

Dans la table de données on a pour ce matricule :

SCO_ID_HR	SCO_DT_ALLOC	SCO_DT_PAY	CFR_AMT_MONTANT_PRINCIPAL	SUM(CFR_AMT_MONTANT_PRINCIPAL)
M10317	25/01/2016 00:00	25/12/2016 00:00	1333,33	21413,33	ok
M10317	25/02/2016 00:00	25/12/2016 00:00	2000,00
M10317	25/03/2016 00:00	25/12/2016 00:00	2000,00
M10317	25/04/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/05/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/06/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/07/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/08/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/09/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/10/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/11/2016 00:00	25/12/2016 00:00	2010,00
M10317	25/01/2017 00:00	25/05/2017 00:00	2010,00	8040	ko car 25/01/2017 > 01/12/2016
M10317	25/02/2017 00:00	25/05/2017 00:00	2010,00
M10317	25/03/2017 00:00	25/05/2017 00:00	2010,00
M10317	25/04/2017 00:00	25/05/2017 00:00	2010,00
M10317	25/05/2017 00:00	25/06/2017 00:00	0,00		ko aussi mais même cas que au dessus

(j'ai ajouté manuellement la colonne somme)
Ici 3 paies sont calculées
- une le 25/12/2016
- une le 25/05/2017
- une le 25/06/2017

Le première a une "profondeur" qui remonte jusqu’au 25/01/2016 soit presque un an.
Comme elle est calculée sur le période en cours elle répond aux critères de sélection et comme sa date d'allocation la plus ancienne qui lui est rattachée est antérieur à la période en cours (antérieur à 01/12/2016) on peut la comptabiliser.

la deuxième a une profondeur qui remonte jusqu'au 25/01/2017 soit 5 mois.
Comme elle est également calculée sur la période en cours elle répond elle aussi aux critères de sélection MAIS comme sa date d'allocation la plus ancienne est également sur la période en cours on ne devrait pas la comptabiliser

la troisième : idem que la deuxième.