Reporting Ringkasan

SELECT 
                          CAST(AVG(lama_pengerjaan) AS DECIMAL(8,2)) rata_rata_pengerjaan,
                          COUNT(status_lewat_deadline) AS status_deadline,
                          SUM(CASE 
                                    WHEN status_lewat_deadline = 'melewati batas' THEN 1 
                                    ELSE 0 
                                    END) AS melewati_batas,
                          SUM(CASE
                                    WHEN status_lewat_deadline = 'tidak melewati batas' THEN 1
                                    ELSE 0
                                    END) AS tidak_melewati_batas,
                          A.layanan AS Layanan
                          FROM (SELECT DISTINCT 
        CAST(A.ID AS VARCHAR) AS No_Tiket,
        CAST(ARRAY_AGG (l.keyword_name) AS VARCHAR) keyword,
        -- 	 (case when ( MAX ( j.created_at ) > d.end_date_assignee) then 'melewati batas'
            (case when extract(epoch from (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 5) 
                - (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 2))/3600 > e.sla then 'melewati batas'
        else 'tidak melewati batas'
        end) as status_lewat_deadline,
            cast(extract(epoch from (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 4) 
                - (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 2))/3600 AS decimal(8,2)) lama_pengerjaan,	
            cast(extract(epoch from (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 5) 
                - (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 2))/3600 AS decimal(8,2)) lama_ticket_sampai_ditutup,
            A.username_user AS user_name,
            A.feedback_user,
            CAST(A.score AS VARCHAR),
            regexp_replace( description_user, E'<[^>]+>', '', 'gi' ) AS deskripsi_tiket,
            f.name AS Layanan,
            e.NAME AS Sub_Layanan,
            p.name AS Prioritas,
            h.NAME AS Tujuan,
            G.NAME AS klasifikas,
            b.username_assignor AS Petugas_assign,
            d.username_assignee AS penerima_tugas,
            CAST(A.created_at AS VARCHAR) AS Tanggal_Pembuatan,
            CAST(d.created_at AS VARCHAR) AS Tanggal_Penugasan,
            CAST(d.end_date_assignee AS VARCHAR) AS Tanggal_Deadline,
            CAST(d.updated_at AS VARCHAR) AS tanggal_selesai_eksekutor,
            CAST(MAX ( j.updated_at ) AS VARCHAR) AS tanggal_penutup_tiket,
            C.task_description AS resolusi,
            CAST(e.sla AS VARCHAR) sla_layanan
        FROM
            (
            SELECT DISTINCT
                username_user,
                type_id,
                sub_service_id,
                score,
                requester,
                reporter,
                rating,
                is_active,
                ID,
                feedback_user,
                description_user,
                created_by,
                created_at 
            FROM
                tickets
            )
            A LEFT JOIN assignments b ON A.ID = b.ticket_id
                            JOIN priority p ON b.priority_id = p.id
            JOIN task C ON b.ID = C.assignment_id
            JOIN assignees d ON C.ID = d.task_id
            JOIN sub_service e ON A.sub_service_id = e.ID 
            JOIN services f ON e.service_id = f.ID 
            JOIN classifications G ON b.classification_id = G.ID 
            JOIN purpose h ON b.purpose_id = h.ID 
            JOIN types i ON A.type_id = i.ID 
            JOIN ticket_status_history j ON A.ID = j.ticket_id AND j.type_id = A.type_id 
            join mapping_keyword k on (a.id = k.tiket_id)
            join keyword l on (l.id = k.keyword_id)
        WHERE
        -- 	A.created_at <= CURRENT_TIMESTAMP - INTERVAL '120 day ago' 
        -- 	AND 
            a.id > 79 
            AND d.task_pic = TRUE
            and d.task_pic = true
            
            AND h.id = 
            AND G.id = 
            AND f.id = 
            AND e.id = 
            AND j.type_id = 
            AND p.id = 
            AND d.username_assignee = 
            AND TO_CHAR(A.created_at,'YYYY-MM-DD') >= ''
            AND TO_CHAR(A.created_at,'YYYY-MM-DD') <= ''
            AND TO_CHAR(j.created_at,'YYYY-MM-DD') >= ''
            AND TO_CHAR(j.created_at,'YYYY-MM-DD') >= ''
        
        GROUP BY
            A.ID,
        -- 	l.keyword_name,
            A.username_user,
            A.feedback_user,
            A.score,
            A.description_user,
            f.name,
            e.NAME,
            h.NAME,
            G.NAME,
            p.name,
            b.username_assignor,
            d.username_assignee,
            A.created_at,
            d.end_date_assignee,
            d.updated_at,
            d.created_at,
            C.task_description,
            e.sla
        ORDER BY
        f.name) A 
            
        
                          GROUP BY A.layanan