Need help with my schema. I have 2 different types of PMS(single pms and group pms). With the work_logs and assignees tables they have a column called type which is goup pm and single pm and use the record_id column as the key for pms.id and pms.grouped_pm id. Is my shcema correct? Below along with the schema i am using is also a sql function for what i am trying to do in power bi
CREATE FUNCTION pm_aggregate_report (_facility_ids uuid[], _risk_ids uuid[] DEFAULT NULL::uuid[], _assignee_ids uuid[] DEFAULT NULL::uuid[], _start_date date DEFAULT NULL::date, _end_date date DEFAULT NULL::date) RETURNS TABLE(facility character varying, pm_id uuid, grouped_pm boolean, risk_id uuid, risk character varying, pm_status_id uuid, user_id uuid, assignee text, completed_by uuid, total_labor bigint)
VOLATILE
AS $dbvis$
/*
USAGE:
SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], NULL, NULL, '2018-09-30', '2019-09-30')
SELECT * FROM pm_aggregate_report(ARRAY['8d735407-744f-4940-bab8-67e40f929c5a']::UUID[], NULL, NULL, NULL, NULL)
--filter on assignees
SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], NULL, ARRAY['eb38dca5-45a6-44db-99ae-aa88aca8c06e']::UUID[], '2018-09-30', '2019-09-30')
--filter on risks
SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], ARRAY['31b838f0-730b-4415-80bd-e84f4323ab10', 'b3c10d9f-7529-468e-811f-1dbbde63bbfd']::UUID[], NULL, '2018-09-30', '2019-09-30')
SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], ARRAY['b3c10d9f-7529-468e-811f-1dbbde63bbfd']::UUID[], NULL, '2018-09-30', '2019-09-30')
*/
BEGIN
CREATE TEMP TABLE tmp_pm_aggregate
AS
SELECT DISTINCT
COALESCE(gp.facility_id, a.facility_id) as facility_id,
COALESCE(p.grouped_pm_id, p.id) as pm_id,
CASE WHEN p.grouped_pm_id IS NULL THEN false ELSE true END as grouped_pm,
COALESCE(gp.risk_id, a.risk_id) as risk_id,
COALESCE(gp.pm_status_id, p.pm_status_id) as pm_status_id,
COALESCE(gass.user_id, sass.user_id) as user_id,
COALESCE(gp.completed_by, p.completed_by) as completed_by
FROM pms p
JOIN assets a
ON p.asset_id = a.id
LEFT JOIN grouped_pms gp
ON p.grouped_pm_id = gp.id
LEFT JOIN assignees sass
ON p.id = sass.record_id
AND sass.type = 'single_pm'
LEFT JOIN assignees gass
ON p.grouped_pm_id = gass.record_id
AND gass.type = 'grouped_pm'
LEFT JOIN users u
ON (sass.user_id = u.id OR gass.user_id = u.id)
WHERE a.facility_id = ANY(_facility_ids)
AND NOT a.is_component
AND COALESCE(gp.pm_status_id, p.pm_status_id) in ('f9bdfc17-3bb5-4ec0-8477-24ef05ea3b9b', '06fc910c-3d07-4284-8f6e-8fb3873f5333')
AND COALESCE(gp.completion_date, p.completion_date) BETWEEN COALESCE(_start_date, '1/1/2000') AND COALESCE(_end_date, '1/1/3000')
AND COALESCE(gp.show_date, p.show_date) <= CURRENT_TIMESTAMP
AND COALESCE(gass.user_id, sass.user_id) IS NOT NULL
AND u.user_type_id != 'ec823d98-7023-4908-8006-2e33ddf2c11b' --exclude shared_services users
AND (_risk_ids IS NULL OR COALESCE(gp.risk_id, a.risk_id) = ANY(_risk_ids))
AND (_assignee_ids IS NULL OR COALESCE(gass.user_id, sass.user_id) = ANY(_assignee_ids));
RETURN QUERY
SELECT
f.name as facility,
t.pm_id,
t.grouped_pm,
t.risk_id,
r.name as risk,
t.pm_status_id,
t.user_id,
u.name_last || ', ' || u.name_first as assignee,
t.completed_by,
COALESCE(gwl.total_labor, swl.total_labor) as total_labor
FROM tmp_pm_aggregate t
JOIN facilities f
ON t.facility_id = f.id
JOIN risks r
ON t.risk_id = r.id
JOIN users u
ON t.user_id = u.id
LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
FROM work_logs wl
WHERE wl.type = 'single_pm'
GROUP BY wl.record_id, wl.user_id) as swl
ON t.pm_id = swl.record_id
AND t.user_id = swl.user_id
AND t.grouped_pm = false
LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
FROM work_logs wl
WHERE wl.type = 'grouped_pm'
GROUP BY wl.record_id, wl.user_id) as gwl
ON t.pm_id = gwl.record_id
AND t.user_id = gwl.user_id
AND t.grouped_pm = true
ORDER BY facility,
assignee,
risk;
DROP TABLE tmp_pm_aggregate;
END;
$dbvis$ LANGUAGE plpgsql
