ToBeAddressed - x-ian/dha-mis-migration GitHub Wiki

stuff

4: forms / certificates / letters - 4: supervision form (part 1): much, much slower (unusable?) art_supervision_mainreport_run calls art_supervision_mainreport art_supervision_subreport_survival report art_supervision_mainreport_v3

5 supply mgmt 1 create / edit DR 2. check supplies needed too slow psm_regim_consum_growth_stocks_sum

10 - supply hub / spoke sites fails, but not needed anymore

8 - list all tb reg data error in both old and new version

remove If Me!chk_expiry_header = True as a relict of stock report?

Admin Frontend

concept_set and concept creation

Unsure at the same time a new concept_set with new concepts can be created. If yes, it might fail because of different ID generation.

Data Entry: Outstanding, untested, ...

DE Invalid queries

  • art_clinic_obs_drug_set

Form_htc_provider_obs htc_prov_id_AfterUpdate

Form_obs_dim_set.form_section_select_AfterUpdate

Comment requery line to avoid the weird runtime error 2485. Unclear if not invoking this requery has any other negative side effect. Maybe it has something to do with Debug settings (e.g. Break on all errors)???

Supervision analysis: Outstanding, unknown, untested, unclear

To be addressed

Analyse Invalid queries

  • art_clinic_obs_outc_site_quart_new
  • art_clinic_old
  • art_regimens
  • check_cd4
  • concept_lev_rep_obs
  • htc_site_agg
  • map_regimen_supply_rule_frmdata
  • psm_distribution_list_accpac
  • psm_DL6_diff
  • psm_DL6_diff_error
  • psm_DL6_diff_error_sites
  • psm_DL6_receive
  • psm_relo_item_frmdata
  • psm_relo_transact_frmdata
  • psm_relo_transact_item_review
  • psm_rule_supply_item_frmdata
  • survival_set_data
  • tb_data_year_quarter
Supply Mgmt - Dist round - Check supplies needed

The SQL statement could not be executed because it contains ambiguous outer joins. To enforce one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. Unable to document Query 'psm_regim_consum_growth' Continue?

Slightly ugly solution

See Query dependency hierarchy

Maybe psm_relocate_adjust_tmptbl as base table for psm_regim_consum_growth_stocks is the problem?

Plenty of references to calculated columns, need to be rewritten for SQL

psm_regim_consum_growth_stocks_mssql

SELECT view_psm_regim_consum_growth.art_clinic_obs_id, view_psm_regim_consum_growth.year_quarter_id, view_psm_regim_consum_growth.hdepartment_id, view_psm_regim_consum_growth.supply_item_ID, view_psm_regim_consum_growth.consum_now, view_psm_regim_consum_growth.consum_grow, view_psm_regim_consum_growth.consum_min, IIf([consum_need]>Nz([consum_need_min],0),[consum_need],[consum_need_min]) AS consum_need_adjust_min, art_drug_stocks.units_instock, art_drug_stocks.units_exp6m, Val(Nz([adjust_units],0)) AS units_adjust, DateDiff("d",[quarter_stopdate],[visit_date])/30.44 AS visit_interval, [visit_interval]*([consum_now]+[consum_grow]*[visit_interval]/2) AS visit_interval_consum, Round(Nz([units_instock],0)+[visit_interval_consum],0) AS units_endq, [units_endq]+Nz([units_adjust],0)-Nz([units_doomed_b4_consum_date],0) AS units_endq_adjust, IIf([units_endq_adjust]>=0,[units_endq_adjust],0) AS units_endq_adjust_pos, [consum_need_adjust_min]-[units_endq_adjust_pos] AS supply_need, IIf(([hservice_paed]=False And [item_paed]=True) Or ([hservice_fp]=False And [item_fp]=True),0,1) AS supply_item_allow, IIf([supply_need]>=0,[supply_need],0)*[supply_item_allow] AS supply_need_pos, art_drug_stocks.expiry_date_min, DateAdd("m",-1,[expiry_date_min]) AS expiry_date_min_back, IIf([units_exp6m]>=Abs(Nz([relo_out],0)+Nz([dispos],0)),[units_exp6m]-Abs(Nz([relo_out],0)+Nz([dispos],0)),0) AS units_exp6m_adj, Round(IIf([consum_now]>[consum_min],[consum_now],[consum_min])*IIf([expiry_date_min_back]>[visit_date],([expiry_date_min_back]-[visit_date])/30.44,0),0) AS units_consum_b4_expiry, IIf([units_consum_b4_expiry]<[units_exp6m_adj] And [expiry_date_min_back]<[consum_date],[units_exp6m_adj]-[units_consum_b4_expiry],0) AS units_doomed_b4_consum_date
FROM (((view_psm_regim_consum_growth LEFT JOIN art_drug_stocks ON (view_psm_regim_consum_growth.supply_item_ID = art_drug_stocks.supply_item_ID) AND (view_psm_regim_consum_growth.art_clinic_obs_id = art_drug_stocks.art_clinic_obs_id)) INNER JOIN view_supply_item_paed_fp ON view_psm_regim_consum_growth.supply_item_ID = view_supply_item_paed_fp.supply_item_id) INNER JOIN view_code_hdepartment_ARTpaed_FP ON view_psm_regim_consum_growth.hdepartment_id = view_code_hdepartment_ARTpaed_FP.hdepartment_id) LEFT JOIN psm_relocate_adjust_tmptbl ON (view_psm_regim_consum_growth.supply_item_ID = psm_relocate_adjust_tmptbl.supply_item_id) AND (view_psm_regim_consum_growth.art_clinic_obs_id = psm_relocate_adjust_tmptbl.art_clinic_obs_id);

