~singpolyma/jmp-schemas

ref: 240b3444466fb458b517d84c83bd6a782a21934e jmp-schemas/deploy/cdr_with_charge.sql -rw-r--r-- 560 bytes
240b3444Stephen Paul Weber NOTIFY on low balance 11 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Deploy jmp:cdr_with_charge to pg
-- requires: cdr
-- requires: call_rates
-- requires: customer_plans

BEGIN;

CREATE VIEW cdr_with_charge AS
	SELECT DISTINCT ON (cdr_id)
		cdr.*,
		rate,
		(CASE WHEN cdr.disposition = 'VOICEMAIL' THEN 0
		ELSE rate * ceiling(billsec / 60.0) END) AS charge
	FROM
		cdr
		INNER JOIN customer_plans USING (customer_id)
		LEFT JOIN call_rates ON
			cdr.direction = call_rates.direction AND
			customer_plans.plan_name = call_rates.plan_name AND
			cdr.tel LIKE call_rates.prefix || '%'
	ORDER BY cdr_id, prefix DESC;

COMMIT;