SELECT
SELECT
concat(c.gfrn,’_’,b.collateral_type_code) as Anchor,
concat(c.gfrn,’_’,b.collateral_type_code) as Anchor,
b.cob_date,
b.cob_date,
c.gfrn,
c.gfrn,
b.collateral_type_code,
b.collateral_type_code,
b.collateral_currency_code,
b.collateral_currency_code,
MAX(
CASE
WHEN b.re_hypothecatable=”Rehypothecatable” THEN ‘Y’
WHEN b.re_hypothecatable=”Non-Rehypothecatable” THEN ‘N’
END) as re_hypotheticable,
  END) AS volatility_haircut_amount_ccy_133,
(CASE
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usd
WHEN b.collateral_currency_code=”USD” THEN .
ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
ELSE . / a.tran_to_func_ccy_conv_rate
END) AS volatility_haircut_amount_ccy_133,
END) AS ,
(CASE
(CASE
WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
WHEN b.collateral_currency_code=”USD” THEN .
ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rate
ELSE . / a.tran_to_func_ccy_conv_rate
END) AS net_collateral_value_ccy_134,
END) AS ,
(CASE
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
WHEN b.collateral_currency_code=”USD” THEN a.
ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
ELSE a. / a.tran_to_func_ccy_conv_rate
END) AS ccy_haircut_amount_ccy_156,
END) AS
END) AS correlation_haircut_amount_ccy_157
FROM gfolyrsk_cr_reporting.om_cr_bbk_collateral_native_fact a
FROM gfolyrsk_cr_reporting.om_cr_bbk_collateral_native_fact a
JOIN gfolyrsk_cr_managed.om_cr_bbk_collateral_fact b
JOIN gfolyrsk_cr_managed.om_cr_bbk_collateral_fact b
ON a.cr_banking_collateral_native_sk = b.cr_banking_collateral_sk
ON a.cr_banking_collateral_native_sk = b.cr_banking_collateral_sk
LEFT JOIN gfolyrsk_cr_managed.om_cr_facility_dim c
LEFT JOIN gfolyrsk_cr_managed.om_cr_facility_dim c
ON b.facility_id = c.facility_id
ON b.facility_id = c.facility_id
WHERE b.cob_date=”20250916″
WHERE b.cob_date=”20250916″
AND b.source_system_name=”CitiRisk Collateral”
AND b.source_system_name=”CitiRisk Collateral”
GROUP BY — Group by every item in the SELECT list
  concat(c.gfrn,’_’,b.collateral_type_code),
  b.cob_date,
  c.gfrn,
  b.collateral_type_code,
  b.collateral_currency_code,
  b.pre_haircut_collateral_amt_usd,
  b.pre_haircut_collateral_amt_local_ccy,
  a.volatility_haircut_amount_usd,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usdlogo
    ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END),
  b.net_held_amount,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
    ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rateÂ
  END),
  a.ccy_haircut_amount_usd,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
    ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END),
  a.correlation_haircut_amount_usd,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.correlation_haircut_amount_usd
    ELSE a.correlation_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END);
________________________________________________________________________________–
SELECT
 concat(c.gfrn,’_’,b.collateral_type_code) as Anchor,
  b.cob_date,
  c.gfrn,
  b.collateral_type_code,
  b.collateral_currency_code,
  b.pre_haircut_collateral_amt_usd AS gross_collateral_amount_usd_36,
  b.pre_haircut_collateral_amt_local_ccy AS gross_collateral_amount_local_ccy_36,
  a.volatility_haircut_amount_usd AS volatility_haircut_amount_usd_133,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usdÂ
    ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END) AS volatility_haircut_amount_ccy_133,
 b.net_held_amount AS net_collateral_value_usd_134,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
    ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rateÂ
  END) AS net_collateral_value_ccy_134,
a.ccy_haircut_amount_usd AS ccy_haircut_amount_usd_156,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
    ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END) AS ccy_haircut_amount_ccy_156,
  a.correlation_haircut_amount_usd AS correlation_haircut_amount_usd_157,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.correlation_haircut_amount_usd
    ELSE a.correlation_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END) AS correlation_haircut_amount_ccy_157
FROM gfolyrsk_cr_reporting.om_cr_bbk_collateral_native_fact a
JOIN gfolyrsk_cr_managed.om_cr_bbk_collateral_fact b
  ON a.cr_banking_collateral_native_sk = b.cr_banking_collateral_sk
LEFT JOIN gfolyrsk_cr_managed.om_cr_facility_dim c
  ON b.facility_id = c.facility_id
WHERE b.cob_date=”20250916″
 AND b.source_system_name=”CitiRisk Collateral”
GROUP BY — Group by every item in the SELECT list
  concat(c.gfrn,’_’,b.collateral_type_code),
  b.cob_date,
  c.gfrn,
  b.collateral_type_code,
  b.collateral_currency_code,
  b.pre_haircut_collateral_amt_usd,
  b.pre_haircut_collateral_amt_local_ccy,
  a.volatility_haircut_amount_usd,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usdlogo
    ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END),
  b.net_held_amount,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
    ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rateÂ
  END),
  a.ccy_haircut_amount_usd,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
    ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END),
  a.correlation_haircut_amount_usd,
  (CASEÂ
    WHEN b.collateral_currency_code=”USD” THEN a.correlation_haircut_amount_usd
    ELSE a.correlation_haircut_amount_usd / a.tran_to_func_ccy_conv_rateÂ
  END);
________________________________________________________________________________–