psm_regim_consum_growth_stocks_sum_mssql

SELECT psm_regim_consum_growth_stocks_mssql.supply_item_ID, Sum(psm_regim_consum_growth_stocks_mssql.supply_need_pos) AS supply_need_total
FROM psm_regim_consum_growth_stocks_mssql
GROUP BY psm_regim_consum_growth_stocks_mssql.supply_item_ID;

CREATE VIEW [dbo].[view_code_hdepartment_ART]
AS
SELECT        dbo.code_hdepartment.ID AS hdepartment_ID, dbo.code_hdepartment.hfacility_id, dbo.code_year_quarter.year, dbo.code_year_quarter.quarter, 
                         dbo.code_year_quarter.ID
FROM            dbo.code_year_quarter INNER JOIN
                         dbo.code_hfacility INNER JOIN
                         dbo.code_hdepartment ON dbo.code_hfacility.ID = dbo.code_hdepartment.hfacility_id ON 
                         dbo.code_year_quarter.quarter_startdate >= dbo.code_hdepartment.service_start
WHERE        (dbo.code_hdepartment.hservmode = 309) AND (dbo.code_hfacility.Voided = 0) AND (dbo.code_hdepartment.Voided = 0) AND 
                         (dbo.code_hdepartment.service_end IS NULL) AND (dbo.code_hdepartment.service_end IS NULL OR
                         dbo.code_hdepartment.service_end <= dbo.code_year_quarter.quarter_stopdate) AND (dbo.code_hdepartment.hservice = 294)


CREATE VIEW [dbo].[view_code_hdepartment_ARTpaed_FP]
AS
SELECT code_hdepartment_ART.ID AS hdepartment_id, 
code_hdepartment_ART.hservice_paed,
IIf((view_code_hdepartment_FP.hfacility_id is not null),0,1) AS hservice_fp
FROM code_hdepartment AS code_hdepartment_ART 
LEFT JOIN view_code_hdepartment_FP ON code_hdepartment_ART.hfacility_id = view_code_hdepartment_FP.hfacility_id
WHERE (((code_hdepartment_ART.hservice)=294)
AND ((code_hdepartment_ART.Voided)=0) 
AND ((code_hdepartment_ART.hservmode)=309) 
AND ((code_hdepartment_ART.service_end) Is Null));

CREATE VIEW [dbo].[view_code_hdepartment_FP]
AS
SELECT        hfacility_id
FROM            dbo.code_hdepartment AS code_hdepartment_ART
WHERE        (hservice = 1334) AND (Voided = 0) AND (hservmode = 309) AND (service_end IS NULL)
GROUP BY hfacility_id


