Parcours et comparaison des colonnes

**LandGreen** · 03/04/2019, 15h02

Bonjour à tous, bonjour à toutes;

j'ai besoin de votre habituelle collaboration je suis coincé.

j'ai une table qui contient 2 colonnes comme suit :

table(Date_a, montant)

voici un exemple de données qui peuvent y apparaître :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
03/09/2017			-3940,54    ...(-1)...ok
03/12/2017			-5000		...(-2)...ok
20/12/2017			3940,54		...(1) ...
20/12/2017			5000		...(2) ...
04/03/2018			-4486,08	...(-3)...ok
13/03/2018			4486,08		...(3) ...
03/06/2018			-4871,64	...(-4)...ok
02/09/2018			-5000		...(-5)...ok
02/12/2018			-5000		...(-6)...NO ...je dois récupérer sa date 
30/12/2018			4871,64		...(4) ...
30/12/2018			5000		...(5) ...
30/12/2018			4446,02		...(1) ...
03/03/2019			-5000		...(-7)...NO ...je dois récupérer sa date

les données mentionnées dans la colonne MONTANT marche 1 à 1, c'est à dire chaque montant négatif est associé à un montant positif,

je dois parcourir toute la colonne et une fois je ne trouve pas un montant négatif sans son associé, je récupère sa date.

exemple
ici le (-6) n'a pas sont (6) donc je dois récupérer le 02/12/2018
ici le (-7) n'a pas sont (7) donc je dois récupérer le 03/03/2019

et ainsi de suite

**Ikebukuro** · 03/04/2019, 15h27

Compliqué ton truc, en plus tu ne précises pas toute la règle :
ici le (-6) n'a pas sont (6) donc je dois récupérer le 02/12/2018 ==> FAUX, c'est le (2)
ici le (-7) n'a pas sont (7) donc je dois récupérer le 03/03/2019 ==> FAUX, c'est le (2)

Il faut donc préciser que le (-6) n'a pas sont (6) car le nombre correspondant (5000 que toi tu appelles (2)) a déjà matché avec son équivalent mais d'autre signe.

Est-ce que dans ta table tu auras TOUJOURS le nombre positif avant le négatif, ou l'inverse ou bien il n'y a pas de règle?

Je laisse les pros du SQL répondre mais ça n'a pas l'air simple.

**LandGreen** · 03/04/2019, 15h37

Envoyé par Ikebukuro

Est-ce que dans ta table tu auras TOUJOURS le nombre positif avant le négatif, ou l'inverse ou bien il n'y a pas de règle?

merci pour ton retour mon ami,

je vais éclaircir un peu :

je commence toujours par le nombre négatif, d'où je cherche son positif.

et dans mon exemple le (-n) correspond au montant tandis que (n) correspondant à son associé voilà

**McM** · 03/04/2019, 15h49

Bonjour,

C'est possible, pas trop trop compliqué avec ce jeu d'essai, mais à voir si ça passe en réel
En gros :
Vue e : les montants qui posent problème (valeur absolue) et le nombre de montants négatifs sans montant positif (nbKO)
Jointure e, t : Permet avec le partition de trier par date décroissante et d'y associer un n° de ligne

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
20
21
22
23
24
25
WITH t AS (    SELECT TO_DATE('03/09/2017', 'DD/MM/RRRR') date_a, -3940.54 montant FROM dual
UNION ALL SELECT TO_DATE('03/12/2017', 'DD/MM/RRRR'), -5000 FROM dual
UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'), 3940.54 FROM dual
UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'), 5000 FROM dual
UNION ALL SELECT TO_DATE('04/03/2018', 'DD/MM/RRRR'), -4486.08 FROM dual
UNION ALL SELECT TO_DATE('13/03/2018', 'DD/MM/RRRR'), 4486.08 FROM dual
UNION ALL SELECT TO_DATE('03/06/2018', 'DD/MM/RRRR'), -4871.64 FROM dual
UNION ALL SELECT TO_DATE('02/09/2018', 'DD/MM/RRRR'), -5000 FROM dual
UNION ALL SELECT TO_DATE('02/12/2018', 'DD/MM/RRRR'), -5000 FROM dual
UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 4871.64 FROM dual
UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 5000 FROM dual
UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 4446.02 FROM dual
UNION ALL SELECT TO_DATE('03/03/2019', 'DD/MM/RRRR'), -5000 FROM dual
),
e AS (SELECT ABS(montant) mt, SUM(CASE WHEN montant < 0 THEN 1 ELSE 0 END) - SUM(CASE WHEN montant > 0 THEN 1 ELSE 0 END) nbKO
FROM t
GROUP BY ABS(montant)
HAVING SUM(CASE WHEN montant < 0 THEN 1 ELSE 0 END) > SUM(CASE WHEN montant > 0 THEN 1 ELSE 0 END)
)
SELECT date_a, montant
FROM (SELECT t.date_a, t.montant, e.nbKO, row_number() OVER (PARTITION BY montant ORDER BY date_a desc) rn
FROM e, t
WHERE t.montant = -e.mt
)
WHERE rn <= nbKO

