Résultat intermédiaire - Somme

**QuestVba** · 20/12/2020, 15h21

Bonjour à tous,
Voici ma question du jour.

J'ai la requête suivante qui me donne un nombre de dossiers et d'enfants selon certains critères

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
@set my_shift = 1
WITH ménage AS
(
SELECT DISTINCT
    ben.file_number AS dossierm
    ,CASE fam.type
        WHEN 'MONO' THEN 'M'
        ELSE 'D'
    END AS typ
FROM
    beneficiaries ben
    LEFT JOIN lumpsum_beneficiaries lum ON lum.actor_id = ben.actor_id
    INNER JOIN actors ac ON ac.actor_id = ben.actor_id
    LEFT JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id
    LEFT JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id
        AND LAST_DAY(ADD_MONTHS(TO_DATE(CURRENT_DATE), - :my_shift - 1)) BETWEEN fam.start_date AND NVL(fam.end_date,'31/12/9999')
WHERE lum.actor_id IS NULL
)
SELECT
    TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(CURRENT_DATE), - :my_shift),'yyyymm')) as MONTH_STAT
    ,taux
    ,typ AS famille
    ,COUNT(DISTINCT dossier) AS nbre_dossiers
    ,COUNT(enfant) AS nbre_enfants
 
FROM
    (
        (
        SELECT
            fil.file_number AS dossier
            ,ent.owner_id AS enfant
            ,CASE
                WHEN rat.scale LIKE 'S140%' THEN 'coc base'
                WHEN rat.scale LIKE 'S150%' THEN 'coc base'
                WHEN rat.scale LIKE 'SPL%' THEN 'coc base'
                WHEN rat.scale = 'SBI' THEN 'coc base'
                WHEN rat.scale IN ('S40', 'S50B') THEN 'fed base'
                WHEN rat.scale LIKE 'OUT%' THEN 'fed base' --sinon, les allocataires sont sélectionnés 2 * : Coc base + Fed base
                WHEN rat.scale IN ('SUPSOCA', 'SUPSOCB', 'SUPSOCC', 'SUPSOCD', 'SUPSOCE', 'SUPSOCF', 'SUPSOCI', 'SUPSOCJ', 'SUPSOCK', 'SUPSOCL') THEN 'coc low'
                WHEN rat.scale IN ('SUPSOCG', 'SUPSOCH') THEN 'coc med'
                WHEN rat.scale IN ('S41', 'S42B', 'S50T') THEN 'fed sup'
                ELSE NULL
            END AS taux
            ,typ
        FROM
            allowance_calculations af
            INNER JOIN files fil ON fil.file_number = af.file_id
            INNER JOIN entitled_rates ent ON ent.allowance_calculation_id = af.allowance_calculation_id
            INNER JOIN rates rat ON rat.rate_id = ent.rate_id
                AND rat.scale_type IN ('BASE', 'SUPPLEMENT')
            INNER JOIN ménage mén ON mén.dossierm = af.file_id
        WHERE af.allowance_calculation_id IN
        --Payement ou dernière régularisation en date pour chaque dossier pour le mois passé -> allouable pour ce dossier
        --si tout est récupéré, l'allowance_calculation ne correspondra à aucun entitled_rate...
        --...ni à aucun benefit_receiver_amount
            (
            SELECT
                calc_id
            FROM
                    (
                    SELECT
                        af.allowance_calculation_id AS calc_id
                        ,fil.file_number AS dossier
                        ,af.creation_date AS dat,
                        ROW_NUMBER() OVER (PARTITION BY file_number ORDER BY creation_date desc) AS rang
                    FROM allowance_calculations af
                        INNER JOIN files fil ON fil.file_number = af.file_id
                    WHERE
                        (af.year * 100 + af.month) = TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(CURRENT_DATE), - :my_shift),'yyyymm'))
                        AND af.calculation_type IN('CHILD_ALLOWANCE')
                    )
            WHERE rang = 1
            )
        )
        UNION
        (
        --calculs à la proportionnelle
        SELECT
            fil.file_number AS dossier
            ,pro.owner_id AS enfant
            ,CASE
                WHEN pro.scale IN ('S40', 'S50B') THEN 'fed base'
                WHEN pro.scale IN ('S41', 'S42B', 'S50T') THEN 'fed sup'
                ELSE NULL
            END AS taux
            ,typ
        FROM
            allowance_calculations af
            INNER JOIN files fil ON fil.file_number = af.file_id
            INNER JOIN benefit_receiver_amounts ben ON ben.allowance_calculation_id = af.allowance_calculation_id
            INNER JOIN proportional_entitled_rates pro ON pro.prop_rate_ent_amount_id = ben.prop_rate_ent_amount_id
            INNER JOIN ménage mén ON mén.dossierm = af.file_id
        WHERE af.allowance_calculation_id IN
            (
            SELECT
                calc_id
            FROM
                    (
                    SELECT
                        af.allowance_calculation_id AS calc_id
                        ,fil.file_number AS dossier
                        ,af.creation_date AS dat,
                        ROW_NUMBER() OVER (PARTITION BY file_number ORDER BY creation_date desc) AS rang
                    FROM allowance_calculations af
                        INNER JOIN files fil ON fil.file_number = af.file_id
                    WHERE
                        (af.year * 100 + af.month) = TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(CURRENT_DATE), - :my_shift),'yyyymm'))
                        AND af.calculation_type IN('CHILD_ALLOWANCE')
                    )
            WHERE rang = 1
            )
        )
    )
WHERE taux IS NOT NULL
GROUP BY taux, typ
ORDER BY taux, typ
;