1 Antwort
- Neueste
- Die meisten Stimmen
- Die meisten Kommentare
0
Hello I have some corrections to the above query.
- Include Spot costs
- Has Pricing term
- Has Amortized cost, in addition to the billed cost. This is especially important if the customer bought up front or partial upfront RIs or SPs
- Savings plan negation should not be ommittted unless using amortized costs.
You still will have to replace the same name. You should also use a where clause to restruct the timeframe . For example to find Jan cost you would write: where line_item_useage_state_date >= date '2021-01-01' and line_item_useage_state_date <date '2021-01-01' and
SELECT
line_item_usage_account_id
, (CASE WHEN (line_item_usage_type LIKE '%SpotUsage%') THEN 'Spot'
WHEN (((product_usagetype LIKE '%BoxUsage%') OR (product_usagetype LIKE '%DedicatedUsage:%')) AND ("line_item_line_item_type" LIKE 'SavingsPlanCoveredUsage')) or (line_item_line_item_type = 'SavingsPlanNegation') THEN 'SavingsPlan'
WHEN (("product_usagetype" LIKE '%BoxUsage%') AND ("line_item_line_item_type" LIKE 'DiscountedUsage')) THEN 'ReservedInstance'
WHEN ((("product_usagetype" LIKE '%BoxUsage%') OR ("product_usagetype" LIKE '%DedicatedUsage:%')) AND ("line_item_line_item_type" LIKE 'Usage')) THEN 'OnDemand'
ELSE 'Other' END) "Pricing Term"
, case when line_item_usage_type like '%BoxUsage' or line_item_usage_type like '%DedicatedUsage' then product_instance_type else split_part (line_item_usage_type, ':', 2) end InstanceType
, round (sum (line_item_unblended_cost),2) BilledCost
, round (sum((CASE
WHEN line_item_usage_type LIKE '%SpotUsage%' THEN line_item_unblended_cost
WHEN ((product_usagetype LIKE '%BoxUsage%') OR (product_usagetype LIKE '%DedicatedUsage:%')) AND (line_item_line_item_type LIKE 'Usage') THEN line_item_unblended_cost
WHEN ((line_item_line_item_type LIKE 'SavingsPlanCoveredUsage')) THEN TRY_CAST(savings_plan_savings_plan_effective_cost AS double)
WHEN ((line_item_line_item_type LIKE 'DiscountedUsage')) THEN reservation_effective_cost
WHEN (line_item_line_item_type = 'SavingsPlanNegation') then 0
ELSE line_item_unblended_cost END)), 2) "AmortizedCost"
FROM <your table>
where line_item_operation like '%RunInstance%' and line_item_product_code = 'AmazonEC2'
and (product_instance_type <> '' or (line_item_usage_type like '%SpotUsage%' and line_item_line_item_type = 'Usage'))
group by 1,2,3
beantwortet vor 3 Jahren
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor einem Jahr
- AWS OFFICIALAktualisiert vor 2 Jahren
- AWS OFFICIALAktualisiert vor 2 Jahren
- AWS OFFICIALAktualisiert vor 3 Jahren