テスト仕様書

概要

本書はクライアント向けデイリーレポートが正しく動作するかを確認するためのシステムテスト・外部結合テスト仕様書です。

外部結合・システムテスト仕様書

前提条件

• ローカル環境構築済みである、もしくはテスト環境であること。
• venv環境をアクティベートしていること
• pi_datamartへの接続が可能であること。
• ターミナル上で実施年、カレントディレクトリが「src」であること。
• ローカル環境の場合、src/config/.evnが「local」、テスト環境の場合「st」となっていること。

テストケース

• ID
  SEND-SEED-001

• 操作手順

  1. 「src/daily_report_for_client/settings/{.envで指定した環境}/client.toml」の「mail_to」に、自分の宛先を設定する。
  2. (エラー時の為に)「src/config/{.envで指定した環境}/email.ini」の[error_email]セクション「it_team_address」に自分の宛先を設定する。
  3. 下記コマンドを実行する。

     python -m daily_report_for_client Seed
     

• 期待結果

  1. 自分宛に下記件名のメールが届くこと。(日付部分は昨日の日付となっていること。)

     【株式会社プレステージ・コアソリューション】応答状況のご報告（2025年06月29日分）

  2. メール内の数値がpi_datamartから正しく数値が取れていること。
     pi_datamart上での数値の確認SQL
     ※{昨日の日付}部分(2か所あり)は書き換えること

     SELECT
     CAST(SUM(COALESCE(getd_resrate."nyuuden",0)) AS INTEGER) AS nyuuden_sum,
     CAST(SUM(COALESCE(getd_resrate."outou",0)) AS INTEGER) AS outou_sum,
     round(csimd.div(SUM(COALESCE(getd_resrate."outou", 0)), SUM(COALESCE(getd_resrate."nyuuden", 0))) * 100, 2) as outou_p
     FROM(
     select to_char(getd_call_detail_set.time,'YYYY/MM/DD') as "ymd"
     ,to_char(getd_call_detail_set.time,'HH24:MI:SS') as "hms"
     ,getd_call_detail_set.service_name as "service_name"
     ,"totalAnswered" as "outou"
     ,"totalAbandoned" +"totalAnswered" -
     (case x_aband
     when '5'    then cast("abandonedWithin5" as integer)
     when '10'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)
     when '15'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)
     when '20'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)
     when '25'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)
     when '30'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
     when '40'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)
     when '50'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)
     when '60'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)
     when '90'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)
     when '120'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)
     when '180'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)
     when '180p' then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)+cast("abandonedWithinInf" as integer)
     else '0'
     end)
     as "nyuuden"
     ,"totalAbandoned" -
     (case x_aband
     when '5'    then cast("abandonedWithin5" as integer)
     when '10'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)
     when '15'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)
     when '20'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)
     when '25'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)
     when '30'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
     when '40'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)
     when '50'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)
     when '60'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)
     when '90'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)
     when '120'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)
     when '180'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)
     when '180p' then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)+cast("abandonedWithinInf" as integer)
     else '0'
     end)
     as "houki"
     ,average_time_to_answer * "totalAnswered" as "outou_sum"
     ,answered as "outou_appsum"
     ,(case x_aband
     when '5'    then cast("abandonedWithin5" as integer)
     when '10'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)
     when '15'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)
     when '20'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)
     when '25'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)
     when '30'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
     when '40'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)
     when '50'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)
     when '60'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)
     when '90'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)
     when '120'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)
     when '180'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)
     when '180p' then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)+cast("abandonedWithinInf" as integer)
     else '0'
     end) as "s_abandon"
     ,average_time_to_answer as "outou_ave"
     ,answered_within_x_seconds as "res_within_x"
     from csimd.getd_call_detail_set getd_call_detail_set
     left join csimd.getd_mst_aggattr_s getd_mst_aggattr_s
     on getd_call_detail_set."service_name" = getd_mst_aggattr_s."service_name"
     left join csimd.getd_service_performance_set getd_service_performance_set
     on getd_call_detail_set."service_name" = getd_service_performance_set."service_name"
         and getd_call_detail_set.time = getd_service_performance_set.time
         and getd_call_detail_set.region = getd_service_performance_set.region
     where getd_call_detail_set.time between TO_TIMESTAMP({昨日の日付} || ' 00:00:00', 'YYYY/MM/DD HH24:MI:SS') and TO_TIMESTAMP({昨日の日付} || ' 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
     and getd_call_detail_set.region = 'to'
     and getd_call_detail_set.service_name IN (select service_name from csimd.getd_mst_servicegrp where resrate_grp_cd = 'CSIM-YO-ROOMSUPPORT-02' and enabled = '1')
     ) getd_resrate;
     

