Query: vpc_monthly_forecast_table

with monthly_costs as (
  select
    period_start,
    period_end,
    case
      when date_trunc('month', period_start) = date_trunc('month', CURRENT_DATE::timestamp) then 'Month to Date'
      when date_trunc('month', period_start) = date_trunc('month', CURRENT_DATE::timestamp - interval '1 month') then 'Previous Month'
      else to_char (period_start, 'Month')
    end as period_label,
    period_end::date - period_start::date as days,
    sum(unblended_cost_amount)::numeric::money as unblended_cost_amount,
    (sum(unblended_cost_amount) / (period_end::date - period_start::date))::numeric::money as average_daily_cost,
    date_part('days', date_trunc ('month', period_start) + '1 MONTH'::interval - '1 DAY'::interval) as days_in_month,
    sum(unblended_cost_amount) / (period_end::date - period_start::date ) * date_part('days', date_trunc ('month', period_start) + '1 MONTH'::interval - '1 DAY'::interval)::numeric::money as forecast_amount
  from
    aws_cost_by_service_usage_type_monthly as c
  where
    service = 'Amazon Virtual Private Cloud'
    and date_trunc('month', period_start) >= date_trunc('month', CURRENT_DATE::timestamp - interval '1 month')
  group by
    period_start,
    period_end
)
select
  period_label as "Period",
  unblended_cost_amount as "Cost",
  average_daily_cost as "Daily Avg Cost"
from
  monthly_costs
union all
select
  'This Month (Forecast)' as "Period",
  (select forecast_amount from monthly_costs where period_label = 'Month to Date') as "Cost",
  (select average_daily_cost from monthly_costs where period_label = 'Month to Date') as "Daily Avg Cost";