Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT calls_costs.created, SUM(calls_costs.amount_cost) FROM (
- SELECT calls.created created,
- CASE WHEN calls.use_call_cost = FALSE THEN
- SUM(calls.duration) * calls.COST
- WHEN calls.use_call_cost = TRUE THEN
- COUNT(calls.COST) * calls.COST
- END AS amount_cost,
- calls.COST AS COST
- FROM (
- SELECT DISTINCT * FROM
- (
- SELECT
- DATE_TRUNC('day', "Timestamp") AS created,
- CASE
- WHEN ("CallDuration" / 60) > 1
- THEN round(("CallDuration" / 60), 0)
- WHEN ("CallDuration" / 60) < 1
- THEN 1
- WHEN "CallDuration" ISNULL
- THEN 0
- END AS duration,
- CASE
- WHEN descr.use_call_cost = TRUE
- THEN descr.call_cost
- WHEN descr.use_call_cost = FALSE
- THEN descr.minute_cost
- END AS COST,
- use_call_cost
- FROM cdr_cdrblocked
- JOIN cdr_description descr ON cdr_cdrblocked."Description" = descr.prefix
- UNION ALL
- SELECT
- DATE_TRUNC('day', "Timestamp") AS created,
- CASE
- WHEN ("CallDuration" / 60) > 1
- THEN round(("CallDuration" / 60), 0)
- WHEN ("CallDuration" / 60) < 1
- THEN 1
- WHEN "CallDuration" ISNULL
- THEN 0
- END AS duration,
- CASE
- WHEN descr.use_call_cost = TRUE
- THEN descr.call_cost
- WHEN descr.use_call_cost = FALSE
- THEN descr.minute_cost
- END AS COST,
- use_call_cost
- FROM cdr_cdrerror
- JOIN cdr_description descr ON cdr_cdrerror."Description" = descr.prefix
- WHERE cdr_cdrerror."Description" NOTNULL AND cdr_cdrerror."b_NumberIsCorrect" = TRUE
- ) cdr
- WHERE
- DATE(cdr.created) BETWEEN '01.06.2017' AND '23.11.2017' ORDER BY cdr.created
- ) calls
- GROUP BY calls.created, calls.COST, calls.use_call_cost ORDER BY calls.created
- )
- calls_costs GROUP BY calls_costs.created;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement