Untitled

SELECT 
        unit,
        description_user,
        STRING_AGG ( penerima_tugas, ', ' ) penerima_tugas,
        ID,
        waktu_tenggat,
        status_deadline 
    FROM
        (
            SELECT 
            b.unit,
            tickets.description_user,
            fullname penerima_tugas,
            tickets.ID,
            assignees.end_date_assignee waktu_tenggat,
        CASE
                
                WHEN to_char( now( ), 'dd-mm-yyyy' ) > to_char( end_date_assignee, 'dd-mm-yyyy' ) THEN
                'Melewati Deadline' 
                WHEN to_char( now( ), 'dd-mm-yyyy' ) = to_char( end_date_assignee, 'dd-mm-yyyy' ) THEN
                'Mendekati Deadline' ELSE'Belum Melewati Deadline' 
            END status_deadline 
    FROM
        assignees
        JOIN task ON assignees.task_id = task.
        ID JOIN assignments ON task.assignment_id = assignments.
        ID JOIN tickets ON assignments.ticket_id = tickets.
        ID JOIN sub_service ON sub_service.ID = tickets.sub_service_id
        JOIN services ON services.ID = sub_service.service_id
        JOIN (
        SELECT DISTINCT
            fullname,
            user_id,
        CASE
                
                WHEN unit LIKE'%BAGIAN PENGEMBANGAN PRODUK TI%' THEN
                'DEVTI' 
                WHEN unit LIKE'%BAGIAN RISET, LAYANAN, DAN QUALITY MANAGEMENT TI%' THEN
                'RIYANTI' 
                WHEN unit LIKE'%BAGIAN INFRASTRUKTUR TI%' THEN
                'ISTI' 
            END unit,
        work_location_id 
    FROM
        dblink ( 'service_desk_humanresource', '
        SELECT
            fullname,
            user_id,
            case when d.detail_name like ''%KEPALA BAGIAN%'' then e.detail_name else f.detail_name end unit,
            f.id work_location_id
        FROM
            employees
            a JOIN mapping_employee_user b ON ( a.identity_card_number = b.employee_identity_number ) 
            join position_history c on (a.identify_number=c.employee_identify_number)
            join positions d on (c.position_id=d.id)
            join work_locations e on (d.work_location_id=e.id) 
            join work_locations f on (e.work_location_parent_id=f.id)
        WHERE
            a.active_status = TRUE and
            c.active_status = TRUE and
            b.active_status = ''1''
            and f.detail_name in 
            (
            WITH RECURSIVE cte AS (
            SELECT id, detail_name 
            FROM   work_locations t
            WHERE  id = 651 and active_status =''1''
        
            UNION  ALL 
            SELECT c.id, c.detail_name
            FROM   work_locations c join cte d on d.id = c.work_location_parent_id
            where   c.active_status = ''1''  
            )
        SELECT detail_name
        FROM   cte
            )
        ' ) AS t1 ( fullname VARCHAR, user_id VARCHAR, unit VARCHAR, work_location_id INT ) 
        ) A ON ( A.user_id = assignees.username_assignee )
        JOIN (
        SELECT ID
            ,
        CASE
                
                WHEN detail_name LIKE'%BAGIAN PENGEMBANGAN PRODUK TI%' THEN
                'DEVTI' 
                WHEN detail_name LIKE'%BAGIAN RISET, LAYANAN, DAN QUALITY MANAGEMENT TI%' THEN
                'RIYANTI' 
                WHEN detail_name LIKE'%BAGIAN INFRASTRUKTUR TI%' THEN
                'ISTI' 
            END unit 
    FROM
        dblink ( 'service_desk_humanresource', '
        select id, detail_name from (
        WITH RECURSIVE cte AS (
            SELECT id, detail_name 
            FROM   work_locations t
            WHERE  id = 651 and active_status =''1''
        
            UNION  ALL 
            SELECT c.id, c.detail_name
            FROM   work_locations c join cte d on d.id = c.work_location_parent_id
            where   c.active_status = ''1'' 
            )
        SELECT id, detail_name
        FROM   cte
        order by 1
        ) a
        ' ) AS t2 ( ID INT, detail_name VARCHAR ) 
        ) b ON ( b.ID = services.work_location_id ) -- AS t1 ( fullname VARCHAR,   user_id VARCHAR,  unit VARCHAR, work_location_id int ) ) a ON ( a.work_location_id = services.work_location_id  )
    -- where  assignees.username_assignee = 'villysatria'
    -- and task_id = '3012'
    -- where  assignees.username_assignee = 'villysatria'
    -- and task_id = '3012'
        
    WHERE
        tickets.is_active = '1' 
        AND task.is_active = '1' 
        AND assignees.is_active = TRUE 
        and assignees.is_done = FALSE
        AND tickets.type_id in(2,3)
    -- 		 AND tickets.ID = 5009 
        -- and tickets.created_at >= '2022-01-01' and tickets.created_at < '2022-04-01'
    -- and tickets.description_user LIKE '%%'
    -- and unit LIKE '%DEVTI%'
    -- and to_char(tickets.created_at,'mmyyyy') = '042022'
            GROUP BY
            b.unit,
            tickets.description_user,
            fullname,
            tickets.ID,
            assignees.end_date_assignee
        
    ORDER BY
        end_date_assignee DESC 
    
        ) A 
    GROUP BY
        unit,
        description_user,
    -- 	penerima_tugas,
        ID,
        waktu_tenggat,
        status_deadline
        ORDER BY waktu_tenggat desc