v2024.08.01

Summary

These are the scripts that run the DWH refresh on August 2024

What's Changed

• Feature/Adding-Date-Confirmed-HIV-to-Dim-Patient by @nobert-mumo in #443
• Feature/Builidng-reporting-table-for-case-survillence-tiles-for-the-overview-page by @nobert-mumo in #446
• Hiv cs tx continuity by @DennisGibz in #445
• Updated nupi to null where nupi is coming as an empty string by @DennisGibz in #452
• Modified te factART to include factARTHistory incrementally by @DennisGibz in #444
• Added and (LatestExits.ExitReason is null or LatestExits.ExitReason =… by @DennisGibz in #441
• Feat ushauri etl by @NewtonMutugi in #456
• Added new variables on both ODS.dbo.ct_Patientvisit([AppointmentRemin… by @DennisGibz in https://github.com//pull/458
• Included PersonAPatientPkHash and PersonBPatientPkHash Hashing on the… by @DennisGibz in #440
• Added Load Ushauri_HW_Exposure script by @NewtonMutugi in #459
• Added missing update ODS DQC scripts by @NewtonMutugi in #457
• Refactor the column for LastCD4 dates to pick from the latest CD4 CTE by @Marymary-dev in #455
• Feature/Adding-new-reporting-table-for-on-ART--sentinel-event by @nobert-mumo in #449
• Adding changes to ARTOutcomes model by @Marymary-dev in #461
• Updated column to match Fact Column by @cbrianbet in #462
• Feature/Building-Indicator-for-PBFW-as-of-Diagnosis by @nobert-mumo in #463
• Adding Missed opportunities VL gap model by @Marymary-dev in #448
• Adding Linelist for reported cases risks by @Marymary-dev in #466
• Adding Disclosure column by @Marymary-dev in #471
• Feauture hiv advanced disease model by @Marymary-dev in #467
• Feat ct dockets updates by @NewtonMutugi in #468
• Feature/IndexElicitation-&-testing-report-for-case-survillence by @nobert-mumo in #470
• Feature/building a reporting table for substance abuse & sti risk factors by @nobert-mumo in #474
• Fix update mhealth table names by @NewtonMutugi in #469
• Aligned the hashing scripts and added the missing updates. Also remov… by @DennisGibz in #472
• Feat/nishauri profile etl by @NewtonMutugi in #473
• Fix/-Removing-dups-from-the--AHD-model-linelist-for-CS by @nobert-mumo in #480
• aggregate otz outcomes updated to cover active clients by @cbrianbet in #483
• Adding a column for EveronART to DimPatient by @Marymary-dev in #477
• Refactoring prophylaxis data by @Marymary-dev in #465
• Removed ambiguity on the outcome column under the alias on the where … by @DennisGibz in #485
• Modified HTSclientTracing to eliminate duplicates by @DennisGibz in #484
• corrected column PeripheralNeuropath to PeripheralNeuropathy by @DennisGibz in #464
• Added scripts to flag duplicates on the ODS layer by @DennisGibz in #460
• Align ct patient lab remove dups by @DennisGibz in #488
• Moved all the CS based scripts from Reporting main folder to HIVCaseS… by @DennisGibz in #489
• Added new variables on the mnch_ANCVisits by @DennisGibz in #490
• Added a new intermediate table to calculate PBFW. The script has comm… by @DennisGibz in #481
• Fix/nishauri ods by @NewtonMutugi in #491
• Adding Confirmed Treatment failure by @Marymary-dev in #493
• Adding new patient column by @Marymary-dev in #494

Full Changelog: v2024.05.01...v2024.08.01

v2024.06.01

Summary

These are the scripts that run the DWH refresh on June 2024

What's Changed

• Filtering for honoured and not honoured in the Appointments cascade by @Marymary-dev in #421
• Feature/Adding-switched-to--2nd-regimenline-from-1st-regimenline by @nobert-mumo in #423
• Bugfix/Uasin-Gishu-seems-not-to-have-any-client-onTLD-based-regimen by @nobert-mumo in #424
• Removed the visitID in the uniqueness check under the ct_patientPharm… by @DennisGibz in #425
• Feature/Adding-SiteAbstraction-date-to-concordance-report by @nobert-mumo in #427
• Removed the lineARTHistory from the static folder to the main folder … by @DennisGibz in #428
• Refactor last Encounter model to pick the max Expected return from t… by @Marymary-dev in #426
• Removed LastEncounterDate on the LastEncounterPharmacy cte under Inte… by @DennisGibz in #429
• updated CT_Artpatient StartARTDate to StartARTAtThisFacility where St… by @DennisGibz in #430
• Adding Owner, Kephlevel and infrastructure type columns by @Marymary-dev in #431
• Added a new extract on ODS named Ushauri_PatientReferral on ODS.Delet… by @DennisGibz in #433
• Added an update on the CT_patientStatus to clean the ExitDate and Exi… by @DennisGibz in #432
• 1. Added A case statement to update voiding source on all the C&T ext… by @DennisGibz in #435
• Introduced a try_cast on RiskCategory case statement. Also renamed L… by @DennisGibz in #438
• Introduced Max ID in the inner query just to pick max created unique … by @DennisGibz in #439
• Modified AggregateHTSUptake to only consider records for the reportin… by @DennisGibz in #437
• Added ODS DQC scripts by @NewtonMutugi in #434

New Contributors

• @NewtonMutugi made their first contribution in #434

Full Changelog: v2024.04.01...v2024.05.01

v2024.04.01

Summary

These are the scripts that run the DWH refresh on April 2024

What's Changed

• Feauture add index patient pk by @Marymary-dev in #395
• Changing from Coalesce to OR Statement and removing extra space by @Marymary-dev in #397
• Filtering for the unsuppressed who had a repeat VL by @Marymary-dev in #400
• Adding Mmd, Apointments category and stability assessment by @Marymary-dev in #401
• Adding PrepEnrollmentDate to the Fact and the Linelist by @Marymary-dev in #396
• Creating Fact Modules Uptake by @Marymary-dev in #399
• Adding linelist modules uptake by @Marymary-dev in #402
• Bugfix/Fix-ambigous-column-name-in-prep-cascade by @nobert-mumo in #404
• Feature/Building-RTC-in-the-last-12-months-from-reporting-period by @nobert-mumo in #398
• Fixing traced indicator in Ushauri appointments by @Marymary-dev in #403
• Modified CT_Relationship,Ushauri_appointments,intermmiadiate ViralLoa… by @DennisGibz in #406
• Task/Put-RTT-intermediate-in-a-sub-folder by @nobert-mumo in #407
• Adding pregnant, breastfeeding and ScreenedDepression date columns by @Marymary-dev in #405
• Resolving conflicts by @Marymary-dev in #408
• Returning deleted column isCT by @cbrianbet in #410
• Modified Intermediate LastVisit to cater for /. Cleaning of the BP co… by @DennisGibz in #411
• Adding PrepVariables into Preplinelist by @Marymary-dev in #409
• Renaming linelistPrep to LinelistPrepAssessments by @Marymary-dev in #412
• Feature/Update-logic-for-checking-if-NCDs-are-controlled-or-not-based-on-the-new-data-points-that-were-added-from-DWAPI by @nobert-mumo in #415
• Adding LinelistPrep by @Marymary-dev in #416
• Aliasing Pregnant column in DSD Unstable model by @Marymary-dev in #417
• Clean Regimen Lines by @Marymary-dev in #414
• Introduced Union to well cater for patients coming from ushauri by @DennisGibz in #418
• Creating look up table for regimen line cleaning by @Marymary-dev in #419
• Added Ushauri_Hei script,removed [TracingCalls],[TracingSMS],[Tracing… by @DennisGibz in https://github.com//pull/413
• Bugfix/-Adapting-Fact-ART-to-handle-updated-logic-for-NCD-controlled-and-screened by @nobert-mumo in #420
• Adding IPT variables into linelist FactART by @Marymary-dev in #422

Full Changelog: v2024.03.01...v2024.04.01

v2024.03.01

Summary

These are the scripts that run the DWH refresh on March 2024

What's Changed

• Feature/Adding-additional-data-points-for-historical-appointments by @nobert-mumo in #364
• Feature/Model Dyslipidemia NCD by @nobert-mumo in #380
• Add ReferredForPreventativeServices to linelist hts risk categorization by @nthusi-codes in #332
• Feauture adjust prep refill dates by @Marymary-dev in #384
• Mode some modification on the ODS and NDWH scripts resulting from the… by @DennisGibz in #383
• Added a new extract. CT_Relationships on ODS by @DennisGibz in #386
• Converted the ct_relationship file to a .sql file by @DennisGibz in #387
• Bug fix on table join by @nthusi-codes in #388
• Feauture fix pbfw regimen switches by @Marymary-dev in #381
• modified ODS and NDWH scripts by @DennisGibz in #389
• Feauture refactor dhis aggregates to extract from the fact by @Marymary-dev in #385
• Adding outcome date by @Marymary-dev in #391
• modified mhealth date columns to allow datetime as their data types. … by @DennisGibz in #390
• Fix/Adjusting-logic-for-ushauri-appointments-logic by @nobert-mumo in #392
• Add Pregnant and breast feeding to the Fact and aggregate by @Marymary-dev in #393

Full Changelog: v2024.02.01...v2024.03.01

v2024.02.01

What's Changed

• Replace the schema NDWH with ODS by @Marymary-dev in #350
• Bugfix/Fixing-logic-on-PregAtInitiation-and-PregDuringART-intermediate-tables by @nobert-mumo in #353
• Feature/Adding-DSD--model-to-FactART-History-&-the-aggregate-for-bookings by @nobert-mumo in #356
• Feature/-Add-DSD-Model-to-Historical-TXCurr-table by @nobert-mumo in #355
• Refactoring dim PAtient to include Ushauri clients in non EMR Sites by @Marymary-dev in #354
• Adding FactUshauriSMSReminders by @Marymary-dev in #351
• Changed the table names for HTS and CBS manifests by @DennisGibz in #303
• Revert " Refactoring dim PAtient to include Ushauri clients in non EMR Sites" by @cbrianbet in #358
• Alignment of ODS and Reporting scripts(appointments linelist). Modifi… by @DennisGibz in #357
• Adding Dwapiversion, MFLCode and County into the table by @Marymary-dev in #359
• Adding MFLCode, County and Dwapi versions into the table by @Marymary-dev in #360
• Adding aggregate_Ushauri_sms_reminders by @Marymary-dev in #352
• Renaming from Ushauri SMS Reminders to Ushauri Appointments by @Marymary-dev in #361
• Introduced a mhealth folder with scripts mapped to CT patient PK by @DennisGibz in #362
• Cleaned ODS database by seperating the log tables from the main ODS E… by @DennisGibz in #365
• Add Ushauri patients to the dimension by @Marymary-dev in #366
• Added siteType on DimPatient by @DennisGibz in #367
• Made some adjustments on the recreate scripts by @DennisGibz in #369
• Adding Tracing indicators to Ushauri Appointments by @Marymary-dev in #363
• Added post_load folder and moved the scripts inside by @DennisGibz in #370
• Removing the casting on column by @Marymary-dev in #368
• Correct validity for pbfw and other population by @Marymary-dev in #371
• Modified MNCH,PREP,HTS and mhealth scripts under ODS to deal with dup… by @DennisGibz in #373
• BugFix/Add-PatientID-on-Intermediates-for-Preg by @nobert-mumo in #374
• Modified DimPatient to added the voided on the merge and also removed… by @DennisGibz in #375
• Refactor number with two consecutive VLs by @Marymary-dev in #376
• Added RecordUUID on some HTS ODS tables by @DennisGibz in #377
• Feature/Fix-Valid-VLs & Logic for PBFW by @nobert-mumo in #378
• Modified dimpatient to align the sequence of excution for CTEs by @DennisGibz in #379

Full Changelog: 2024.01.1...v2024.02.01

2024.01.1

Summary

These are the scripts that run the DWH refresh on January 2024

What's Changed

• Removing dependency from Fact NCD by @Marymary-dev in #334
• Adding mental health variables into linelist ART by @Marymary-dev in #335
• Renaming rom Aggregate cohort retention to Aggregate_TxNew by @Marymary-dev in #336
• Changed facility source to ODS from HISImplementation by @cbrianbet in #301
• Fix/Some-house-keeping-of-the-repo by @nobert-mumo in #338
• create service desk util by @cbrianbet in #340
• Alignment between .83 scripts the dev branch by @DennisGibz in #339
• Refactoring to filter next appointment date by @Marymary-dev in #217
• Fix/Remove-LinkedToCare-from-LinelistPNS by @nobert-mumo in #337
• Adding hts eligibility linelist by @cbrianbet in #325
• Adding Entry point into linelist_hts_risk_categorization_and_test_res… by @Marymary-dev in #345
• Seperated the two Intermediate table adjustments from the serviceDesk… by @DennisGibz in #346
• Pushing the correct script for Appointment Status by @Marymary-dev in #347
• Add schema reference to matching output table by @nthusi-codes in #348
• Removing comma before insert into statement by @Marymary-dev in #349

Full Changelog: 202312...2024.01.1

v 2023.12.0-1

What's Changed

• Adding AHD indicators into linelist Fact ART by @Marymary-dev in #282
• Adding TBScreening into Fact Latest Obs by @Marymary-dev in #281
• Moved DimArtOutcome to the static Dimensions folder. Also,removed the… by @DennisGibz in #285
• Changing from inner to left join in ARTOutcome CTE + removing the nu… by @Marymary-dev in #286
• Removed null and yes folder by @DennisGibz in #287
• Removed Where voided =0 on DimPatient script by @DennisGibz in #288
• Included F.code >0 for all the ODS C&T Scripts for eliminate data fro… by @DennisGibz in #291
• Feature/Adding-an-IIT-RiskScore-fact-and-report by @nobert-mumo in #290
• Revert "Included F.code >0 for all the ODS C&T Scripts for eliminate data fro…" by @cbrianbet in #292
• Remove voided check on ODS - central scripts by @cbrianbet in #294
• Added voided check (Voided =0 ) for intermediate tables referencing O… by @DennisGibz in #295
• Moved the CBS_Data folder from CT_Docket to- ODS folder by @DennisGibz in #296
• Change to datediff Dispensedate,Expected return by @Marymary-dev in #289
• Added scripts to update records for voided patients by @cbrianbet in #293
• Moved the ODS_VOIDING folder from Utils sub-folder to ODS Main Folder by @DennisGibz in #298
• Patch/reversing scripts by @cbrianbet in #302
• Bugfix/Update-PrepCT-based-on-MER-guidelines by @nobert-mumo in #299
• Change from Dispensedate to LastVisit date by @Marymary-dev in #304
• Starting from FactART and Left join VL by @Marymary-dev in #297
• Bugfix/DSD-Aggregate-models-do-not-give-same-results-as-what-is-in-Linelist-ART by @nobert-mumo in #300
• Addiing MNCH datasets into FactPbfw by @Marymary-dev in #284
• Updated ValidVLResultCategory Limits by @cbrianbet in #305
• Adding Date Confirmed Positive Key into Fact ART by @Marymary-dev in #310
• Optimizing load_FactDefaulterTracing.sql by @cbrianbet in #311
• Optimized the loading of FactTPT by @DennisGibz in #314
• Added [RecordUUID] by @DennisGibz in #318
• Added mhealth folder with ushauri patients and Appointments by @DennisGibz in #319
• MOdified ODS scripts to include more columns in the update. Also adde… by @DennisGibz in #320
• Ushauri on ods by @DennisGibz in #321
• Feature/an aggregate dataset on the reporting layer for the durable u equals u dashboard by @nobert-mumo in #306
• Feature/ Add Positive & started on prep' to the aggregate prep cascade by @nobert-mumo in #313
• Refactoring the model to accomodate AMRS sites by @Marymary-dev in #322
• Adding Fact HTSPOS Concordance by @Marymary-dev in #316
• Adding Aggregate_Concordance_TxCurr by @Marymary-dev in #308
• Adding Fact TxCurr Concordance by @Marymary-dev in #315
• ODS CT Scripts clean up by @DennisGibz in #324
• Feature/Add-EOM-date-in-Fact-ART by @nobert-mumo in #323
• Feature/Refactor-logic-for-ScreenedForBP-&-ScreenedForDiabetes by @nobert-mumo in #317
• Included voided check in the merge to avoid duplication errors. Also … by @DennisGibz in #326
• Modified CT_IPT by @DennisGibz in #327
• Filtering out HasRegimen clients to subset in the unsuppressed category by @Marymary-dev in #312
• Adding reporting Month by @Marymary-dev in #329
• Adding Fact_manifest by @Marymary-dev in #330
• Fix/Omit-records-with-NULL-adverse-events-from-the-fact by @nobert-mumo in #331
• Feat/add mpi golden id by @nthusi-codes in #307
• Adding Aggregate_concordance_HTSPO by @Marymary-dev in #309
• Removing dependency from Fact NCD by @Marymary-dev in #334
• Adding mental health variables into linelist ART by @Marymary-dev in #335
• Renaming rom Aggregate cohort retention to Aggregate_TxNew by @Marymary-dev in #336
• Changed facility source to ODS from HISImplementation by @cbrianbet in #301
• Fix/Some-house-keeping-of-the-repo by @nobert-mumo in #338
• create service desk util by @cbrianbet in #340
• Alignment between .83 scripts the dev branch by @DennisGibz in #339
• Updating release by @cbrianbet in #341

Full Changelog: v1.0.0...202312-1

v 2023.11.0

This release represents the ETL scripts used to run the refresh on Nov 17, 2023

What's Changed

• Feature/adding dimensional layer scripts by @nobert-mumo in #2
• Feature/re arranging the scripts by @nobert-mumo in #3
• added keys to Dim tables by @cbrianbet in #5
• Feature/working on more dimensions by @nobert-mumo in #6
• Feature/ Adding New dims, yaaay! by @nobert-mumo in #7
• Changes to dim facility by @Marymary-dev in #8
• Addition dim treatment type by @Marymary-dev in #10
• Load intermediate tables by @Marymary-dev in #11
• Changes to remove Schema in intermediate tables by @Marymary-dev in #13
• Adding new intermediate tables for use in creating downstream models by @nobert-mumo in #12
• Load intermediate tables by @Marymary-dev in #15
• changes to PregnancyAtARTInitiation by @Marymary-dev in #16
• Adding new fact tables, intermediate table and other small changes by @nobert-mumo in #17
• Feature/Adding viral load fact table by @nobert-mumo in #21
• Additions to PregnancyAsAt intermediate table by @Marymary-dev in #14
• Load fact tables by @Marymary-dev in #19
• Dev by @Marymary-dev in #23
• Load fact tables by @Marymary-dev in #25
• Dev by @Marymary-dev in #26
• Feat/intermediate tables improvements by @nthusi-codes in #28
• Create load_FACTPatientExits by @Marymary-dev in #24
• Additions to FactTables FactMANifest by @Marymary-dev in #27
• adding new tables for IPT & CD4 by @nobert-mumo in #29
• Feature/Adding art outomes to DimPatient and small tidying up by @nobert-mumo in #30
• Create load_Linelist-FACTART by @Marymary-dev in #33
• Update load_intermediate_LastPatientEncounter by @Marymary-dev in #34
• Update load_FactART.sql by @Marymary-dev in #37
• Aggregate tables for OTZ outcomes and cohort retention by @cbrianbet in #38
• Feat/aggregate otz outcomes tables by @cbrianbet in #42
• Feat/aggregate time to art report tables by @nthusi-codes in #35
• Change reporting folder by @cbrianbet in #43
• Add AgeLastVisit column to FactArt by @nthusi-codes in #44
• Feat/aggregate otz table by @cbrianbet in #46
• Feat/aggregate viral load report tables by @nthusi-codes in #41
• Fix/fact tables merge by @nthusi-codes in #51
• Added covid aggregate table by @cbrianbet in #40
• Feat/aggregate otz table by @cbrianbet in #54
• Feature/adding aggregate TXCur table by @nobert-mumo in #49
• Dev by @Marymary-dev in #55
• Load aggregate tables by @Marymary-dev in #52
• Adding IsTXCurr in the linelist by @Marymary-dev in #50
• Dev by @Marymary-dev in #56
• Feat/aggregate dsd table by @cbrianbet in #47
• Create load_aggregate_otz.sql by @cbrianbet in #39
• Load aggregate tables by @Marymary-dev in #57
• Feat/ovc reports by @nthusi-codes in #48
• Feature/building a Fact Adverse events table by @nobert-mumo in #53
• Load aggregate tables by @Marymary-dev in #59
• Fix/report scripts cleanup by @nthusi-codes in #62
• changes to Intermediate_ARTOutcome by @Marymary-dev in #63
• Changes to ARTOutcome logic by @Marymary-dev in #64
• Load intermediate art outcome by @Marymary-dev in #65
• Changes to LastPatientencounter by @Marymary-dev in #66
• Someone deleted this linelist by @cbrianbet in #67
• Create load_aggregate_nupi.sql by @cbrianbet in #69
• changes to effective discontinuation date by @Marymary-dev in #70
• Changes to include correctly patients with TCAs >365 days by @Marymary-dev in #71
• Fixed column reference errors by @cbrianbet in #68
• Feature/Enhancing-DimPatient-to-accomodate-HTS-clients by @nobert-mumo in #61
• Load intermediate art outcome by @Marymary-dev in #73
• changes to Last encounter by @Marymary-dev in #76
• Last patient encounter by @Marymary-dev in #78
• Added TxCurr Column by @cbrianbet in #80
• Update load_aggregate_dsd.sql by @cbrianbet in #81
• Capping in all CTE for LE by @Marymary-dev in #82
• Add aggregate adverse events report by @nthusi-codes in #60
• Correct table names used by @cbrianbet in #84
• Feature/Modelling-HTS-Client-Tests-Fact-table by @nobert-mumo in #74
• Feature/Modelling-Client-Linkages-fact-table by @nobert-mumo in #75
• Feature/Modelling-Fact-HTS-Partner-Tracings by @nobert-mumo in #79
• Feature/Modelling-Fact-HTS-Test-Kits-table by @nobert-mumo in #85
• Added TXCurr check for Otz tables also added eligible +enrolled aggregate by @cbrianbet in #72
• Feature/Modelling-Fact-HTS-ClientTracing by @nobert-mumo in #77
• Load intermediate art outcome by @Marymary-dev in #86
• Minor changes to explicitly call variables by @Marymary-dev in #87
• Sequencing the cases by @Marymary-dev in #88
• Explicitly used column names to fix arising errors by @cbrianbet in #90
• Feat/aggregate time to art report tables by @nthusi-codes in #91
• Update load_linelist_covid.sql by @cbrianbet in #92
• Update load_linelist_covid.sql by @cbrianbet in #93
• Merge to production by @nthusi-codes in #96
• Optimize_StartRegimens_aggregate by @Marymary-dev in #58
• Added missing columns in art linelist also added AdverseClientsCount to Agg Adverse events by @cbrianbet in #94
• Rename CTPartner,CTAgency columns to ParnerName,AgencyName by @nthusi-codes in #97
• called columns when inserting data by @cbrianbet in #98
• Updates by @DennisGibz in #101
• Feature/Adapting-DimPatient-to-include-Prep-patients by @nobert-mumo in #100
• Update by @DennisGibz in #104
• Feature update by @DennisGibz in #105
• Feature/Adding-an-intermediate-table-for-latest-Prep-visit-per-patient by @nobert-mumo in #102
• Updates to OptimizeStartRegimens, vlOutcomeAndUptake, AggregateTimeToARTGrp and DimPatient by @cbrianbet in #99
• Loading fact HtsPartnerNotification table by @nobert-mumo in #95
• Updated columns in Linelist art by @cbrianbet in https://github.com/palladiumkenya/dwh-...