| 1 | DROP VIEW IF EXISTS public.v_customer_addon_history;
|
|---|
| 2 |
|
|---|
| 3 | CREATE VIEW public.v_customer_addon_history AS
|
|---|
| 4 | SELECT
|
|---|
| 5 | c.customer_id,
|
|---|
| 6 | CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
|
|---|
| 7 | a.account_number,
|
|---|
| 8 | s.subscription_number,
|
|---|
| 9 | p.plan_name,
|
|---|
| 10 | sa.subscription_addon_id,
|
|---|
| 11 | ad.addon_name,
|
|---|
| 12 | ad.addon_type,
|
|---|
| 13 | ad.allowance_value,
|
|---|
| 14 | ad.allowance_unit,
|
|---|
| 15 | ad.is_recurring,
|
|---|
| 16 | sa.price_at_activation,
|
|---|
| 17 | sa.activation_date,
|
|---|
| 18 | sa.deactivation_date,
|
|---|
| 19 | sa.status,
|
|---|
| 20 | CASE WHEN sa.deactivation_date IS NULL THEN CURRENT_DATE ELSE sa.deactivation_date END - sa.activation_date + 1 AS active_days
|
|---|
| 21 | FROM public.customers c
|
|---|
| 22 | JOIN public.accounts a ON a.customer_id = c.customer_id
|
|---|
| 23 | JOIN public.subscriptions s ON s.account_id = a.account_id
|
|---|
| 24 | JOIN public.plans p ON p.plan_id = s.plan_id
|
|---|
| 25 | JOIN public.subscription_addons sa ON sa.subscription_id = s.subscription_id
|
|---|
| 26 | JOIN public.addons ad ON ad.addon_id = sa.addon_id;
|
|---|
| 27 |
|
|---|