ステージング環境でのデイリー稼働テスト(初回リリース時のみ)

前提条件

• タスクスケジューラーにてステージング環境のデイリー配信設定済みであること。
• ステージング環境に最新のソースがデプロイ済みであること。

テストケース

• ID
  ST-DAILY-SEED-001

• 操作手順
  なし

• 期待結果

  1. 下記件名のメールが届くこと。(日付部分は昨日の日付となっていること。)

     【株式会社プレステージ・コアソリューション】応答状況のご報告（2025年06月29日分）

  2. メール内の数値がpi_datamartから正しく数値が取れていること。
     pi_datamart上での数値の確認SQL
     ※{昨日の日付}部分(2か所あり)は書き換えること

     SELECT
     CAST(SUM(COALESCE(getd_resrate."nyuuden",0)) AS INTEGER) AS nyuuden_sum,
     CAST(SUM(COALESCE(getd_resrate."outou",0)) AS INTEGER) AS outou_sum,
     round(csimd.div(SUM(COALESCE(getd_resrate."outou", 0)), SUM(COALESCE(getd_resrate."nyuuden", 0))) * 100, 2) as outou_p
     FROM(
     select to_char(getd_call_detail_set.time,'YYYY/MM/DD') as "ymd"
     ,to_char(getd_call_detail_set.time,'HH24:MI:SS') as "hms"
     ,getd_call_detail_set.service_name as "service_name"
     ,"totalAnswered" as "outou"
     ,"totalAbandoned" +"totalAnswered" -
     (case x_aband
     when '5'    then cast("abandonedWithin5" as integer)
     when '10'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)
     when '15'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)
     when '20'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)
     when '25'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)
     when '30'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
     when '40'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)
     when '50'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)
     when '60'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)
     when '90'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)
     when '120'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)
     when '180'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)
     when '180p' then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)+cast("abandonedWithinInf" as integer)
     else '0'
     end)
     as "nyuuden"
     ,"totalAbandoned" -
     (case x_aband
     when '5'    then cast("abandonedWithin5" as integer)
     when '10'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)
     when '15'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)
     when '20'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)
     when '25'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)
     when '30'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
     when '40'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)
     when '50'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)
     when '60'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)
     when '90'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)
     when '120'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)
     when '180'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)
     when '180p' then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)+cast("abandonedWithinInf" as integer)
     else '0'
     end)
     as "houki"
     ,average_time_to_answer * "totalAnswered" as "outou_sum"
     ,answered as "outou_appsum"
     ,(case x_aband
     when '5'    then cast("abandonedWithin5" as integer)
     when '10'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)
     when '15'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)
     when '20'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)
     when '25'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)
     when '30'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
     when '40'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)
     when '50'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)
     when '60'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)
     when '90'   then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)
     when '120'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)
     when '180'  then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)
     when '180p' then cast("abandonedWithin5" as integer)+cast("abandonedWithin10" as integer)+cast("abandonedWithin15" as integer)+cast("abandonedWithin20" as integer)+cast("abandonedWithin25" as integer)+cast("abandonedWithin30" as integer)
         +cast("abandonedWithin40" as integer)+cast("abandonedWithin50" as integer)+cast("abandonedWithin60" as integer)+cast("abandonedWithin90" as integer)+cast("abandonedWithin120" as integer)+cast("abandonedWithin180" as integer)+cast("abandonedWithinInf" as integer)
     else '0'
     end) as "s_abandon"
     ,average_time_to_answer as "outou_ave"
     ,answered_within_x_seconds as "res_within_x"
     from csimd.getd_call_detail_set getd_call_detail_set
     left join csimd.getd_mst_aggattr_s getd_mst_aggattr_s
     on getd_call_detail_set."service_name" = getd_mst_aggattr_s."service_name"
     left join csimd.getd_service_performance_set getd_service_performance_set
     on getd_call_detail_set."service_name" = getd_service_performance_set."service_name"
         and getd_call_detail_set.time = getd_service_performance_set.time
         and getd_call_detail_set.region = getd_service_performance_set.region
     where getd_call_detail_set.time between TO_TIMESTAMP({昨日の日付} || ' 00:00:00', 'YYYY/MM/DD HH24:MI:SS') and TO_TIMESTAMP({昨日の日付} || ' 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
     and getd_call_detail_set.region = 'to'
     and getd_call_detail_set.service_name IN (select service_name from csimd.getd_mst_servicegrp where resrate_grp_cd = 'CSIM-YO-ROOMSUPPORT-02' and enabled = '1')
     ) getd_resrate;