Delimiter //
Drop Procedure if Exists BUSINESS_KEEP_ONE_ACCOUNT_FIANA_PER_MONTH//
 
Create Procedure BUSINESS_KEEP_ONE_ACCOUNT_FIANA_PER_MONTH()
Begin
    main: begin

		Set @refDate  = AMO_BAL_REFDATE();
		set @refDate = date_add(@refDate, interval 1- DAYOFMONTH(@refDate) day);
        Delete FHD 
        From (
				Select 
					FH.ACCOUNTS_ID_ACCOUNTS, per, max(FH.ID_FI_ANA) LAST_ID_FIA_ANA
				From (
						Select
							ACCOUNTS_ID_ACCOUNTS, extract(YEAR_MONTH From FI_ANA_HISTO_DATE) per, count(*) nbre
							, max(FI_ANA_HISTO_DATE) datelst
						From
							FI_ANA_HISTO
						Where
							FI_ANA_HISTO_DATE >= @refDate
						GROUP By ACCOUNTS_ID_ACCOUNTS, extract(YEAR_MONTH From FI_ANA_HISTO_DATE) 
						having count(*) > 1
					) lastHistoMonth
					Inner Join FI_ANA_HISTO FH
						On lastHistoMonth.ACCOUNTS_ID_ACCOUNTS = FH.ACCOUNTS_ID_ACCOUNTS
						And lastHistoMonth.datelst = FH.FI_ANA_HISTO_DATE
				Where
					FH.FI_ANA_HISTO_DATE >= @refDate
				Group By FH.ACCOUNTS_ID_ACCOUNTS, per
			) FPR
			Inner Join FI_ANA_HISTO FHD
				On FPR.ACCOUNTS_ID_ACCOUNTS = FHD.ACCOUNTS_ID_ACCOUNTS
				And FPR.per = extract(YEAR_MONTH From FHD.FI_ANA_HISTO_DATE) 
				And FPR.LAST_ID_FIA_ANA <> FHD.ID_FI_ANA
		Where
			FHD.FI_ANA_HISTO_DATE >= @refDate
    ;
    end ;
end //
Delimiter ;
