diff options
Diffstat (limited to 'db/migrations/0296_special_cannonball.sql')
| -rw-r--r-- | db/migrations/0296_special_cannonball.sql | 154 |
1 files changed, 154 insertions, 0 deletions
diff --git a/db/migrations/0296_special_cannonball.sql b/db/migrations/0296_special_cannonball.sql new file mode 100644 index 00000000..d25b37f0 --- /dev/null +++ b/db/migrations/0296_special_cannonball.sql @@ -0,0 +1,154 @@ +DROP VIEW "risks"."risks_view";--> statement-breakpoint +CREATE VIEW "risks"."risks_view" AS (select "risks"."risk_events"."id", "risks"."risk_events"."event_type", "risks"."risk_events"."vendor_id", "vendors"."vendor_code", "vendors"."vendor_name", "vendors"."tax_id", "risks"."risk_events"."provider", "risks"."risk_events"."content", + CASE + WHEN "risks"."risk_events"."event_type" = '종합등급' THEN + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '종합등급' + AND re2.occurred_at < "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '종합등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "prev_rating_total", + CASE + WHEN "risks"."risk_events"."event_type" = '종합등급' THEN + "risks"."risk_events"."content" + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '종합등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "cur_rating_total", + CASE + WHEN "risks"."risk_events"."event_type" = '신용등급' THEN + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '신용등급' + AND re2.occurred_at < "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '신용등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "prev_rating_credit", + CASE + WHEN "risks"."risk_events"."event_type" = '신용등급' THEN + "risks"."risk_events"."content" + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '신용등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "cur_rating_credit", + CASE + WHEN "risks"."risk_events"."event_type" = '현금흐름등급' THEN + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '현금흐름등급' + AND re2.occurred_at < "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '현금흐름등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "prev_rating_cashflow", + CASE + WHEN "risks"."risk_events"."event_type" = '현금흐름등급' THEN + "risks"."risk_events"."content" + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = '현금흐름등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "cur_rating_cashflow", + CASE + WHEN "risks"."risk_events"."event_type" = 'WATCH등급' THEN + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = 'WATCH등급' + AND re2.occurred_at < "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = 'WATCH등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "prev_rating_watch", + CASE + WHEN "risks"."risk_events"."event_type" = 'WATCH등급' THEN + "risks"."risk_events"."content" + ELSE + COALESCE(( + SELECT content + FROM "risks".risk_events re2 + WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" + AND re2.event_type = 'WATCH등급' + AND re2.occurred_at <= "risks"."risk_events"."occurred_at" + ORDER BY re2.occurred_at DESC + LIMIT 1 + ), '-') + END + as "cur_rating_watch", "risks"."risk_events"."event_status", "risks"."risk_events"."manager_id", "users"."name", "risks"."risk_events"."admin_comment", "risks"."risk_events"."occurred_at" from "risks"."risk_events" left join "vendors" on "vendors"."id" = "risks"."risk_events"."vendor_id" left join "users" on "users"."id" = "risks"."risk_events"."manager_id");
\ No newline at end of file |