Untitled

ardvarez

Apr 24th, 2025 (edited)

331

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

PostgreSQL 11.40 KB | Software | 0 0

raw download clone embed print report

--'Tan Delta dan Kapasitansi Bushing Trafo', 🟡
WITH parameter_ids AS (
SELECT id
FROM m_parameter
WHERE nama in ('Tan Delta dan Kapasitansi Bushing Trafo')
AND hide IS FALSE
),
power_transformers AS (
SELECT id
FROM m_jenis_asset
WHERE nama ILIKE '%power transf%'
),
tegangan_atribut AS (
SELECT id
FROM m_atribut
WHERE nama ILIKE '%tegangan ope%'
),
latest_inspection AS (
SELECT ti.id_asset, MAX(ti.created_date) AS latest_created
FROM t_inspeksi_parameter tip
INNER JOIN t_inspeksi ti ON ti.id = tip.id_inspeksi
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND (tip.parameter_reference_id IN (SELECT id FROM parameter_ids) or tip.parameter_reference_parent_id in (select id from parameter_ids))
--and ti.created_date >= '2025-03-26 00:00:00.342 +0700'
GROUP BY ti.id_asset
),
data_with_skor AS (
SELECT
tip.id,
tip.is_completed,
ti.id_asset,
tara.id_parameter,
tip.health_index AS hi_param,
tip.backup_health_index AS backup_hi_param,
tipi.uraian,
tipi.value,
tipi.value_koreksi,
tipi.health_index AS isian_health_index,
tara.health_index AS hi_tara,
CASE
when tipi.uraian = 'Evaluasi Tan Delta Bushing' then
case
WHEN CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) < 0.4 THEN '1 - Very Good'
WHEN CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) < 0.5 THEN '3 - Fair'
WHEN CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) < 0.7 THEN '4 - Poor'
ELSE '5 - Critical'
end
when tipi.uraian = 'Evaluasi Kapasitansi' then
case
when CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) = 1.0 THEN '2 - Good'
when CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) = 0.0 THEN '5 - Critical'
end
END AS skor_kondisi
FROM t_inspeksi ti
JOIN latest_inspection li
ON ti.id_asset = li.id_asset AND ti.created_date = li.latest_created
JOIN t_inspeksi_parameter tip
ON tip.id_inspeksi = ti.id
JOIN t_inspeksi_parameter_isian tipi
ON tipi.id_inspeksi_parameter = tip.id
JOIN t_assets_register_spesifikasi tars
ON tars.id_assets_register = ti.id_asset
JOIN t_assets_register_ahi tara
ON tara.id_asset = ti.id_asset
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND tip.parameter_reference_id IN (SELECT id FROM parameter_ids)
AND tars.id_atribut IN (SELECT id FROM tegangan_atribut)
AND tara.id_parameter IN (SELECT id FROM parameter_ids)
and tipi.health_index is not null
),
kecocokan as (
SELECT *,
CASE
WHEN skor_kondisi = isian_health_index and hi_param = hi_tara THEN 'Match'
ELSE 'Not Match'
END AS status_kecocokan
FROM data_with_skor
)
SELECT *
FROM kecocokan
where status_kecocokan = 'Not Match';
--'Index Polarisasi Trafo Tenaga', 🟡
WITH parameter_ids AS (
SELECT id
FROM m_parameter
WHERE nama in ('Index Polarisasi Trafo Tenaga')
AND hide IS FALSE
),
power_transformers AS (
SELECT id
FROM m_jenis_asset
WHERE nama ILIKE '%power transf%'
),
tegangan_atribut AS (
SELECT id
FROM m_atribut
WHERE nama ILIKE '%tegangan ope%'
),
latest_inspection AS (
SELECT ti.id_asset, MAX(ti.created_date) AS latest_created
FROM t_inspeksi_parameter tip
INNER JOIN t_inspeksi ti ON ti.id = tip.id_inspeksi
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND (tip.parameter_reference_id IN (SELECT id FROM parameter_ids) or tip.parameter_reference_parent_id in (select id from parameter_ids))
--and ti.created_date >= '2025-03-26 00:00:00.342 +0700'
GROUP BY ti.id_asset
),
data_with_skor AS (
SELECT
tip.id,
tip.is_completed,
ti.id_asset,
tara.id_parameter,
tip.health_index AS hi_param,
tip.backup_health_index AS backup_hi_param,
tipi.uraian,
tipi.value,
tipi.value_koreksi,
tipi.health_index AS isian_health_index,
tara.health_index AS hi_tara,
CASE
when tipi.uraian = 'Evaluasi IP' then
case
WHEN CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) > 2 THEN '1 - Very Good'
WHEN CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) > 1.5 THEN '2 - Good'
WHEN CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) > 1.25 THEN '3 - Fair'
WHEN CAST(coalesce (tipi.value_koreksi,tipi.value) AS double precision) > 1.1 THEN '4 - Poor'
ELSE '5 - Critical'
end
END AS skor_kondisi
FROM t_inspeksi ti
JOIN latest_inspection li
ON ti.id_asset = li.id_asset AND ti.created_date = li.latest_created
JOIN t_inspeksi_parameter tip
ON tip.id_inspeksi = ti.id
JOIN t_inspeksi_parameter_isian tipi
ON tipi.id_inspeksi_parameter = tip.id
JOIN t_assets_register_spesifikasi tars
ON tars.id_assets_register = ti.id_asset
JOIN t_assets_register_ahi tara
ON tara.id_asset = ti.id_asset
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND tip.parameter_reference_id IN (SELECT id FROM parameter_ids)
AND tars.id_atribut IN (SELECT id FROM tegangan_atribut)
AND tara.id_parameter IN (SELECT id FROM parameter_ids)
and tipi.health_index is not null
),
kecocokan as (
SELECT *,
CASE
WHEN skor_kondisi = isian_health_index and hi_param = hi_tara THEN 'Match'
ELSE 'Not Match'
END AS status_kecocokan
FROM data_with_skor
)
SELECT *
FROM kecocokan
where status_kecocokan = 'Not Match';
--'Tan Delta Belitan 3P', ⚪
--'Kapasitansi Belitan Trafo Tenaga',🟡
WITH parameter_ids AS (
SELECT id
FROM m_parameter
WHERE nama in ('Kapasitansi Belitan Trafo Tenaga')
AND hide IS FALSE
),
power_transformers AS (
SELECT id
FROM m_jenis_asset
WHERE nama ILIKE '%power transf%'
),
tegangan_atribut AS (
SELECT id
FROM m_atribut
WHERE nama ILIKE '%tegangan ope%'
),
latest_inspection AS (
SELECT ti.id_asset, MAX(ti.created_date) AS latest_created
FROM t_inspeksi_parameter tip
INNER JOIN t_inspeksi ti ON ti.id = tip.id_inspeksi
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND (tip.parameter_reference_id IN (SELECT id FROM parameter_ids) or tip.parameter_reference_parent_id in (select id from parameter_ids))
--and ti.created_date >= '2025-03-26 00:00:00.342 +0700'
GROUP BY ti.id_asset
),
data_with_skor AS (
SELECT
tip.id,
tip.is_completed,
ti.id_asset,
ti.created_date,
tara.id_parameter,
tip.health_index AS hi_param,
tip.backup_health_index AS backup_hi_param,
tipi.uraian,
tipi.value,
tipi.value_koreksi,
tipi.health_index AS isian_health_index,
tara.health_index AS hi_tara,
CASE
when tipi.uraian = 'Evaluasi Kapasitansi' then
CASE
WHEN CAST(COALESCE(tipi.value_koreksi, tipi.value) AS double precision) = 1 THEN '2 - Good'
else '4 - Poor'
END
END AS skor_kondisi
FROM t_inspeksi ti
JOIN latest_inspection li
ON ti.id_asset = li.id_asset AND ti.created_date = li.latest_created
JOIN t_inspeksi_parameter tip
ON tip.id_inspeksi = ti.id
JOIN t_inspeksi_parameter_isian tipi
ON tipi.id_inspeksi_parameter = tip.id
JOIN t_assets_register_spesifikasi tars
ON tars.id_assets_register = ti.id_asset
JOIN t_assets_register_ahi tara
ON tara.id_asset = ti.id_asset
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND tip.parameter_reference_id IN (SELECT id FROM parameter_ids)
AND tars.id_atribut IN (SELECT id FROM tegangan_atribut)
AND tara.id_parameter IN (SELECT id FROM parameter_ids)
and tipi.health_index is not null
),
kecocokan as (
SELECT *,
CASE
WHEN skor_kondisi = isian_health_index and hi_param = hi_tara THEN 'Match'
ELSE 'Not Match'
END AS status_kecocokan
FROM data_with_skor
)
SELECT *
FROM kecocokan
where status_kecocokan = 'Not Match';
--'Tegangan Tembus Minyak', 🟡
WITH parameter_ids AS (
SELECT id
FROM m_parameter
WHERE nama in ('Tegangan Tembus Minyak')
AND hide IS FALSE
),
power_transformers AS (
SELECT id
FROM m_jenis_asset
WHERE nama ILIKE '%power transf%'
),
tegangan_atribut AS (
SELECT id
FROM m_atribut
WHERE nama ILIKE '%tegangan ope%'
),
latest_inspection AS (
SELECT ti.id_asset, MAX(ti.created_date) AS latest_created
FROM t_inspeksi_parameter tip
INNER JOIN t_inspeksi ti ON ti.id = tip.id_inspeksi
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND (tip.parameter_reference_id IN (SELECT id FROM parameter_ids) or tip.parameter_reference_parent_id in (select id from parameter_ids))
--and ti.created_date >= '2025-03-26 00:00:00.342 +0700'
GROUP BY ti.id_asset
),
data_with_skor AS (
SELECT
tip.id,
tip.is_completed,
ti.id_asset,
ti.created_date,
tara.id_parameter,
tip.health_index AS hi_param,
tip.backup_health_index AS backup_hi_param,
tipi.uraian,
tipi.value,
tipi.value_koreksi,
tipi.health_index AS isian_health_index,
tara.health_index AS hi_tara,
CASE
when tipi.uraian = 'Tegangan Tembus Minyak' then
CASE
WHEN CAST(COALESCE(tipi.value_koreksi, tipi.value) AS double precision) > 60 THEN '1 - Very Good'
WHEN CAST(COALESCE(tipi.value_koreksi, tipi.value) AS double precision) > 50 THEN '2 - Good'
WHEN CAST(COALESCE(tipi.value_koreksi, tipi.value) AS double precision) > 40 THEN '3 - Fair'
else '5 - Critical'
END
END AS skor_kondisi
FROM t_inspeksi ti
JOIN latest_inspection li
ON ti.id_asset = li.id_asset AND ti.created_date = li.latest_created
JOIN t_inspeksi_parameter tip
ON tip.id_inspeksi = ti.id
JOIN t_inspeksi_parameter_isian tipi
ON tipi.id_inspeksi_parameter = tip.id
JOIN t_assets_register_spesifikasi tars
ON tars.id_assets_register = ti.id_asset
JOIN t_assets_register_ahi tara
ON tara.id_asset = ti.id_asset
WHERE ti.id_jenis_asset IN (SELECT id FROM power_transformers)
AND tip.parameter_reference_id IN (SELECT id FROM parameter_ids)
AND tars.id_atribut IN (SELECT id FROM tegangan_atribut)
AND tara.id_parameter IN (SELECT id FROM parameter_ids)
and tipi.health_index is not null
),
kecocokan as (
SELECT *,
CASE
WHEN skor_kondisi = isian_health_index and hi_param = hi_tara THEN 'Match'
ELSE 'Not Match'
END AS status_kecocokan
FROM data_with_skor
)
SELECT *
FROM kecocokan
where status_kecocokan = 'Not Match';
--'Tahanan Isolasi Belitan Trafo Tenaga', ⚪
--'Pengujian DGA Minyak OLTC'⚪

Add Comment

Please, Sign In to add comment