SQLize Online / PHPize Online  /  SQLtest Online

drop table persons purge; drop table journal purge; create table persons ( --'Сотрудники' id NUMBER GENERATED by default on null as IDENTITY, --'Идентификатор сотрудника' name VARCHAR2(150), --'ФИО сотрудника' CONSTRAINT persons_pk PRIMARY KEY (id) ); create table journal ( -- 'Таблица "Журнал входов выходов"' id NUMBER GENERATED by default on null as IDENTITY, -- 'Идентификатор записи в журнале' person number, --'Идентификатор сотрудника' DTime date, -- 'Дата и время события' Type varchar2(1) CHECK(Type in ('0','1')), -- 'Тип события: ''0'' – вход, ''1'' – выход' CONSTRAINT fk_persons FOREIGN KEY (person) REFERENCES persons(id) ); ; truncate table persons; truncate table journal; insert into persons (name) values ('zuzu'); insert into persons (name) values ('zaza') ; insert into journal (person, DTime, Type) select 1, to_date('01.07.2022 09:00', 'dd.mm.yyyy hh24:mi') + level-1, '0' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; insert into journal (person, DTime, Type) select 1, to_date('01.07.2022 18:00', 'dd.mm.yyyy hh24:mi') + level-1, '1' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; insert into journal (person, DTime, Type) select 1, to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1, '1' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; insert into journal (person, DTime, Type) select 1, to_date('01.07.2022 14:00', 'dd.mm.yyyy hh24:mi') + level-1, '0' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; insert into journal (person, DTime, Type) select 2, to_date('01.07.2022 09:00', 'dd.mm.yyyy hh24:mi') + level-1, '0' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; insert into journal (person, DTime, Type) select 2, to_date('01.07.2022 18:00', 'dd.mm.yyyy hh24:mi') + level-1, '1' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; insert into journal (person, DTime, Type) select 2, to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1, '1' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; insert into journal (person, DTime, Type) select 2, to_date('01.07.2022 14:00', 'dd.mm.yyyy hh24:mi') + level-1, '0' from dual where to_char(to_date('01.07.2022 13:00', 'dd.mm.yyyy hh24:mi') + level-1+level-1,'d') not in ('6','7') connect by level <= 31 ; delete from journal j where j.person = 1 and trunc(j.dtime,'dd') in (trunc(to_date('05.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('12.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('13.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('19.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('26.07.2022','dd.mm.yyyy'),'dd')); delete from journal j where j.person = 2 and trunc(j.dtime,'dd') in (trunc(to_date('05.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('12.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('19.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('20.07.2022','dd.mm.yyyy'),'dd'),trunc(to_date('26.07.2022','dd.mm.yyyy'),'dd')); update journal set dtime = to_date('04.07.2022 10:01', 'dd.mm.yyyy hh24:mi') where person = 2 and type = '0' and dtime = to_date('04.07.2022 09:00', 'dd.mm.yyyy hh24:mi'); update journal set dtime = to_date('07.07.2022 11:00', 'dd.mm.yyyy hh24:mi') where person = 1 and type = '0' and dtime = to_date('07.07.2022 09:00', 'dd.mm.yyyy hh24:mi'); update journal set dtime = to_date('10.07.2022 09:40', 'dd.mm.yyyy hh24:mi') where person = 1 and type = '0' and dtime = to_date('10.07.2022 09:00', 'dd.mm.yyyy hh24:mi'); commit ; with yyyy as ( select '2022' y from dual ), mm as ( select to_char(to_date('01.07.'||yyyy.y,'dd.mm.yyyy'),'mm.yyyy') my, to_char(to_date('01.07.2022','dd.mm.yyyy'),'Month') m, yyyy.y from yyyy ), dd as ( select to_char(to_date('01'||mm.my,'dd.mm.yyyy')+level-1,'dd') d, mm.m, mm.y, trunc(to_date('01'||mm.my,'dd.mm.yyyy')+level-1,'dd') full from mm where to_char(to_date('01'||mm.my,'dd.mm.yyyy')+level-1,'mm.yyyy') = mm.my and to_char(to_date('01'||mm.my,'dd.mm.yyyy')+level-1,'d') not in ('6','7') connect by level <= 31 ), dd_with_persons as ( select dd.*,p.id p_id,p.name from dd, persons p ) select dwp.y,dwp.m,dwp.d ,dwp.p_id,dwp.name ,to_number(to_char(min(j.dtime),'sssss'))/60 - 9*60 "минуты опоздания" from dd_with_persons dwp left outer join journal j on trunc(j.dtime,'dd') = dwp.full and dwp.p_id = j.person where j.type = '0' group by dwp.p_id,dwp.name,dwp.y,dwp.m,dwp.d having to_number(to_char(min(j.dtime),'sssss'))/60/60 > 9 union select dwp.y,dwp.m,dwp.d ,dwp.p_id,dwp.name ,-1 "минуты опоздания" --отсутствовал from dd_with_persons dwp where not exists( select * from journal j where j.type = '0' and j.person = dwp.p_id and trunc(j.dtime,'dd')=dwp.full ) order by 1,2,3,4