Conversão de dólar

Objetivo: Utilizar ptax para converter billing dólar para alguma moeda conforme a necessidade. Neste campo existem dois fatores, acrescentar um ptax para o Marketplace e acrescentar um ptax para outros itens do billing

Caso de uso: Criar visualização para mostrar o custo do billing em R$ ou outra moeda.

SQL:

CASE WHEN "bill/billingentity" = 'Marketplace' THEN CASE WHEN "bill/billingperiodstartdate" = CAST('2024-06-01' AS DATE) THEN 6.5541155 WHEN "bill/billingperiodstartdate" = CAST('2024-07-01' AS DATE) THEN 7.5547712 WHEN "bill/billingperiodstartdate" = CAST('2024-08-01' AS DATE) THEN 8.6327960 WHEN "bill/billingperiodstartdate" = CAST('2024-09-01' AS DATE) THEN 7.7832092 WHEN "bill/billingperiodstartdate" = CAST('2024-10-01' AS DATE) THEN 7.990898 WHEN "bill/billingperiodstartdate" = CAST('2024-11-01' AS DATE) THEN 8.99089 WHEN "bill/billingperiodstartdate" = CAST('2024-12-01' AS DATE) THEN 7.99089 WHEN "bill/billingperiodstartdate" = CAST('2025-01-01' AS DATE) THEN 6.813503 WHEN "bill/billingperiodstartdate" = CAST('2025-02-01' AS DATE) THEN 5.2655620 WHEN "bill/billingperiodstartdate" = CAST('2025-03-01' AS DATE) THEN 7.8920 WHEN "bill/billingperiodstartdate" = CAST('2025-04-01' AS DATE) THEN 7.107108 WHEN "bill/billingperiodstartdate" = CAST('2025-05-01' AS DATE) THEN 9.272041 ELSE 5.83768409038 END ELSE CASE WHEN "bill/billingperiodstartdate" = CAST('2024-06-01' AS DATE) THEN 5.451265425 WHEN "bill/billingperiodstartdate" = CAST('2024-07-01' AS DATE) THEN 5.59990108722 WHEN "bill/billingperiodstartdate" = CAST('2024-08-01' AS DATE) THEN 5.60968690955 WHEN "bill/billingperiodstartdate" = CAST('2024-09-01' AS DATE) THEN 5.59648214329 WHEN "bill/billingperiodstartdate" = CAST('2024-10-01' AS DATE) THEN 5.680644 WHEN "bill/billingperiodstartdate" = CAST('2024-11-01' AS DATE) THEN 5.380644 WHEN "bill/billingperiodstartdate" = CAST('2024-12-01' AS DATE) THEN 5.280644 WHEN "bill/billingperiodstartdate" = CAST('2025-01-01' AS DATE) THEN 5.00579 WHEN "bill/billingperiodstartdate" = CAST('2025-02-01' AS DATE) THEN 5.056643 WHEN "bill/billingperiodstartdate" = CAST('2025-03-01' AS DATE) THEN 5.03128133367 WHEN "bill/billingperiodstartdate" = CAST('2025-04-01' AS DATE) THEN 5.17799225 WHEN "bill/billingperiodstartdate" = CAST('2025-05-01' AS DATE) THEN 5.1929545213 ELSE 5.680644 END END * "lineitem/netunblendedcost"