Support

SQL Scripts

Filled Shifts

The number of filled shifts in the DWH:

SELECT count(vacancy_id) as filled
FROM stg.shifts 
WHERE project_day >= '2023-05-01' and project_day <= '2023-05-07' 
AND deleted_at is NULL

Requested Shifts

from DWH

select sum(amount) FROM
(
    SELECT vacancy_id, min(amount) as amount
    FROM stg.shifts 
    WHERE project_day >= '2023-05-01' and project_day <= '2023-05-07' 
    -- and deleted_at is NULL
    GROUP BY vacancy_id
) as i

Specific Shifts

DWH

SELECT s.project_day, s.type, s.project_id, s.deleted_at, s.updated_at, s.student_id, p.project_name, p.client_id,
c.name
FROM stg.shifts s
INNER JOIN stg.project p on s.project_id = p.id
INNER JOIN stg.client c on p.client_id = c.id
WHERE s.project_day >= '2023-07-10' and s.project_day <= '2023-07-16' 
AND c.name like '%Baa%'

Platform

select 
    concat(cast(v.id as varchar(255)),'.', cast(va.id as varchar(255))) as id,
    pd.day, 
    c.name as client_name,
    vg.project_id, 
    v.updated_at, 
    va.student_id, 
    p.name as project_name, 
    p.client_id,
    p.deleted_at as project_deleted_at,
    va.deleted_at as vacancy_allocation_deleted_at
from
    public.vacancy v
left join public.base_timeslot bt on
    v.timeslot_id = bt.id
left join public.project_day pd on
    bt.project_day_id = pd.id
left join public.vacancy_group vg on
    v.group_id = vg.id
left join public.vacancy_allocation va on
    vg.id = va.vacancy_group_id
left join job_function jf on
    v.job_function_id = jf.id
left join (
    select distinct on (vacancy_id) vacancy_id, office_id from public.vacancy_office
) as vo on vo.vacancy_id = v.id
inner join project p on vg.project_id = p.id
inner join client c on p.client_id = c.id
WHERE pd.day >= '2023-07-10' 
and pd.day <= '2023-07-16' 
and c.name = 'Baarle 90'