create view WORKED_VIEW (
    ID,
    END_DAY,
    FIO,
    FOTO,
    USDAT,
    COMMENTARY,
    FINED)
as
select STAFF_CARDS.IDENTIFIER as ID, STAFF_CARDS.DATE_END as END_DAY, STAFF.PORTRET as FOTO,
       trim(STAFF.LAST_NAME) || ' ' || trim(STAFF.FIRST_NAME) || ' ' || trim(STAFF.MIDDLE_NAME) as FIO,
       trim(SUBDIV_REF.DISPLAY_NAME) || ' ' || trim(APPOINT_REF.DISPLAY_NAME) as USDAT,
       trim(STAFF_INFO_DATA_STR.INFO_DATA) as COMMENTARY/*, max(STAFF_REF.DATE_ACTION) as FINED*/

from STAFF
join STAFF_REF on (STAFF.ID_STAFF = STAFF_REF.STAFF_ID)
join APPOINT_REF on (STAFF_REF.APPOINT_ID = APPOINT_REF.ID_REF)
join SUBDIV_REF on (STAFF_REF.SUBDIV_ID = SUBDIV_REF.ID_REF)
join STAFF_CARDS on (STAFF.ID_STAFF = STAFF_CARDS.STAFF_ID)
join STAFF_INFO_DATA_STR on ((STAFF_INFO_DATA_STR.REF_ID =  1264276 ) and (STAFF_INFO_DATA_STR.STAFF_ID = STAFF.ID_STAFF))

where ((STAFF.PORTRET is not null) and
      (STAFF.DELETED =  0 ) and
      (STAFF_CARDS.PROHIBIT =  0 ) and
      (STAFF.VALID =  1 ))

/*group by FIO*/
;

CREATE VIEW WORKED_VIEW(
    STAFFID,
    DATES)
AS
select trim( staff.LAST_NAME) || ' ' || trim( staff.FIRST_NAME) || ' ' || trim( staff.MIDDLE_NAME) as staffid,
      max( staff_ref.date_action ) as dates
from  staff join staff_ref on (staff.id_staff = staff_ref.staff_id)
where (STAFF.PORTRET is not null)
group by staffid
;