Pivot pl/sql 2

SELECT
     "Accounts"."FullName"
    ,"Accounts"."ShortName"
    ,"Accounts"."INN"
    ,"Accounts"."OGRN"
    ,"Accounts"."BIC"
    ,"Accounts"."RegNum"
    ,"Accounts"."Code"
    ,"Messages"."Id"
    ,m."RegistrationNumber" AS "Регистрационный номер"
    ,"DictCatalogTasks"."Code" AS "Код задачи"
    ,"DictCatalogTasks"."Name" AS "Наименование задачи",
    TO_CHAR(COALESCE("1","10")+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss') AS "Отправлено",
    TO_CHAR("3"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss') AS "Ошибка",
    TO_CHAR("2"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss') AS "Загружено",
    TO_CHAR("4"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss') AS "Принято в обработку",
    TO_CHAR("5"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss') AS "Зарегистрировано",
    TO_CHAR("6"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss') AS "Отклонено"
    ,CASE WHEN
      date_part('hour',
        (SELECT MAX("DateReceipt")
         FROM dbo."MessageReceipts" AS mr
         WHERE mr."MessageId" = m."Id" AND mr."StatusEcId" IN ('DCF0CE75-E3DA-4573-9DBF-D36C7B50F096', '782651DF-6FB7-48BC-85F0-C0420DC1A76C'))-
        (SELECT MAX("DateReceipt") FROM dbo."MessageReceipts" AS mr
                            WHERE mr."MessageId" = m."Id"
                              AND mr."StatusEcId" IN ('75B8BD15-B52E-4827-8CFB-38C479C6C537'))) *60+
        date_part('minute',
        (SELECT MAX("DateReceipt")
         FROM dbo."MessageReceipts" AS mr
         WHERE mr."MessageId" = m."Id" AND mr."StatusEcId" IN ('DCF0CE75-E3DA-4573-9DBF-D36C7B50F096', '782651DF-6FB7-48BC-85F0-C0420DC1A76C'))-
        (SELECT MAX("DateReceipt") FROM dbo."MessageReceipts" AS mr
                            WHERE mr."MessageId" = m."Id"
                              AND mr."StatusEcId" IN ('75B8BD15-B52E-4827-8CFB-38C479C6C537'))) > 120
    THEN 'Более двух часов (Задержка)'
     WHEN date_part('hour',
                    (SELECT MAX("DateReceipt") FROM dbo."MessageReceipts" AS mr WHERE mr."MessageId" = m."Id" AND mr."StatusEcId" IN ('DCF0CE75-E3DA-4573-9DBF-D36C7B50F096', '782651DF-6FB7-48BC-85F0-C0420DC1A76C')) -
                    (SELECT MAX("DateReceipt") FROM dbo."MessageReceipts" AS mr WHERE mr."MessageId" = m."Id" AND mr."StatusEcId" IN ('75B8BD15-B52E-4827-8CFB-38C479C6C537'))) * 60 +
          date_part('minute',
                    (SELECT MAX("DateReceipt") FROM dbo."MessageReceipts" AS mr WHERE mr."MessageId" = m."Id" AND mr."StatusEcId" IN ('DCF0CE75-E3DA-4573-9DBF-D36C7B50F096', '782651DF-6FB7-48BC-85F0-C0420DC1A76C')) -
                    (SELECT MAX("DateReceipt") FROM dbo."MessageReceipts" AS mr WHERE mr."MessageId" = m."Id" AND mr."StatusEcId" IN ('75B8BD15-B52E-4827-8CFB-38C479C6C537'))) <= 120
    THEN 'Менее двух часов (Штатно)'
    END "Получение последней ответной квитанции"
    ,(SELECT COUNT("StatusEcId") FROM dbo."MessageReceipts" AS mr WHERE m."Id" = mr."MessageId" AND mr."StatusEcId" = 'DCF0CE75-E3DA-4573-9DBF-D36C7B50F096') AS "Кол-во квитанций Зарегистрировано"
    ,(SELECT COUNT("StatusEcId") FROM dbo."MessageReceipts" AS mr WHERE m."Id" = mr."MessageId" AND mr."StatusEcId" = '782651DF-6FB7-48BC-85F0-C0420DC1A76C') AS "Кол-во квитанций Отклонено"
    ,CASE WHEN "5" IS NULL AND "6" IS NULL THEN 'нет квитанций'
          WHEN "5" IS NOT NULL OR "6" IS NOT NULL THEN 'есть квитанции'
    END "rezultat"

FROM

  (SELECT * FROM  crosstab(
       'SELECT r."MessageId", coalesce(s."Value",r."ReceiptStatus") ReceiptStatus, max(r."DateReceipt") "DateReceipt"
        FROM dbo."MessageReceipts" r
        left join dbo."StatusEc" s on s."Id" = r."StatusEcId"
      group by r."MessageId", coalesce(s."Value",r."ReceiptStatus")',
      $$SELECT unnest('{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 18, 99}'::int[])$$)
 AS SourceTable ("MessageId" uuid, "0" TIMESTAMP, "1" TIMESTAMP,"2" TIMESTAMP,"3" TIMESTAMP,"4" TIMESTAMP,"5" TIMESTAMP,"6" TIMESTAMP,
                 "7" TIMESTAMP,"8" TIMESTAMP,"9" TIMESTAMP,"10" TIMESTAMP,"18" TIMESTAMP,"99" TIMESTAMP)
  )  "PivotTable"
JOIN (
    SELECT m.*, COALESCE(s."Value",m."Status") "CombinedStatus"
    FROM dbo."Messages" m
    left join dbo."StatusEc" s ON s."Id" = m."StatusEcId"
) AS m ON "PivotTable"."MessageId" = m."Id"
JOIN dbo."Messages" ON "PivotTable"."MessageId" = "Messages"."Id"
INNER JOIN dbo."MessageData" ON "Messages"."Id" = "MessageData"."MessageId"
JOIN dbo."Accounts" ON dbo."Messages"."OwnerId" = "Accounts"."Id"
JOIN dbo."DictCatalogTasks" ON dbo."Messages"."CatalogTaskId" = dbo."DictCatalogTasks"."Id"
WHERE dbo."DictCatalogTasks"."Id" IN
('7867C287-08B4-466A-8E5A-2B19CCF4EDE1', '1CEDFA1C-AEC4-4A00-BBD5-599DC054A14D')
AND (dbo."Messages"."CreationDate" BETWEEN '2024-07-07 21:00:00.0000000' AND '2024-07-08 21:00:00.0000000')
--and (dbo."Messages"."CreationDate" between '2024-02-20 21:00:00.0000000' and '2024-02-21 21:00:00.0000000')
AND (m."CombinedStatus") <> 0 AND (m."CombinedStatus") <> 8 AND (m."CombinedStatus") <> 9
ORDER BY dbo."Messages"."CreationDate" DESC;