CREATE VIEW [dbo].[view_psm_regim_consum_growth]
AS
SELECT
dbo.view_psm_regim_consum_now.art_clinic_obs_id, 
dbo.view_psm_regim_consum_now.year_quarter_id, 
dbo.view_psm_regim_consum_now.hdepartment_id, 
dbo.view_psm_regim_consum_now.visit_date, 
dbo.view_psm_regim_consum_now.quarter_stopdate, 
dbo.view_psm_regim_consum_now.supply_item_ID, 
dbo.view_psm_regim_consum_now.consum_min, 
dbo.view_psm_regim_consum_now.Consume_month AS consum_now, 
IIf(dbo.view_psm_regim_consum_now.growth_allow=1,(((dbo.view_psm_regim_consum_now.Consume_month-(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)))+((Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0))-(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0))))/2),0) AS consum_grow, 
dbo.view_psm_regim_consum_now.consum_min*DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44 AS consum_need_min, 
(((dbo.view_psm_regim_consum_now.Consume_month-(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)))+((Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0))-(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0))))/2) AS consum_growth_calc, 
(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)) AS consum_prev1, 
(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0)) AS consum_prev2, 
DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44*(dbo.view_psm_regim_consum_now.Consume_month+IIf(dbo.view_psm_regim_consum_now.growth_allow=1,(((dbo.view_psm_regim_consum_now.Consume_month-(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)))+((Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0))-(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0))))/2),0)*DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44/2) AS consum_need,
DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44 AS supply_months, 
'01/01/2016' AS consum_date
FROM            dbo.view_psm_regim_consum_now LEFT OUTER JOIN
                         dbo.view_psm_regim_consum_prev2 ON dbo.view_psm_regim_consum_now.hdepartment_id = dbo.view_psm_regim_consum_prev2.hdepartment_id AND 
                         dbo.view_psm_regim_consum_now.map_regimen_supply_ID = dbo.view_psm_regim_consum_prev2.map_regimen_supply_ID LEFT OUTER JOIN
                         dbo.view_psm_regim_consum_prev1 ON dbo.view_psm_regim_consum_now.hdepartment_id = dbo.view_psm_regim_consum_prev1.hdepartment_id AND 
                         dbo.view_psm_regim_consum_now.map_regimen_supply_ID = dbo.view_psm_regim_consum_prev1.map_regimen_supply_ID



CREATE VIEW [dbo].[view_psm_regim_consum_now]
AS
SELECT        dbo.art_clinic_obs.ID AS art_clinic_obs_id, dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, MIN(dbo.art_clinic_obs.visit_date) AS visit_date, 
                         MIN(dbo.code_year_quarter.quarter_stopdate) AS quarter_stopdate, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         dbo.map_regimen_supply.supply_item_ID, COALESCE (SUM(dbo.obs.data_value * dbo.map_regimen_supply_rule.unit_month), 0) AS Consume_month, 
                         dbo.map_regimen_supply.consum_min, dbo.map_regimen_supply.growth_allow
FROM            dbo.map_regimen_supply INNER JOIN
                         dbo.code_hdepartment INNER JOIN
                         dbo.code_year_quarter INNER JOIN
                         dbo.art_clinic_obs ON dbo.code_year_quarter.ID = dbo.art_clinic_obs.year_quarter_id INNER JOIN
                         dbo.obs_dimensions INNER JOIN
                         dbo.map_regimen_supply_rule ON dbo.obs_dimensions.period_report = dbo.map_regimen_supply_rule.period_report AND 
                         dbo.obs_dimensions.sub_group = dbo.map_regimen_supply_rule.sub_group INNER JOIN
                         dbo.obs ON dbo.map_regimen_supply_rule.patient_regimen = dbo.obs.data_element AND dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID ON 
                         dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id ON dbo.code_hdepartment.ID = dbo.art_clinic_obs.hdepartment_id ON 
                         dbo.map_regimen_supply.ID = dbo.map_regimen_supply_rule.map_regimen_supply_ID
WHERE        (dbo.map_regimen_supply_rule.active = 1) AND (dbo.code_year_quarter.ID = 66)
GROUP BY dbo.art_clinic_obs.ID, dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         dbo.map_regimen_supply.supply_item_ID, dbo.map_regimen_supply.consum_min, dbo.map_regimen_supply.growth_allow


CREATE VIEW [dbo].[view_psm_regim_consum_prev1]
AS
SELECT        dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         COALESCE (SUM(dbo.obs.data_value * dbo.map_regimen_supply_rule.unit_month), 0) AS Consume_month
FROM            dbo.art_clinic_obs INNER JOIN
                         dbo.obs_dimensions INNER JOIN
                         dbo.map_regimen_supply_rule INNER JOIN
                         dbo.obs ON dbo.map_regimen_supply_rule.patient_regimen = dbo.obs.data_element ON 
                         dbo.obs_dimensions.period_report = dbo.map_regimen_supply_rule.period_report AND dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID AND 
                         dbo.obs_dimensions.sub_group = dbo.map_regimen_supply_rule.sub_group ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id
WHERE        (dbo.art_clinic_obs.year_quarter_id = 66 - 1) AND (dbo.map_regimen_supply_rule.active = 0)
GROUP BY dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID


