Discussion :

[QuestVba]

Hello le Forum ,

J'ai une requête que me donne une liste de dossiers et pour chaque dossier, j'ai 6 montants au maximum.
Pour la requête, voici :

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
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
select 
    pers.inss as inss
    , ben.file_number AS file_number
    , mdbt.managed_debt_id as DEBT_itiniris
    , min(dbt.year*100 + dbt.month) as Start_Month
    , max(dbt.year*100 + dbt.month) as End_Month
    , sum(dbt.amount) as Month_Amount-- toutes les lignes mises en débit
    , 
        (
            select 
                sum(w_FDR.AMOUNT) as AMOUNT
            from RESERVEFUND_WRITEOFFS  w_FDR
                inner join DEBT_ACCOUNTS da_FDR on w_FDR.DEBT_ACCOUNT_ID = da_FDR.DEBT_ACCOUNT_ID
                inner join MANAGED_DEBTS md_FDR on da_FDR.DEBT_ACCOUNT_ID = md_FDR.DEBT_ACCOUNT_ID
            where md_FDR.MANAGED_DEBT_ID = mdbt.managed_debt_id
            group by md_FDR.managed_debt_id
        ) 
        as FDR
    , --Retenues (autre CAF)
        (
            select 
                sum(w_WITTH_O.AMOUNT) as AMOUNT
            from WITHHOLDINGS w_WITTH_O
                inner join DEBT_ACCOUNTS da_WITTH_O on w_WITTH_O.DEBT_ACCOUNT_ID = da_WITTH_O.DEBT_ACCOUNT_ID
                inner join MANAGED_DEBTS md_WITTH_O on da_WITTH_O.DEBT_ACCOUNT_ID = md_WITTH_O.DEBT_ACCOUNT_ID
            where md_WITTH_O.MANAGED_DEBT_ID = mdbt.managed_debt_id
            group by md_WITTH_O.managed_debt_id
        ) 
        as WITHH_O
    , -- Remboursement 
        (
            select 
                sum(w_RMB.AMOUNT) as AMOUNT
            from DEBT_PAYOFFS w_RMB
                inner join DEBT_ACCOUNTS da_RMB on w_RMB.DEBT_ACCOUNT_ID = da_RMB.DEBT_ACCOUNT_ID
                inner join MANAGED_DEBTS md_RMB on da_RMB.DEBT_ACCOUNT_ID = md_RMB.DEBT_ACCOUNT_ID
            where md_RMB.MANAGED_DEBT_ID = mdbt.managed_debt_id
            group by md_RMB.managed_debt_id
        )
        AS remb
    , -- Transfert héritiers
        (
            select
                sum(w_HERIT.AMOUNT) as AMOUNT
            from INHERITANCE_TRANSFERS w_HERIT
                inner join DEBT_ACCOUNTS da_HERIT on w_HERIT.DEBT_ACCOUNT_ID = da_HERIT.DEBT_ACCOUNT_ID
                inner join MANAGED_DEBTS md_HERIT on da_HERIT.DEBT_ACCOUNT_ID = md_HERIT.DEBT_ACCOUNT_ID
            where md_HERIT.MANAGED_DEBT_ID = mdbt.managed_debt_id
            group by md_HERIT.managed_debt_id        
        )
        as HERIT
    , -- Paiement suite renonciation OK
        (
            select 
                sum(p_RENON.AMOUNT) as AMOUNT
            from PAYMENTS p_RENON
                inner join RESERVEFUND_WRITEOFFS w_RENON on p_RENON.PAYMENT_ID = w_RENON.PAYMENT_ID
                inner join DEBT_ACCOUNTS da_RENON on w_RENON.DEBT_ACCOUNT_ID = da_RENON.DEBT_ACCOUNT_ID
                inner join MANAGED_DEBTS md_RENON on da_RENON.DEBT_ACCOUNT_ID = md_RENON.DEBT_ACCOUNT_ID
            where md_RENON.MANAGED_DEBT_ID = mdbt.managed_debt_id
            group by md_RENON.managed_debt_id
        )
        as RENON
from beneficiaries ben
    inner join actors act on act.actor_id = ben.actor_id
    inner join persons pers on pers.person_id = act.person_id 
    inner join debits dbt on dbt.owner_id = ben.actor_id
    inner join managed_debts mdbt on mdbt.debt_account_id = dbt.debt_account_id
        -- Ne pas prendre les débits à la vérification, Closed, Prévu...
        and mdbt.debtstatus not in ('CLOSED', 'CLOSED_NO_H29', 'CLOSED_WITH_NOTIFICATION', 'VERIFIED', 'MOTIVATED', 'PROVIDED')
--where MANAGED_DEBT_ID = 100003514953
group by pers.inss, ben.file_number, mdbt.managed_debt_id
order by pers.inss, ben.file_number, mdbt.managed_debt_id
;

Voici ce que cela donne comme résultat, ce sera plus clair :


Comment dois-je procéder pour obtenir une somme de ces 6 montants ? Si je veux ces 6 montants dans la colonne 7 comme un total, j'imagine que je peux tout englober dans un autre select où je ferais le calcul ?
Est-ce comme cela que je dois procéder ? Ou avez-vous une manière plis simple ?

NB : j’ai l’impression que je peux faire plus simple mais il y a un truc qui ne fait pas tilt.

[vanagreg]

Bonjour,

Oui tu englobes la requête dans une sous-requête ou un bloc with et tu fais la somme de tes 6 montants.