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
Telegram: DB developers channel