Pivot pl/sql 2 - Pastebin.com

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;