Postgres SQL, as run
select min(charge_period_start) as charge_period_start,
max(charge_period_end) as charge_period_end,
case when publisher_name is not null and publisher_name is distinct from invoice_issuer_name then publisher_name else provider_name end as service_provider_name,
billing_account_id as billing_account_id,
commitment_discount_id as commitment_discount_id,
commitment_discount_name as commitment_discount_name,
commitment_discount_type as commitment_discount_type,
commitment_discount_status as commitment_discount_status,
sum(billed_cost) as total_billed_cost,
sum(effective_cost) as total_effective_cost
from focus.cost_and_usage
where charge_period_start >= $1::timestamptz
and charge_period_end < $2::timestamptz
and commitment_discount_status = 'Unused'
group by 3, 4, 5, 6, 7, 8
order by total_effective_cost desc nulls last, commitment_discount_id
limit 500Parameters: $1 = period_start, $2 = period_end
Original FOCUS SQL
SELECT
MIN(ChargePeriodStart) AS ChargePeriodStart,
MAX(ChargePeriodEnd) AS ChargePeriodEnd,
ServiceProviderName,
BillingAccountId,
CommitmentDiscountId,
CommitmentDiscountType,
CommitmentDiscountStatus,
SUM(BilledCost) AS TotalBilledCost,
SUM(EffectiveCost) AS TotalEffectiveCost
FROM focus_data_table
WHERE ChargePeriodStart >= ? AND ChargePeriodEnd < ?
AND CommitmentDiscountStatus = 'Unused'
GROUP BY
ServiceProviderName,
BillingAccountId,
CommitmentDiscountId,
CommitmentDiscountType,
CommitmentDiscountStatus