Categories should always have at least a single category options.
Recommendation
Any categories without category options should either be removed from the system if they are not in use. Otherwise, appropriate category options should be added to the category.
Summary SQL Query
select 'categories_no_options' as indicator, COUNT(*)::varchar as value, (100 * COUNT(*) / (SELECT COUNT(*) FROM dataelementcategory)) || '%' as percent, 'Categories without category options' as description from dataelementcategory where categoryid not in (select distinct categoryid from categories_categoryoptions);
Details SQL Query
SELECT uid,name from dataelementcategory where categoryid not in (select distinct categoryid from categories_categoryoptions) ORDER BY name;
There should only be a single 'default' category option combination in the system. Having multiple default category option combinations may lead to irregularties in both data entry as well as analytical outputs.
Recommendation
All references to the additional default category option combination should be replaced with the desired default category option combination.
Summary SQL Query
WITH categories_additional_default_coc AS ( select coc.categoryoptioncomboid, coc.uid from categoryoptioncombo coc inner join categoryoptioncombos_categoryoptions cocco on coc.categoryoptioncomboid=cocco.categoryoptioncomboid inner join dataelementcategoryoption co on cocco.categoryoptionid=co.categoryoptionid where co.name = 'default' offset 1 ) SELECT 'categories_additional_default_coc' as indicator, COUNT(*)::varchar as value, (100 * COUNT(*) / (SELECT COUNT(*) FROM categoryoptioncombo)) || '%' as percent, 'Additional default category option combos based on category option.' as description FROM categories_additional_default_coc;
Details SQL Query
select coc.uid, coc.name from categoryoptioncombo coc inner join categoryoptioncombos_categoryoptions cocco on coc.categoryoptioncomboid=cocco.categoryoptioncomboid inner join dataelementcategoryoption co on cocco.categoryoptionid=co.categoryoptionid where co.name = 'default' offset 1;
All category options should belong to at least one category.
Recommendation
Category options which are not part of any category should be removed or alternatively should be added to an appropriate category.
Summary SQL Query
WITH category_options_no_categories AS ( SELECT uid,name FROM dataelementcategoryoption WHERE categoryoptionid NOT IN (SELECT DISTINCT categoryoptionid FROM categories_categoryoptions)) SELECT 'category_options_no_categories' as indicator, COUNT(*)::varchar as value, ROUND( 100.0 * COUNT(*) / (SELECT COUNT(*) FROM dataelementcategoryoption) ) || '%' as percent, 'Category options with no categories' as description FROM category_options_no_categories;
Details SQL Query
SELECT uid,name FROM dataelementcategoryoption WHERE categoryoptionid NOT IN (SELECT DISTINCT categoryoptionid FROM categories_categoryoptions) ORDER BY name;
Total number of dashboards in the system.
Recommendation
DHIS2 should contain useful dashboards for users.
Summary SQL Query
select 'dashboard_count' as indicator, count(*)::varchar as value, '100%' as percent, 'Total number of dashboards' as description from dashboard;
Details SQL Query
select uid,name from dashboard LIMIT 1000;
Sed vestibulum, vitae. Aliquam tincidunt morbi sit class aenean. Neque sodales vitae donec. Dis, vitae maecenas nec diam aenean, nec, sed. Tincidunt mattis hac in non donec leo dictumst et feugiat commodo, curae. Diam quis ac vestibulum nec lobortis id, in facilisi et felis.
Recommendation
Vestibulum quisque vel in dolor in proin, ipsum et urna mauris. Sit lorem dis et hac non feugiat. Mi pulvinar, curae nisi ipsum libero aliquet. Id id. Sed tortor vestibulum tincidunt facilisi elementum mauris euismod, tincidunt.
Summary SQL Query
select 'dashboard_used' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from dashboard))||'%' as percent, 'Dashboards that have been viewed 1 time or less in the past three years' as description from dashboard where uid not in (select favoriteuid from datastatisticsevent where eventtype = 'DASHBOARD_VIEW' and favoriteuid is not null and extract(year from timestamp) >= ( extract(year from now()) -3 ) group by favoriteuid having count(*) >1);
Details SQL Query
SELECT uid,name from dashboard where uid not in (select favoriteuid from datastatisticsevent where eventtype = 'DASHBOARD_VIEW' and favoriteuid is not null and extract(year from timestamp) >= ( extract(year from now()) -3 ) group by favoriteuid having count(*) >1) ORDER BY name;
All dashboards should be reviewed routinely. Dashboards that are no longer viewed routinely are not being used, or potentially being used incorrectly. The definition of "routine" may vary between implementations and therefore you can assess this over both the last 12 months as well as a pre-defined period of your choosing.
Recommendation
Dashboards that should be reviewed routinely and are not point to a broader data use issue that will not be addressed by technical means. Such dashboards may need to be kept while a process for reviewing them more frequently becomes more well defined.
Summary SQL Query
select 'dashboard_used1y' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from dashboard))||'%' as percent, 'Dashboards that have not been opened in the last 12 months' as description from dashboard where not uid in (select favoriteuid from datastatisticsevent where eventtype = 'DASHBOARD_VIEW' and favoriteuid is not null and timestamp > (now() - INTERVAL '12 months') group by favoriteuid);
Details SQL Query
SELECT uid,name from dashboard where not uid in (select favoriteuid from datastatisticsevent where eventtype = 'DASHBOARD_VIEW' and favoriteuid is not null and timestamp > (now() - INTERVAL '12 months') GROUP BY favoriteuid);
All dashboards should have content on them. Dashboards without any content do not serve any purpose, and may have been created by accident or as part of a training exercise.
Recommendation
Dashboards without content that have not been modified in the last 14 days can be removed.
Summary SQL Query
select 'dashboard_empty' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from dashboard))||'%' as percent, 'Dashboards with no content' as description from dashboard where dashboardid not in (select dashboardid from dashboard_items);
Details SQL Query
SELECT uid,name from dashboard WHERE dashboardid not in (select dashboardid from dashboard_items);
All data elements that are captured in DHIS2 should be used to produce some type of analysis output (charts, maps, tables). This can be by using them directly in an output, or by having them contribute to an indicator calculation that is used an output.
Recommendation
Data elements that are not routinely being reviewed in analysis, either directly or indirectly through indicators, should be reviewed to determine if they still need to be collected. If these are meant to be used in routine review, then associated outputs should be created using them. If these data elements are not going to be used for any type of information review, consideration should be made to either archive them or delete them.
Summary SQL Query
WITH des_no_analysis AS ( select name,uid from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select de.dataelementid from dataelement de left join indicator ind on (ind.numerator like '%'||de.uid||'%' or ind.denominator like '%'||de.uid||'%') where ind.name is not null and ind.indicatorid in (select indicatorid from datadimensionitem where indicatorid > 0)) and dataelementid not in (select dataelementoperand_dataelementid from datadimensionitem where dataelementoperand_dataelementid > 0) and dataelementid not in (select dataelementid from datadimensionitem where dataelementid > 0) order by name ) SELECT 'agg_des_no_analysis' as indicator, COUNT(*) as value, ROUND(( COUNT(*)::numeric / ( SELECT COUNT(*) FROM dataelement where domaintype = 'AGGREGATE' )::numeric) * 100)::varchar || '%' as percent, 'Aggregate data elements not used in any favourites (directly or through indicators)' as description FROM des_no_analysis;
Details SQL Query
select name,uid from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select de.dataelementid from dataelement de left join indicator ind on (ind.numerator like '%'||de.uid||'%' or ind.denominator like '%'||de.uid||'%') where ind.name is not null and ind.indicatorid in (select indicatorid from datadimensionitem where indicatorid > 0)) and dataelementid not in (select dataelementoperand_dataelementid from datadimensionitem where dataelementoperand_dataelementid > 0) and dataelementid not in (select dataelementid from datadimensionitem where dataelementid > 0) order by name;
Data elements which are part of an aggregate dataset should be assigned to at least one organisation unit.
Recommendation
If the dataset is active, then review the organisation unit assignments. If the dataset is not active, then it should be removed from the system.
Summary SQL Query
WITH des_no_orgunits AS ( select uid,name from dataelement where (dataelementid not in (select dataelementid from datasetelement)) AND domaintype = 'AGGREGATE' or (dataelementid in (select dataelementid from datasetelement where datasetid not in (select datasetid from datasetsource)) or (dataelementid in (select dataelementid from dataelement de left join datasetelement dse using (dataelementid) left join datasetsource dss using (datasetid) WHERE de.domaintype = 'AGGREGATE' group by dataelementid having count(dss.*) = 1))) order by name ) SELECT 'des_no_orgunits' as indicator, COUNT(*) as value, ROUND(( COUNT(*)::numeric / ( SELECT COUNT(*) FROM dataelement where domaintype = 'AGGREGATE' )::numeric) * 100)::varchar || '%' as percent, 'Aggregate data elements assigned to 1 or less orgunit' as description FROM des_no_orgunits;
Details SQL Query
select uid,name from dataelement where (dataelementid not in (select dataelementid from datasetelement)) AND domaintype = 'AGGREGATE' or (dataelementid in (select dataelementid from datasetelement where datasetid not in (select datasetid from datasetsource)) or (dataelementid in (select dataelementid from dataelement de left join datasetelement dse using (dataelementid) left join datasetsource dss using (datasetid) WHERE de.domaintype = 'AGGREGATE' group by dataelementid having count(dss.*) = 1))) order by name;
All data elements should be in a data element group. This allows users to find the data elements more easily in analysis apps and also contributes to having more complete data element group sets. Maintenance operations can also be made more efficient by applying bulk settings (ex. sharing) to all data elements within a data element group.
Recommendation
Data elements that are not in a data element group should be added to a relevant data element group. If the data elements are not needed, they should be deleted.
Summary SQL Query
select 'dataelement_nongrouped' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from dataelement))::varchar || '%' as percent, 'Data elements not in any data element groups.' as description from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select dataelementid from dataelementgroupmembers);
Details SQL Query
select uid,name from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select dataelementid from dataelementgroupmembers) ORDER BY name;
Recommendation
Data elements which are no longer being reported on should be removed from datasets.
Summary SQL Query
select 'aggregate_des_abandoned' as indicator,count(*)::varchar as value, (100*count(*)/(select count(*) from dataelement where domaintype = 'AGGREGATE'))||'%' as percent, 'Aggregate data elements that have not been changed in last 100 days and do not have any data values' as description from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select dataelementid from datavalue group by dataelementid) and date_part('day', now() - lastupdated::date) > 100;
Details SQL Query
select uid,name from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select dataelementid from datavalue group by dataelementid) and date_part('day', now() - lastupdated::date) > 100 ORDER BY name;
Data elements should generally always be associated with data values. If data elements exist in a data set which is active, but there are no data values associated with them, they may not be part of the data entry screens. Another possibility is that they simply do have have any data at all, because they are unable to be reported on.
Recommendation
Consider removing data elements with no data values.
Summary SQL Query
select 'aggregate_des_nodata' as indicator, count(*)::varchar as value , (100*count(*)/(select count(*) from dataelement where domaintype = 'AGGREGATE'))||'%' as percent, 'Aggregate data elements with NO data values' as description from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select DISTINCT dataelementid from datavalue);
Details SQL Query
select uid,name from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select DISTINCT dataelementid from datavalue) ORDER BY name;
Data elements that have not been recently updated with data values should consider to be deallocated from datasets. The criteria for inactive data elements has been defined as data elements with no new values in the last 3 periods based on the data set period type associated with those data elements)
Recommendation
NA
Summary SQL Query
with ds_period as (select datasetid,dataelementid, periodid from dataset ds left join period pe using(periodtypeid) left join datasetelement using(datasetid) where date_part('day', now() - pe.enddate::timestamp)::int <= 3*date_part('day', pe.enddate::timestamp - pe.startdate::timestamp)::int) select 'dataelement_nonewdata' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from dataelement where domaintype = 'AGGREGATE' ))::varchar || '%' as percent, 'Aggregate data elements with no data values in the last 3 periods (based on data set period type).' as description from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select distinct dataelementid from ds_period where (dataelementid,periodid) in (select dataelementid, periodid from datavalue) group by dataelementid);
Details SQL Query
with ds_period as (select datasetid,dataelementid, periodid from dataset ds left join period pe using(periodtypeid) left join datasetelement using(datasetid) where date_part('day', now() - pe.enddate::timestamp)::int <= 3*date_part('day', pe.enddate::timestamp - pe.startdate::timestamp)::int) select uid,name from dataelement where domaintype = 'AGGREGATE' and dataelementid not in (select distinct dataelementid from ds_period where (dataelementid,periodid) in (select dataelementid, periodid from datavalue) group by dataelementid) ORDER BY name;
Total number of datasets in the system.
Recommendation
DHIS2 should contain datasets which are useful for data entry.
Summary SQL Query
select 'dataset_count' as indicator, count(*)::varchar as value, '100%' as percent, 'Total number of data sets' as description from dataset;
Details SQL Query
SELECT uid,name from dataset ORDER BY name;
Datasets should generally be assigned to multiple organisation units, and should have been used recently (e.g. 100 days). Unused datasets may increase the response time of the data entry app. Users may also by mistake enter data into inactive datasets.
Recommendation
Datasets which are not active should be removed from the system to decrease system clutter and metadata size.
Summary SQL Query
select 'datasets_abandoned' as indicator, count(*)::varchar as value , (100*count(*)/(select count(*) from dataset)) || '%' as percent, 'Data sets that have not been changed in last 100 days and are assigned to 1 or less orgunits' as description from dataset where date_part('day', now() - lastupdated::date) > 100 and (datasetid not in (select datasetid from datasetsource) or datasetid in (select datasetid from datasetsource group by datasetid having count(*) = 1));
Details SQL Query
SELECT uid,name from dataset where date_part('day', now() - lastupdated::date) > 100 and (datasetid not in (select datasetid from datasetsource) or datasetid in (select datasetid from datasetsource group by datasetid having count(*) = 1)) ORDER BY name;
All active datasets should have data values associated with them. Active data sets are data sets which should be currently used (this is defined as within the last 3 reporting periods) to collect data.
Recommendation
Summary SQL Query
with ds_lastupdate as (select datasetid, avg(date_part('day', pe.enddate::timestamp - pe.startdate::timestamp))::int as interval from dataset ds left join period pe using(periodtypeid) group by datasetid), ds_period as (select datasetid, dataelementid, periodid from dataset ds left join period pe using(periodtypeid) left join datasetelement using(datasetid) where date_part('day', now() - pe.enddate::timestamp)::int <= 3*date_part('day', pe.enddate::timestamp - pe.startdate::timestamp)::int) select 'dataset_nonewdata' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from dataset))::varchar || '%' as percent, 'Data sets with no data values in the last 3 periods (based on data set period type).' as description from dataset where datasetid not in (select distinct datasetid from ds_period where (dataelementid,periodid) in (select dataelementid, periodid from datavalue) group by datasetid);
Details SQL Query
with ds_lastupdate as (select datasetid, avg(date_part('day', pe.enddate::timestamp - pe.startdate::timestamp))::int as interval from dataset ds left join period pe using(periodtypeid) group by datasetid), ds_period as (select datasetid, dataelementid, periodid from dataset ds left join period pe using(periodtypeid) left join datasetelement using(datasetid) where date_part('day', now() - pe.enddate::timestamp)::int <= 3*date_part('day', pe.enddate::timestamp - pe.startdate::timestamp)::int) SELECT uid,name from dataset where datasetid not in (select distinct datasetid from ds_period where (dataelementid,periodid) in (select dataelementid, periodid from datavalue) group by datasetid);
Overview of the number of indicators in the system.
Recommendation
DHIS2 should contain useful indicators for users.
Summary SQL Query
select 'indicator_count' as indicator, count(*)::varchar as value, '100%' as percent, 'Total count of indicators' as description from indicator;
Details SQL Query
SELECT uid,name FROM indicator ORDER BY name;
All indicators should be in an indicator group. This allows users to find the indicators more easily in analysis apps and also contributes to having more complete indicators group sets. Maintenance operations can also be made more efficient by applying bulk settings (ex. sharing, filtering) to all indicators within an indicator group.
Recommendation
Indicators that are not in a indicator group should be added to a relevant indicator group. If the indicators are not needed, they should be deleted.
Summary SQL Query
select 'indicator_nongrouped' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from indicator))||'%' as percent, 'Indicators not in any groups' as description from indicator where indicatorid not in (select indicatorid from indicatorgroupmembers);
Details SQL Query
SELECT uid,name FROM indicator where indicatorid not in (select indicatorid from indicatorgroupmembers) ORDER BY name;
All indicators that are calculated should be used to produce some type of analysis output (charts, maps, tables). This can be by using them directly in an output or providing direct feedback in a dataset.
Recommendation
Indicators that are not routinely being reviewed in analysis, either in an output or data set, should be reviewed to determine if they still need to be calculated. If these are meant to be used for routine review, then associated outputs should be created using them. If these indicators are not going to be used for any type of information review, consideration should be made to either archive them or delete them.
Summary SQL Query
select 'indicator_nouse' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from indicator))||'%' as percent, 'Indicators not used in favourites OR data sets' as description from indicator where indicatorid not in (select indicatorid from datadimensionitem where indicatorid IS NOT NULL) and indicatorid not in (select indicatorid from datasetindicators);
Details SQL Query
SELECT uid,name FROM indicator where indicatorid not in (select indicatorid from datadimensionitem where indicatorid not in (select indicatorid from datadimensionitem where indicatorid IS NOT NULL) and indicatorid not in (select indicatorid from datasetindicators) ORDER BY name;
All indicators that are calculated should be used to produce some type of analysis output (charts, maps, tables). This can be by using them directly in an output or providing direct feedback in a dataset.
Recommendation
Indicators that are not routinely being reviewed in analysis, either in an output or data set, should be reviewed to determine if they still need to be calculated. If these are meant to be used for routine review, then associated outputs should be created using them. If these indicators are not going to be used for any type of information review, consideration should be made to either archive them or delete them.
Summary SQL Query
select 'indicator_noanalysis' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from indicator))||'%' as percent, 'Indicators not used in favourites' as description from indicator where indicatorid not in (select indicatorid from datadimensionitem where indicatorid IS NOT NULL);
Details SQL Query
SELECT uid,name FROM indicator where indicatorid not in (select indicatorid from datadimensionitem where indicatorid IS NOT NULL) ORDER BY name;
Option sets should be used for some purpose either with attributes, data elements, or comments.
Recommendation
Consider deleting unused option sets, or alternatively, ensure that they have been properly assigned.
Summary SQL Query
WITH options_sets_unused as ( SELECT DISTINCT uid,name FROM optionset where optionsetid NOT IN ( SELECT DISTINCT optionsetid from attribute where optionsetid IS NOT NULL UNION SELECT DISTINCT optionsetid from dataelement where optionsetid IS NOT NULL UNION SELECT DISTINCT commentoptionsetid from dataelement where commentoptionsetid IS NOT NULL )) SELECT 'options_sets_unused' as indicator, COUNT(*)::varchar as value, (100 * COUNT(*) / (SELECT COUNT(*) FROM optionset)) || '%' as percent, 'Unused option sets' as description from options_sets_unused;
Details SQL Query
SELECT DISTINCT uid,name FROM optionset where optionsetid NOT IN ( SELECT DISTINCT optionsetid from attribute where optionsetid IS NOT NULL UNION SELECT DISTINCT optionsetid from dataelement where optionsetid IS NOT NULL UNION SELECT DISTINCT commentoptionsetid from dataelement where commentoptionsetid IS NOT NULL ORDER BY name;
All option sets should generally include at least two items. Empty option sets serve no purpose.
Recommendation
Options should either be added to the option set, or the option set should be deleted.
Summary SQL Query
WITH options_sets_empty as ( SELECT uid,name from optionset where optionsetid NOT IN (SELECT DISTINCT optionsetid from optionvalue WHERE optionsetid IS NOT NULL) ) SELECT 'options_sets_empty' as indicator, COUNT(*)::varchar as value, (100 * COUNT(*) / (SELECT COUNT(*) FROM optionset)) || '%' as percent, 'Empty option sets' as description from options_sets_empty;
Details SQL Query
SELECT uid,name from optionset where optionsetid NOT IN (SELECT DISTINCT optionsetid from optionvalue WHERE optionsetid IS NOT NULL) ORDER BY name;
Option sets contain options which can be ordered. The sort_order property should always start with 1 and have a sequential sequence. If there are three options in the option set, then the sort order should be 1,2,3. In certain circumstances, options may be deleted from an option set, and the sort order may become corrupted. This may lead to a situation where it becomes impossible to update the option set from the maintenance app, and may lead to problems when attempting to using the option set in the data entry app.
Recommendation
If it is possible to open the option set in the maintenance app, you can resort the option set, which should correct the problem. Another possible solution is to directly update the sort_order property of in the optionset table in the database, ensuring that a valid sequence is present for all options in the option set.
Summary SQL Query
WITH option_sets_wrong_sort_order as ( SELECT DISTINCT optionsetid, sort_order, row_number FROM ( SELECT optionsetid,sort_order, row_number() over ( PARTITION BY optionsetid ORDER BY sort_order) as row_number from optionvalue ) as foo where sort_order != row_number ORDER BY optionsetid, sort_order ) SELECT 'option_sets_wrong_sort_order' as indicator, ( SELECT COUNT(*) from (SELECT DISTINCT optionsetid from option_sets_wrong_sort_order ) as baz ) as value, (100 * (SELECT COUNT(*) from (SELECT DISTINCT optionsetid from option_sets_wrong_sort_order ) as bar) / (SELECT COUNT(*) from optionset)) || '%' as percent, 'Option sets with incorrect sort order' as description FROM option_sets_wrong_sort_order LIMIT 1;
Details SQL Query
SELECT a.uid,a.name,b.sort_order,b.row_number from optionset a INNER JOIN ( SELECT DISTINCT optionsetid, sort_order, row_number FROM ( SELECT optionsetid,sort_order, row_number() over ( PARTITION BY optionsetid ORDER BY sort_order) as row_number from optionvalue ) as foo where sort_order != row_number ORDER BY optionsetid, sort_order ) b on a.optionsetid = b.optionsetid;
Any organisation unit groups which have been marked as compulsory should contain all organization units in the system. If certain organization units are omitted from the groups in the group set, this may cause irregularities in analytical outputs, such as data being omitted.
Recommendation
Add all organization units to exactly one group within a compulsory organization unit group.
Summary SQL Query
with orgunit_compulsory_group_count as (select organisationunitid as orgunitid,count(ogs.orgunitgroupsetid) as actual, (select count(*) as expected from orgunitgroupset where compulsory = true) from orgunitgroupsetmembers ogsm left join orgunitgroupset ogs using(orgunitgroupsetid) left join orgunitgroupmembers ogm using(orgunitgroupid) where ogs.compulsory = true group by ogm.organisationunitid) select 'orgunit_notincompulsory' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from organisationunit))||'%' as percent, 'Orgunits that are not in all compulsory orgunit group sets' as description from orgunit_compulsory_group_count where actual != expected;
Details SQL Query
with orgunit_compulsory_group_count as (select organisationunitid as orgunitid,count(ogs.orgunitgroupsetid) as actual, (select count(*) as expected from orgunitgroupset where compulsory = true) from orgunitgroupsetmembers ogsm left join orgunitgroupset ogs using(orgunitgroupsetid) left join orgunitgroupmembers ogm using(orgunitgroupid) where ogs.compulsory = true group by ogm.organisationunitid) SELECT uid,name from organisationunit where organisationunitid NOT IN (SELECT organisationunitid from orgunit_compulsory_group_count) ORDER BY name;
If a closing date has been defined for an organisation unit, it should always be after the opening date (if one has been defined).
Recommendation
Alter either the opening or closing date of all affected organisation units so that the closing date is after the opening date.
Summary SQL Query
with orgunit_future_opening_date as (select uid,name,openingdate,closeddate from organisationunit where openingdate > closeddate ) select 'orgunit_openingdate_gt_closeddate' as indicator, count(*)::varchar as value, (100*count(*)/(select count(*) from organisationunit))||'%' as percent, 'Organisation units which have an opening date later than the closed date.' as description from orgunit_future_opening_date;
Details SQL Query
select uid,name from organisationunit where openingdate > closeddate ORDER BY name;
Trailing spaces in organisation units are superfluous.
Recommendation
If the number of affected organisation units is small, the easiest remedy is to correct them directly from the user interface. Another possible option would be to replace all of the multiple spaces using SQL.
Summary SQL Query
WITH orgunits_trailing_spaces as ( SELECT uid, name from organisationunit where name ~('+$') ) SELECT 'orgunits_trailing_spaces' as indicator, COUNT(*)::varchar as value, (100 * COUNT(*) / (SELECT COUNT(*) FROM organisationunit)) || '%' as percent, 'Organisation units should not have trailing spaces' as description from orgunits_trailing_spaces;
Details SQL Query
SELECT uid, name from organisationunit where name ~('+$') ORDER BY name;
Names of organisation units should not contain multiple spaces. They are superfluous and may complicate the location of organisation units when they are searched.
Recommendation
If the number of affected organisation units is small, the easiest remedy is to correct them directly from the user interface. Another possible option would be to replace all of the multiple spaces using SQL.
Summary SQL Query
WITH orgunits_trailing_spaces as ( SELECT uid, name from organisationunit where name ~('') ) SELECT 'orgunits_trailing_spaces' as indicator, COUNT(*)::varchar as value, (100 * COUNT(*) / (SELECT COUNT(*) FROM organisationunit)) || '%' as percent, 'Organisation units should not have multiple spaces in their names' as description from orgunits_trailing_spaces;
Details SQL Query
SELECT uid, name from organisationunit where name ~('') ORDER BY name;