CREATE VIEW taxation_aggregated AS
SELECT DATE_FORMAT(taxation.call_start,'%Y-%m-%d') AS call_date,
taxation.source_peer_name AS customer,
peer_to_company.company_name AS provider,
number_to_dest.tariffdesc AS destination,
sum(taxation.duration)/60 AS volume,
sum(taxation.sell_eur) + 0.76 * sum(taxation.sell_usd)
- sum(taxation.cost_eur) + 0.76 * sum(taxation.cost_usd) AS margin
FROM taxation, number_to_dest, peer_to_company
WHERE taxation.called_number = number_to_dest.called_number AND
taxation.gateway_name = peer_to_company.gateway_name
GROUP BY call_date, customer, provider, destination