ratings.appear_subpoena
Y
Normal
N
1000
100
-
2024/12/24 16:15:10.943
-
2024/12/24 16:15:10.943
Table input
Get variables
Y
Get variables
Select values
Y
Select values
Insert / update
Y
Get variables
GetVariable
Y
1
none
-1
REG_ID
-1
none
String
592
256
Insert / update
InsertUpdate
Y
1
none
100
ervu-dashboard
=
id_region
REG_ID
ratings
appear_mil_com
appear_mil_com
Y
appear_mil_com_percent
appear_mil_com_percent
Y
id_region
region_id
N
N
976
256
Select values
SelectValues
Y
1
none
N
REG_ID
REG_ID
String
-2
-2
false
false
768
256
Table input
TableInput
Y
1
none
postgres.subpoena
N
0
WITH
subpoena_data AS (
SELECT
s.id AS subpoena_id,
s.create_date,
s.department_id AS recruitment_id,
s.status_id,
sr.type,
ssh.code AS sub_stat_hist,
s.send_date,
sh.date_time::timestamp AS history_date,
ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY sh.date_time DESC) AS rn
FROM public.subpoena s
JOIN subpoena_status ss ON s.status_id = ss.id
JOIN subpoena_reason AS sr ON sr.id = s.reason_id
JOIN subpoena_history AS sh ON sh.subpoena_id = s.id
JOIN subpoena_status AS ssh ON ssh.id = sh.status_id
WHERE ('${VK_ARRAY}' IS NULL
OR '${VK_ARRAY}' = ''
OR s.department_id = ANY (
string_to_array(
trim(both '{}' FROM replace('${VK_ARRAY}', ' ', '')), ','
)::uuid[]
)
)
AND sr.type IS NOT NULL
AND ssh.code NOT IN ('3.7','3.72', '3.71')
AND EXISTS (
SELECT 1
FROM subpoena_history sh2
WHERE sh2.subpoena_id = s.id
AND sh2.status_id = (SELECT id FROM subpoena_status WHERE code = '3')
)
),
last_status AS (
SELECT *
FROM subpoena_data
WHERE rn = 1
),
season_separations AS (
SELECT distinct
ls.recruitment_id,
ls.create_date,
ls.sub_stat_hist,
ls.subpoena_id,
ls.history_date
FROM last_status ls
WHERE ls.create_date >= DATE_TRUNC('year', CURRENT_DATE)
)
SELECT
'${REG_ID}' AS region_id,
COUNT(*) FILTER (WHERE tt.sub_stat_hist IN ('4.1', '4.2')) AS appear_mil_com,
ROUND(
COUNT(*) FILTER (WHERE tt.sub_stat_hist IN ('4.1', '4.2'))::NUMERIC / NULLIF(COUNT(*), 0) * 100, 2
) AS appear_mil_com_percent
FROM season_separations tt
HAVING COUNT(*) FILTER (WHERE tt.sub_stat_hist IN ('4.1', '4.2')) > 0;
Y
416
256