CREATE TABLE RESULTS (
    ID_RES            INTEGER NOT NULL,
    REG_NUM         INTEGER 
    BLOCKS           INTEGER 
    DATE_IN          DATE 
    NUM_SLICE      INTEGER 
    BLOCK_SLICE   INTEGER 
);

SET TERM ^ ;

create or alter procedure NEWREG (
    DATE_P date)
returns (
    NREG integer)
as
declare variable OLDDATE_IN date;
declare variable VDATE_P varchar(4);
declare variable NDATE_P varchar(4);
declare variable OLDBLOCKS integer;
declare variable OLDREG integer;
declare variable MAXID integer;
declare variable MAX_NUM_SLICE integer;

begin
  select max(ID_RES)
  from RESULTS /*Находим последнюю запись*/
  into :MAXID;
  /*Выбираем дату поступления, старый рег. номер*/
  select REG_NUM, BLOCKS, DATE_IN
  from RESULTS
  where ID_RES = :MAXID
  into :OLDREG, :OLDBLOCKS, :OLDDATE_IN;
  /*Сравниваем, если год поступления последней записи меньше чем с клиента,
то Ставим номер 1*/

  VDATE_P = extract(year from OLDDATE_IN);
  NDATE_P = extract(year from DATE_P);

  if ((MAXID is null) or (VDATE_P < NDATE_P)) then
  begin
    NREG = 1;
  end

  /*Иначе, продолжаем нумерацию*/
  else
  begin
    if (NUM_SLICE is null) then
      NREG = OLDREG + OLDBLOCKS;
    else
    begin

      select max(NUM_SLICE)
      from RESULTS
      where extract(year from DATE_IN) = extract(year from :DATE_P)
      into :MAX_NUM_SLICE;

      select NUM_SLICE + BLOCK_SLICE as NEXTNUM
      from RESULTS
      where NUM_SLICE = :MAX_NUM_SLICE
      into :NREG;

    end

    suspend;

  end
end

^

SET TERM ; ^