Delimiter //
Drop Procedure if Exists BUSINESS_KEEP_ONE_MEMBER_FIANA_PER_MONTH//
 
Create Procedure BUSINESS_KEEP_ONE_MEMBER_FIANA_PER_MONTH()
Begin

    main: Begin

        Delete FI_DEL 
        From (
            Select 
                FI_HISTO.MEMBERS_ID_MEMBERS
                , FI_ANA_LAST_YEAR_MONTH
                , Max(FI_HISTO.ID_FI_ANA) FI_ANA_LAST_ID
            From (
                    Select
                        MEMBERS_ID_MEMBERS
                        , Extract(YEAR_MONTH From Max(FI_ANA_HISTO_DATE)) FI_ANA_LAST_YEAR_MONTH
                        , Max(FI_ANA_HISTO_DATE) FI_ANA_LAST_DATE
                        , Count(*) N
                    From
                        FI_ANA_MEMBERS_HISTO
                    Group By 
                        MEMBERS_ID_MEMBERS
                        , Extract(YEAR_MONTH From FI_ANA_HISTO_DATE)
                    Having 
                        Count(*) > 1
                ) FI_HISTO_LAST
                Inner Join FI_ANA_MEMBERS_HISTO FI_HISTO
                    On FI_HISTO.MEMBERS_ID_MEMBERS = FI_HISTO_LAST.MEMBERS_ID_MEMBERS
                    And FI_HISTO.FI_ANA_HISTO_DATE = FI_HISTO_LAST.FI_ANA_LAST_DATE
            Group By 
                FI_HISTO.MEMBERS_ID_MEMBERS
                , FI_ANA_LAST_YEAR_MONTH
        ) FI_PERIOD
            Inner Join FI_ANA_MEMBERS_HISTO FI_DEL
                On FI_DEL.MEMBERS_ID_MEMBERS = FI_PERIOD.MEMBERS_ID_MEMBERS
                And Extract(YEAR_MONTH From FI_DEL.FI_ANA_HISTO_DATE) = FI_PERIOD.FI_ANA_LAST_YEAR_MONTH 
                And FI_DEL.ID_FI_ANA <> FI_PERIOD.FI_ANA_LAST_ID
        ;

    End ;

End //
Delimiter ;