CREATE VIEW [dbo].[view_psm_regim_consum_prev2]
AS
SELECT        dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         COALESCE (SUM(dbo.obs.data_value * dbo.map_regimen_supply_rule.unit_month), 0) AS Consume_month
FROM            dbo.art_clinic_obs INNER JOIN
                         dbo.obs_dimensions INNER JOIN
                         dbo.obs INNER JOIN
                         dbo.map_regimen_supply_rule ON dbo.obs.data_element = dbo.map_regimen_supply_rule.patient_regimen ON 
                         dbo.obs_dimensions.period_report = dbo.map_regimen_supply_rule.period_report AND dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID AND 
                         dbo.obs_dimensions.sub_group = dbo.map_regimen_supply_rule.sub_group ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id
WHERE        (dbo.art_clinic_obs.year_quarter_id = 66 - 2) AND (dbo.map_regimen_supply_rule.active = 0)
GROUP BY dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID



CREATE VIEW [dbo].[view_supply_item_paed_fp]
AS
SELECT supply_item.ID AS supply_item_id,
 supply_item.paed_form AS item_paed, 
IIf([concept_ID_parent]=362,1,0) AS item_fp
FROM concept INNER JOIN supply_item ON concept.ID = supply_item.item
WHERE (((supply_item.void)=0));
code_hdepartment_spokehub

Query no longer used; did not optimize

missing, ODBC fail: Msg 1011, Level 16, State 1, Line 1 The correlation name 'concept_district' is specified multiple times in a FROM clause.

Self-join of the same query including multiple sub queries. Looks like the generated SQL doesn't properly re-alias them so that these subqueries do not receive unique names.

Moving the underlying query code_hdepartment_hub_spoke to a MSSQL view. The previously used Access function StrConv is not available in MSSQL. Therefore a user defined function dbo.IniCap is created and then used to simulate something at least close to similar (InitCap is the name of the Oracle equivalent). Sadly the SSMS Query Designer is replacing the SQL function (?) CONCAT with multiple arguments to the canonical form { fn CONCAT('', '')} which only takes 2 arguments and changes the handling of NULL values. Therefore this view is created with direct SQL.

http://stackoverflow.com/questions/16567121/more-than-2-columns-in-a-concat-function http://stackoverflow.com/questions/19339538/sql-server-return-string-procedure-initcap

CREATE FUNCTION dbo.InitCap(@string varchar(30))
RETURNS VARCHAR(30)
WITH SCHEMABINDING
AS
BEGIN
    RETURN UPPER(LEFT(@string,1)) + LOWER(SUBSTRING(@string, 2, 8000));
END;
GO

CREATE VIEW [dbo].[view_code_hdepartment_hub_spoke]
AS
SELECT        dbo.code_hdepartment.ID AS hdepartment_ID, dbo.InitCap(LTRIM(RTRIM(CONCAT(dbo.code_hfacility.hfacility_name, ' ', 
                         dbo.code_hdepartment.name_healthdept, ', ', concept_district.concept_name)))) AS site_name, 
                         dbo.code_hdepartment.hdepartment_hub_id
FROM            dbo.concept AS concept_district INNER JOIN
                         dbo.code_hfacility INNER JOIN
                         dbo.code_hdepartment ON dbo.code_hfacility.ID = dbo.code_hdepartment.hfacility_id ON concept_district.ID = dbo.code_hfacility.district
WHERE        (dbo.code_hfacility.Voided = 0) AND (dbo.code_hdepartment.Voided = 0) AND (dbo.code_hdepartment.service_end IS NULL) AND 
                         (dbo.code_hdepartment.hservmode = 309) AND (dbo.code_hdepartment.hservice = 294)

GO

Once this view is linked into the Access frontend, the Access query code_hdepartment_spokehub can be changed to the new MSSQL view:

SELECT code_hdepartment_hub.hdepartment_hub_id, code_hdepartment_spoke.site_name AS site_name_spoke, code_hdepartment_hub.site_name AS site_name_hub
FROM dbo_view_code_hdepartment_hub_spoke AS code_hdepartment_spoke LEFT JOIN dbo_view_code_hdepartment_hub_spoke AS code_hdepartment_hub ON code_hdepartment_spoke.hdepartment_hub_id = code_hdepartment_hub.hdepartment_ID;

Obsolete queries

Queries that either didn't work at all or run much slower, but aren't needed anyways.

  • calc_test
  • calc_test_result
  • pmtct_data

Unclear queries / Slow or too slow queries

chk_outc chk_outc_new chk_outc_new_outlier chk_art_cohort_outcome_prev_now chk_surv_reg chk_surv_reggroup chk_surv_reg_diff chk_surv_de_reg_agg htc_set_site_district_greater_allq report_oi_quart_cum_expos_result_zone art_drug_stock_consum_year_quarter report_pmtct_trend_union report_pmtct_trend report_pmtct_trend_chart report_pmtct_trend_xtab supply_item_ART_levels_agg art_clinic_obs_certif_sched complete_join_report_drug_stocks art_cum_to_death

