efektivita_trasovani.sql

CREATE VIEW EFEKTIVITA_TRASOVANI AS 
SELECT 
    RECORD_CREATED::DATE                         AS CREATED,
    replace(split_part(QUEUE_NAME,']',1),'[','') AS KHS,
    RECORD_ID                                    AS POZITIVNI,
    KONTAKT                                      AS KONTAKT
FROM "CAMPAIGN_RECORDS_PII" 
LEFT JOIN (
           SELECT 
              REPLACE(REPLACE(FK_RECORD_ID,'["',''),'"]','') AS "POZITIVNI",
              RECORD_ID "KONTAKT"
            FROM "CAMPAIGN_RECORDS_PII" 
            WHERE "QUEUE_NAME" LIKE '%3. hovor%' 
                  AND LENGTH("POZITIVNI") > 5
          ) ON RECORD_ID = POZITIVNI
WHERE "QUEUE_NAME" LIKE '%1. hovor%' 
ORDER BY CREATED DESC, KHS, POZITIVNI
;