Résultat :
DATE_A MONTANT
03/03/2019 -5000
02/12/2018 -5000

et avec 4446.02 en négatif (30/12/2018) au lieu de positif
DATE_A MONTANT
03/03/2019 -5000
02/12/2018 -5000
30/12/2018 -4446.02

**SQLpro** · 03/04/2019, 16h02

Plus simple avec du SQL normalisé et non avec le dialecte propre à Oracle :

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
20
21
22
23
24
25
26
WITH t AS 
(
SELECT CAST('03/09/2017'AS DATE) date_a, -3940.54 montant, -1 AS no 
UNION ALL SELECT CAST('03/12/2017'AS DATE), -5000, -2 
UNION ALL SELECT CAST('20/12/2017'AS DATE), 3940.54, 1
UNION ALL SELECT CAST('20/12/2017'AS DATE), 5000, 2 
UNION ALL SELECT CAST('04/03/2018'AS DATE), -4486.08, -3 
UNION ALL SELECT CAST('13/03/2018'AS DATE), 4486.08, 3 
UNION ALL SELECT CAST('03/06/2018'AS DATE), -4871.64, -4
UNION ALL SELECT CAST('02/09/2018'AS DATE), -5000, -5 
UNION ALL SELECT CAST('02/12/2018'AS DATE), -5000, -6
UNION ALL SELECT CAST('30/12/2018'AS DATE), 4871.64, 4 
UNION ALL SELECT CAST('30/12/2018'AS DATE), 5000, 5 
UNION ALL SELECT CAST('30/12/2018'AS DATE), 4446.02, 1 
UNION ALL SELECT CAST('03/03/2019'AS DATE), -5000, -7 
)
SELECT CREDIT.date_a AS DATE_CREDIT, 
       CREDIT.montant AS MONTANT_CREDIT,
	   CREDIT.no AS NO_CREDIT,
       DEBIT.date_a AS DATE_DEBIT, 
       DEBIT.montant AS MONTANT_DEBIT,
	   DEBIT.no AS NO_DEBIT
FROM   t AS CREDIT
       FULL OUTER JOIN t AS DEBIT 
	      ON CREDIT.no = - DEBIT.no
WHERE  DEBIT.montant IS NULL OR CREDIT.montant IS NULL;

A +

**LandGreen** · 03/04/2019, 16h04

merci beaucoup SQLpro & McM je vais tester et vous tenir informés

merci encore

**McM** · 03/04/2019, 16h58

Le code de SQLPro contient le champ No qui est l'association et qui n'existe pas dans la table de départ, c'est un peu enlever toute la difficulté