Reports

report_tb_ks_oc_cm

fails with ODBC error

recreate distribution list

happened once, unclear if a problem

you tried to assign the null value to a variable that is not a variant datat type

Review draft allocation list

Lock finalized list

copied

psm_relocate unique index violation

Unique index violation for combined index uniq on columns with migration assistant (on combined index hdepartment_id_send, hdepartment_id_recv, supply_item_ID, move_date, expiry_date (optional, nullable)). Optional/nullable index field are treated differently between Access and MSSQL. While MSSQL reports a constraint violation from the second and above records with a NULL value, Access allows them. There are 78 violations with each 2 or 3 rows resulting in 169 problematic records.

While not always desirable from the business logic, the current behaviour needs to be preserved (as there are valid cases of multiple relocations of the same drug at the same time).

For the data migration the constraint in Access should be weakened by removing the expiry_date from the combined index. Once the migration finished, manually modify the created constraint to include the expiry_date again with a conditional check. (Action item: still to be done)

Action item: However some of the conflicting data also points to a data entry error. So it is advisable to extend the frontend to double-check if the user really wants to add another relocation of modify the existing one..

Action item: Validate the currently existing duplicates as some/most of them might be actually unwanted records. To identify them, follow these steps:

  • Create a query 'psm_relocate_duplicates1' with this SQL definition:
SELECT min(psm_relocate.id), psm_relocate.supply_item_id, psm_relocate.expiry_date, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv, Count(*) AS Expr1

FROM psm_relocate

GROUP BY psm_relocate.supply_item_id, psm_relocate.expiry_date, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv

HAVING (((Count(*))>1))
  • Create query 'psm_relocate_duplicates_2':
SELECT psm_relocate.ID, psm_relocate.supply_item_ID, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv, psm_relocate.expiry_date, psm_relocate.phone_authrecv, psm_relocate.move_units, psm_relocate.move_date, psm_relocate.move_rsn, psm_relocate.receive_packs, psm_relocate.receive_units, psm_relocate.receive_date, psm_relocate.User, psm_relocate.TimeStamp, psm_relocate.UpdateUser, psm_relocate.UpdateTimeStamp

FROM psm_relocate INNER JOIN psm_relocate_duplicates1 ON (psm_relocate.move_date = psm_relocate_duplicates1.move_date) AND (psm_relocate.hdepartment_id_recv = psm_relocate_duplicates1.hdepartment_id_recv) AND (psm_relocate.hdepartment_id_send = psm_relocate_duplicates1.hdepartment_id_send) AND (psm_relocate.supply_item_ID = psm_relocate_duplicates1.supply_item_id)

ORDER BY psm_relocate.supply_item_ID, psm_relocate.move_date, psm_relocate.hdepartment_id_send, psm_relocate.hdepartment_id_recv, psm_relocate.expiry_date;

Validation Rule violations

art_clinic_obs: end_time < start_time

46 entries

SELECT art_clinic_obs.ID, art_clinic_obs.start_time, art_clinic_obs.end_time
FROM art_clinic_obs where start_time > end_time;

art_cohort_reg_target: implement_date around now()

Validation rule for implement_date: Between Now()-182 And Now()+182) using current date as validation. Leading to more and more validation violations over time.

2000+ violations, Assumption: Ok for now.

SELECT art_coh_reg_target.implement_date, art_coh_reg_target.*
FROM art_coh_reg_target
WHERE ((Not ((art_coh_reg_target.implement_date) Between Now()-182 And Now()+182)));

htc_person: birthdate based on now()

12 violations, Assumption: ok

Between DateAdd("yyyy",-15,Now()) And DateAdd("yyyy",-70,Now())

SELECT htc_person.birth_date, htc_person.*
FROM htc_person
WHERE ((Not ((htc_person.birth_date) Between DateAdd("yyyy",-15,Now()) And DateAdd("yyyy",-70,Now()))));

psm_dist_round: dist_date based on now()

33 violations, Assumption: ok

Between DateAdd("m",-1,Now()) And DateAdd("m",4,Now())

SELECT psm_dist_round.dist_date, psm_dist_round.*
FROM psm_dist_round
WHERE ((Not ((psm_dist_round.dist_date) Between DateAdd("m",-1,Now()) And DateAdd("m",4,Now()))));

Unusual data

  • psm_dist_item: Some very low numbers with min of -53992676 for ZCH
  • psm_dist_item: Some very high numbers with max of 54001108 for Pirimiti Health Centre

Action Item: to be followed up with PSM (not just to 50.000.000, but also the ones in the range of million).