Untitled

with org_chart_of_current_employee as (select employee_id as   current_employee_id,
                                              org_node.id      org_node_id,
                                              org_node.content org_name
                                       from admin.account acc
                                                JOIN hr.employee emp
                                                     on acc.email = emp.work_email
                                                JOIN hr.employee_to_org_chart_node emp_org
                                                     on emp_org.employee_id = emp.id
                                                JOIN admin.org_chart_node org_node
                                                     on org_node.id = emp_org.org_chart_node_id),
     employees_belong_to_org_chart as (select current_employee_id,
                                              org_node_id,
                                              org_name,
                                              emp_org.employee_id
                                       from hr.employee_to_org_chart_node emp_org
                                                join org_chart_of_current_employee ococe
                                                     on emp_org.org_chart_node_id = ococe.org_node_id),
------------------- Team Timesheet -------------------
     previous_salary_period AS (SELECT id as time_sheet_period_id,
                                       id as salary_period_id,
                                       id as fiscal_year_id,
                                       period_number,
                                       start_date,
                                       end_date
                                FROM timekeeping.time_sheet_period
                                WHERE period_number = (SELECT max(period_number)
                                                       FROM timekeeping.time_sheet_period
                                                       WHERE start_date::date <= current_date
                                                         AND current_date <= end_date::date) - 1),
     current_salary_period AS (SELECT id as time_sheet_period_id,
                                      id as salary_period_id,
                                      id as fiscal_year_id,
                                      period_number,
                                      start_date,
                                      end_date
                               FROM timekeeping.time_sheet_period
                               WHERE period_number = (SELECT max(period_number)
                                                      FROM timekeeping.time_sheet_period
                                                      WHERE start_date::date <= now()::date
                                                        AND now()::date <= end_date::date)),
     result_period AS (select *
                       from previous_salary_period
                       union all
                       select *
                       from current_salary_period),
     result as (SELECT
                    ebtoc.current_employee_id,
                    ts.employee_id,
                       CASE
                           WHEN status = 'SUBMITTED' THEN 'Submitted'
                           WHEN status = 'ELEVATED' THEN 'Elevated'
                           WHEN status = 'APPROVED' THEN 'Approved'
                           ELSE 'In Progress'
                           end as "Status"
                FROM timekeeping.time_sheet ts
                         ---------- get employees belong to team of current user -------
                         JOIN employees_belong_to_org_chart ebtoc
                              on ts.employee_id = ebtoc.employee_id
               --  where ts.salary_period_id =
               --        (SELECT time_sheet_period_id
               --         FROM result_period
               --         WHERE period_number = (SELECT MIN(period_number) FROM result_period))
                       )
select *
from result
where employee_id = '22c25f0e-8811-4fc7-ba52-3456bc793b16'