Problème requête Oracle

Version imprimable

Bonjour le monde

mon problème et comme suit :

j'ai une table mouvement qui contient :

compte type operation date operation (virement ou retrait) montant operation

je veux faire un select comme suit :

le nombre de compte avec les conditions suivantes : virement entre '01/01/2017' et '01/01/2018' dont le montant >3000 dinars
et dont la somme des opérations de retrait effectuer 30 jour après la date du virement sont >2000

autrement en utilisant oracle c'est quelque chose comme ça
Code:

1 2 3 4 select count(*) from mouvement where date_operation between '01/01/2017' and '01/01/2018' AND MONTANT >=3000 and (select sum(montant) from d_mouvement_par where type_operation='retrait' and date_operation between date_operation(du virement) and dte_operation(du virement)+30 )>2000
merci pour votre aide

Bonjour,

Il suffit de préfixer par le nom de la table

Code:

1
2
3
4
select count(*) from mouvement
where date_operation between '01/01/2017' and '01/01/2018'
AND MONTANT >=3000
and (select sum(montant) from d_mouvement_par where type_operation='retrait' and date_operation between mouvement.date_operation   and mouvement.dte_operation+30 )>2000

ou avec des alias

Code:

1
2
3
4
select count(*) from mouvement mvt
where date_operation between '01/01/2017' and '01/01/2018'
AND MONTANT >=3000
and (select sum(montant) from d_mouvement_par d where type_operation='retrait' and d.date_operation between mvt.date_operation   and mvt.dte_operation+30 )>2000

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
 
with data as (
    select 'A' as compte, 'virement' as type_operation, 4000 as montant, to_date('01-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'A' as compte, 'retrait' as type_operation, 1000 as montant, to_date('08-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'A' as compte, 'retrait' as type_operation, 1100 as montant, to_date('12-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'B' as compte, 'virement' as type_operation, 4000 as montant, to_date('01-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'B' as compte, 'retrait' as type_operation, 1000 as montant, to_date('08-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'B' as compte, 'retrait' as type_operation, 900 as montant, to_date('12-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'C' as compte, 'virement' as type_operation, 4000 as montant, to_date('01-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'C' as compte, 'retrait' as type_operation, 1000 as montant, to_date('08-01-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'C' as compte, 'retrait' as type_operation, 1100 as montant, to_date('12-02-2017', 'dd-mm-yyyy') as date_op from dual
    union
    select 'D' as compte, 'virement' as type_operation, 4000 as montant, to_date('01-01-2019', 'dd-mm-yyyy') as date_op from dual
    union
    select 'F' as compte, 'virement' as type_operation, 2000 as montant, to_date('01-01-2017', 'dd-mm-yyyy') as date_op from dual
 
)
select d.compte from data d,
    lateral(select sum(d1.montant) as retraits from data d1 where d1.compte = d.compte and d1.type_operation = 'retrait'
        and d1.date_op between d.date_op and d.date_op + 30
        having(sum(d1.montant) > 2000)
    ) sd
where d.date_op between to_date('01-01-2017', 'dd-mm-yyyy') and to_date('01-01-2018', 'dd-mm-yyyy')
    and d.type_operation = 'virement' and d.montant > 3000 ;

Solution sans jointure :

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
with cte_mouvement as
(
select 'A' as compte, 'virement' as type_operation, 4000 as montant, date '2017-01-01' as date_op from dual union all
select 'A' as compte, 'retrait'  as type_operation, 1000 as montant, date '2017-01-08' as date_op from dual union all
select 'A' as compte, 'retrait'  as type_operation, 1100 as montant, date '2017-01-12' as date_op from dual union all
select 'B' as compte, 'virement' as type_operation, 4000 as montant, date '2017-01-01' as date_op from dual union all
select 'B' as compte, 'retrait'  as type_operation, 1000 as montant, date '2017-01-08' as date_op from dual union all
select 'B' as compte, 'retrait'  as type_operation,  900 as montant, date '2017-01-12' as date_op from dual union all
select 'C' as compte, 'virement' as type_operation, 4000 as montant, date '2017-01-01' as date_op from dual union all
select 'C' as compte, 'retrait'  as type_operation, 1000 as montant, date '2017-01-08' as date_op from dual union all
select 'C' as compte, 'retrait'  as type_operation, 1100 as montant, date '2017-02-12' as date_op from dual union all
select 'D' as compte, 'virement' as type_operation, 4000 as montant, date '2019-01-01' as date_op from dual union all
select 'F' as compte, 'virement' as type_operation, 2000 as montant, date '2017-01-01' as date_op from dual
)
  ,  cte_calc_retrait_30j as
(
select compte
     , type_operation
     , montant
     , date_op
     , case type_operation
         when 'virement'
         then sum(case type_operation when 'retrait' then montant else 0 end) over(partition by compte order by date_op asc range between current row and interval '30' day following)
         else 0
       end as mnt_retrait_30j
  from cte_mouvement
 where date_op >= date '2017-01-01'
   and date_op <  date '2018-01-01' + 30
)
select *
  from cte_calc_retrait_30j
 where date_op         >= date '2017-01-01'
   and date_op         <  date '2018-01-01'
   and type_operation   = 'virement'
   and montant         >= 3000
   and mnt_retrait_30j >  2000
;