For the reports site, we are simplifying guideline check reporting slightly, so that the only possible guideline check statuses are:
Pass
Fail
Not applicable/data unavailable
In order to maintain clarity and consistency, we'd like to update existing guideline check reporting so that this simplification logic occurs in the source table. Specifically, let's add a column called reports_site_status to fct_daily_organization_combined_guideline_checks (or probably the long table upstream of that), with logic roughly as follows:
CASE status
WHEN "PASS" THEN "PASS"
WHEN "FAIL" THEN "FAIL"
ELSE "NOT APPLICABLE/DATA UNAVAILABLE"
END AS reports_site_status
Along with this update, lets update fct_monthly_reports_site_organization_guideline_checks to refer to this new status column.
Acceptance criteria
fct_daily_organization_combined_guideline_checks has a new column called "reports_site_status" or similar
fct_monthly_reports_site_organization_guideline_checks refer to this column, preferably continuing to output the column with the name "status", so that downstream logic won't need to change.
Background
For the reports site, we are simplifying guideline check reporting slightly, so that the only possible guideline check statuses are:
In order to maintain clarity and consistency, we'd like to update existing guideline check reporting so that this simplification logic occurs in the source table. Specifically, let's add a column called reports_site_status to
fct_daily_organization_combined_guideline_checks
(or probably the long table upstream of that), with logic roughly as follows:Along with this update, lets update
fct_monthly_reports_site_organization_guideline_checks
to refer to this new status column.Acceptance criteria