Advertisement
Guest User

Untitled

a guest
Nov 23rd, 2017
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT calls_costs.created, SUM(calls_costs.amount_cost) FROM (
  2.   SELECT calls.created created,
  3.     CASE WHEN calls.use_call_cost = FALSE THEN
  4.               SUM(calls.duration) * calls.COST
  5.          WHEN calls.use_call_cost = TRUE THEN
  6.               COUNT(calls.COST) * calls.COST
  7.     END AS amount_cost,
  8.   calls.COST AS COST
  9.   FROM (
  10.     SELECT DISTINCT * FROM
  11.     (
  12.       SELECT
  13.         DATE_TRUNC('day', "Timestamp") AS created,
  14.         CASE
  15.         WHEN ("CallDuration" / 60) > 1
  16.           THEN round(("CallDuration" / 60), 0)
  17.         WHEN ("CallDuration" / 60) < 1
  18.           THEN 1
  19.         WHEN "CallDuration" ISNULL
  20.           THEN 0
  21.         END AS duration,
  22.         CASE
  23.         WHEN descr.use_call_cost = TRUE
  24.           THEN descr.call_cost
  25.         WHEN descr.use_call_cost = FALSE
  26.           THEN descr.minute_cost
  27.         END                            AS COST,
  28.         use_call_cost
  29.       FROM cdr_cdrblocked
  30.         JOIN cdr_description descr ON cdr_cdrblocked."Description" = descr.prefix
  31.       UNION ALL
  32.       SELECT
  33.         DATE_TRUNC('day', "Timestamp") AS created,
  34.         CASE
  35.         WHEN ("CallDuration" / 60) > 1
  36.           THEN round(("CallDuration" / 60), 0)
  37.         WHEN ("CallDuration" / 60) < 1
  38.           THEN 1
  39.         WHEN "CallDuration" ISNULL
  40.           THEN 0
  41.         END AS duration,
  42.         CASE
  43.           WHEN descr.use_call_cost = TRUE
  44.             THEN descr.call_cost
  45.           WHEN descr.use_call_cost = FALSE
  46.             THEN descr.minute_cost
  47.         END AS COST,
  48.         use_call_cost
  49.       FROM cdr_cdrerror
  50.         JOIN cdr_description descr ON cdr_cdrerror."Description" = descr.prefix
  51.       WHERE cdr_cdrerror."Description" NOTNULL AND cdr_cdrerror."b_NumberIsCorrect" = TRUE
  52.     ) cdr
  53.     WHERE
  54.       DATE(cdr.created) BETWEEN '01.06.2017' AND '23.11.2017' ORDER BY cdr.created
  55.   ) calls
  56.   GROUP BY calls.created, calls.COST, calls.use_call_cost ORDER BY calls.created
  57.   )
  58. calls_costs GROUP BY calls_costs.created;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement