50 lines
1.4 KiB
MySQL
50 lines
1.4 KiB
MySQL
|
-- Views filtering all core Autotask data to CompanyCategory ID 1 (Recurring Revenue Customer)
|
||
|
|
|
||
|
|
-- Base: recurring revenue companies
|
||
|
|
CREATE OR REPLACE VIEW v_mrr_companies AS
|
||
|
|
SELECT *
|
||
|
|
FROM companies
|
||
|
|
WHERE company_category_id = 1
|
||
|
|
AND is_active = true;
|
||
|
|
|
||
|
|
-- Tickets for recurring revenue companies
|
||
|
|
CREATE OR REPLACE VIEW v_mrr_tickets AS
|
||
|
|
SELECT t.*
|
||
|
|
FROM tickets t
|
||
|
|
INNER JOIN v_mrr_companies c ON c.id = t.company_id
|
||
|
|
WHERE t.is_deleted = false;
|
||
|
|
|
||
|
|
-- Contacts for recurring revenue companies
|
||
|
|
CREATE OR REPLACE VIEW v_mrr_contacts AS
|
||
|
|
SELECT ct.*
|
||
|
|
FROM contacts ct
|
||
|
|
INNER JOIN v_mrr_companies c ON c.id = ct.company_id
|
||
|
|
WHERE ct.is_deleted = false;
|
||
|
|
|
||
|
|
-- Contracts for recurring revenue companies
|
||
|
|
CREATE OR REPLACE VIEW v_mrr_contracts AS
|
||
|
|
SELECT cn.*
|
||
|
|
FROM contracts cn
|
||
|
|
INNER JOIN v_mrr_companies c ON c.id = cn.company_id
|
||
|
|
WHERE cn.is_deleted = false;
|
||
|
|
|
||
|
|
-- Configuration items for recurring revenue companies
|
||
|
|
CREATE OR REPLACE VIEW v_mrr_configuration_items AS
|
||
|
|
SELECT ci.*
|
||
|
|
FROM configuration_items ci
|
||
|
|
INNER JOIN v_mrr_companies c ON c.id = ci.company_id
|
||
|
|
WHERE ci.is_deleted = false;
|
||
|
|
|
||
|
|
-- Time entries for recurring revenue companies (via ticket)
|
||
|
|
CREATE OR REPLACE VIEW v_mrr_time_entries AS
|
||
|
|
SELECT te.*
|
||
|
|
FROM time_entries te
|
||
|
|
INNER JOIN v_mrr_tickets t ON t.id = te.ticket_id;
|
||
|
|
|
||
|
|
-- Billing items for recurring revenue companies
|
||
|
|
CREATE OR REPLACE VIEW v_mrr_billing_items AS
|
||
|
|
SELECT bi.*
|
||
|
|
FROM billing_items bi
|
||
|
|
INNER JOIN v_mrr_companies c ON c.id = bi.company_id
|
||
|
|
WHERE bi.is_deleted = false;
|