Parcours et comparaison des colonnes

Version imprimable

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:

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

03/04/2019, 15h27
Ikebukuro

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.
03/04/2019, 15h37
LandGreen

Citation:

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à

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:

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

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

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
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 +

03/04/2019, 16h04
LandGreen

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

merci encore
03/04/2019, 16h58
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é :ptdr:

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
03/04/2019, 17h08
Ikebukuro

Citation:

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é :ptdr:

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 :ptdr::ptdr:
03/04/2019, 17h43
Waldar

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:

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:

date '2018-12-30'

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

Code:

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

Essayez ceci:

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
 
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

04/04/2019, 12h43
LandGreen

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

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

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:

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

04/04/2019, 17h58
SQLpro

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 +

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
 
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.

07/04/2019, 16h46
LandGreen

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