Quand à la norme standardisée, un CAST('30/12/2018' AS DATE moi me sort en erreur : Not a valid Month
et il manque les FROM DUAL
Bref sur un forum Oracle, la requête est invalide

**Ikebukuro** · 03/04/2019, 17h08

Envoyé par McM

Le code de SQLPro contient le champ No qui est l'association et qui n'existe pas dans la table de départ, c'est un peu enlever toute la difficulté

Quand à la norme standardisée, un CAST('30/12/2018' AS DATE moi me sort en erreur : Not a valid Month
et il manque les FROM DUAL
Bref sur un forum Oracle, la requête est invalide

Héhé, bien vu

**Waldar** · 03/04/2019, 17h43

Aparté quant au sujet initial.

Je n'aime pas ces écritures qui vont dépendre d'un format non spécifié configuré on ne sait trop où :

Code :

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

cast('30/12/2018' as date)

La solution des date literals est pourtant la plus courte (car impose le format ISO) et est supportée par tous les SGBD il me semble.

Code :

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

date '2018-12-30'

Si on veut vraiment utiliser la fonction cast, il faut préciser le format :

Code :

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

select cast('30/12/2018' as date, 'dd/mm/yyyy') from dual;

**JeitEmgie** · 03/04/2019, 19h22

Essayez ceci:

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
WITH data AS (    
    SELECT TO_DATE('03/09/2017', 'DD/MM/RRRR') date_a, -3940.54 montant FROM dual
    UNION ALL SELECT TO_DATE('03/12/2017', 'DD/MM/RRRR'), -5000 FROM dual
    UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'), 3940.54 FROM dual
    UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'), 5000 FROM dual
    UNION ALL SELECT TO_DATE('04/03/2018', 'DD/MM/RRRR'), -4486.08 FROM dual
    UNION ALL SELECT TO_DATE('13/03/2018', 'DD/MM/RRRR'), 4486.08 FROM dual
    UNION ALL SELECT TO_DATE('03/06/2018', 'DD/MM/RRRR'), -4871.64 FROM dual
    UNION ALL SELECT TO_DATE('02/09/2018', 'DD/MM/RRRR'), -5000 FROM dual
    UNION ALL SELECT TO_DATE('02/12/2018', 'DD/MM/RRRR'), -5000 FROM dual
    UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 4871.64 FROM dual
    UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 5000 FROM dual
    UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 4446.02 FROM dual
    UNION ALL SELECT TO_DATE('03/03/2019', 'DD/MM/RRRR'), -5000 FROM dual
)
, odata as (
    -- added montant to order by to cover case where matching transactions are the same day, - must be before + 
    select row_number() over(order by date_a, montant) as rn, d.* from data d
)
, rdata as (
select d1.rn as rn1, d1.date_a as da1, d1.montant as montant1, d2.rn as rn2, d2.date_a as da2, d2.montant as montant2 from odata d1
    join odata d2 on 
        d2.rn = (
            select min(d3.rn) from odata d3 where d3.montant = -d1.montant and d1.rn < rn
            and not exists(select montant from odata where montant = d1.montant and (rn > d1.rn and rn < d3.rn))
        )
    where d1.montant < 0
)
select date_a, montant from odata where 
    (not rn in(select rn1 from rdata) and montant < 0)
    or 
    (not rn in(select rn2 from rdata) and montant > 0)
order by rn
;

DATE_A	MONTANT
02-SEP-18	-5000
30-DEC-18	4446.02
03-MAR-19	-5000

**LandGreen** · 04/04/2019, 12h43

DATE_A	MONTANT
02-SEP-18 >> 2-DEC-18	-5000
30-DEC-18	4446.02
03-MAR-19	-5000

merci pour ton retour, tu as mis SEP au lieu de DEC dans la 1ere ligne

**JeitEmgie** · 04/04/2019, 14h12

c'est ce que renvoie le query...
si vous voulez le deuxième -5000 (du 02-DEC) il faudra modifier les critères,

a priori si c'est cela que vous voulez il faudra travailler "à l'envers" : trier par date desc et faire le lettrage des écritures dans le sens montant positif vers montant négatif

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
WITH data AS (    
    SELECT TO_DATE('03/09/2017', 'DD/MM/RRRR') date_a, -3940.54 montant FROM dual
    UNION ALL SELECT TO_DATE('03/12/2017', 'DD/MM/RRRR'), -5000 FROM dual
    UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'), 3940.54 FROM dual
    UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'), 5000 FROM dual
    UNION ALL SELECT TO_DATE('04/03/2018', 'DD/MM/RRRR'), -4486.08 FROM dual
    UNION ALL SELECT TO_DATE('13/03/2018', 'DD/MM/RRRR'), 4486.08 FROM dual
    UNION ALL SELECT TO_DATE('03/06/2018', 'DD/MM/RRRR'), -4871.64 FROM dual
    UNION ALL SELECT TO_DATE('02/09/2018', 'DD/MM/RRRR'), -5000 FROM dual
    UNION ALL SELECT TO_DATE('02/12/2018', 'DD/MM/RRRR'), -5000 FROM dual
    UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 4871.64 FROM dual
    UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 5000 FROM dual
    UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'), 4446.02 FROM dual
    UNION ALL SELECT TO_DATE('03/03/2019', 'DD/MM/RRRR'), -5000 FROM dual
)
, odata as (
    select row_number() over(order by date_a desc, montant desc) as rn, d.* from data d
)
, rdata as (
select d1.rn as rn1, d1.date_a as da1, d1.montant as montant1, d2.rn as rn2, d2.date_a as da2, d2.montant as montant2 from odata d1
    join odata d2 on 
        d2.rn = (
            select max(d3.rn) from odata d3 where d3.montant = -d1.montant and d1.rn < d3.rn
            and not exists(select montant from odata where montant = d1.montant and (rn > d1.rn and rn < d3.rn))
        )
    where d1.montant > 0
)
select date_a, montant from odata where 
    (not rn in(select rn1 from rdata) and montant > 0)
    or 
    (not rn in(select rn2 from rdata) and montant < 0)
order by rn desc
;

DATE_A	MONTANT
02-Dec-18	-5000
30-Dec-18	4446.02
03-Mar-19	-5000

**SQLpro** · 04/04/2019, 17h58

Vu qu'il n'y a pas de clef dans ses lignes et qu'il n'existe pas d'ordre des lignes dans une table, il n'y a pas de solution à votre problème.... Toutes les requêtes indiquées sont fausses parce qu'elles donneront des résultats arbitraires.

A +

**JeitEmgie** · 06/04/2019, 11h14

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
WITH data AS (      
          SELECT TO_DATE('03/09/2017', 'DD/MM/RRRR') date_a, -3940.54 montant FROM dual
UNION ALL SELECT TO_DATE('03/12/2017', 'DD/MM/RRRR'),       -5000 FROM dual
UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'),       3940.54 FROM dual
UNION ALL SELECT TO_DATE('20/12/2017', 'DD/MM/RRRR'),        5000 FROM dual
UNION ALL SELECT TO_DATE('04/03/2018', 'DD/MM/RRRR'),       -4486.08 FROM dual
UNION ALL SELECT TO_DATE('13/03/2018', 'DD/MM/RRRR'),        4486.08 FROM dual
UNION ALL SELECT TO_DATE('03/06/2018', 'DD/MM/RRRR'),       -4871.64 FROM dual
UNION ALL SELECT TO_DATE('02/09/2018', 'DD/MM/RRRR'),       -5000 FROM dual
UNION ALL SELECT TO_DATE('02/12/2018', 'DD/MM/RRRR'),       -5000 FROM dual
UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'),        4871.64 FROM dual
UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'),        5000 FROM dual
UNION ALL SELECT TO_DATE('30/12/2018', 'DD/MM/RRRR'),        4446.02 FROM dual
UNION ALL SELECT TO_DATE('03/03/2019', 'DD/MM/RRRR'),       -5000 FROM dual
)  
, odata as ( 
    select 
        row_number() over(order by date_a desc, montant desc) as key, date_a, montant,
        abs(montant) as amnt
    from data  
    where montant <> 0
)  
, mdata as (
select k, d, m, kp, dp, mp from odata
    match_recognize (
        partition by amnt order by date_a desc
        measures last(N.key) as k, (P.key) as kp, last(N.date_a) as d, (P.date_a) as dp, last(N.montant) as m, (P.montant) as mp
        pattern( P N+ )
        define
            P as montant > 0,
            N as montant < 0
    )
)
select date_a, montant from odata 
where key not in (select k from mdata union select kp from mdata)
order by key desc
;

DATE_A	MONTANT
02-DEC-18	-5000
30-DEC-18	4446.02
03-MAR-19	-5000

le mdata vous donne le lettrage réalisé
si vous voulez lettrer le premier négatif plutôt que le dernier dans une suite (- - - +),
il suffit d'enlever les instructions last() dans la clause measure

La même logique est possible sans la clause measure mais c'est nettement moins lisible.

**LandGreen** · 07/04/2019, 16h46

merci à tous ça fonctionne après jumelage des différentes solutions proposées par vos soins

Parcours et comparaison des colonnes

SQL Oracle

Discussions similaires

Partager

Partager