Centralized data.mdb Change Log

From CCMDB Wiki
Jump to navigationJump to search

This is the change log for Centralized_data.mdb. See Centralized data.mdb Requested Changes for its requested changes.


  • Increase the field size of L_CCI_Component.CCI_2_Code from 7 to 10

PTorres 17:41, 2019 September 26 (CDT)






  • changed Patient_ID to D_ID
  • added relational integrity constraints


copied out to master Ttenbergen 17:29, 2018 January 4 (CST)

  • added L_CCI table and L_ICD10 table
    • imported from CCMDB_data.mdb
    • changed Patient_ID to D_ID
    • added relational integrity constraints


Rolled out Haubel 16:30, 2017 January 30 (CST)

  • Field size for Item field from L_TmpV2 table changed from 40 to 255.


Rolled out Ttenbergen 19:01, 2017 January 17 (CST)

  • removed BirthMY field Ttenbergen 17:27, 2017 January 17 (CST)
  • imported pseudo-Visit Admit DtTm field data provided by Julie Ttenbergen 18:58, 2017 January 17 (CST)

2016-Nov-24 (Rolled back)

Rolled back Haubel 17:07, 2016 November 25 (CST) Fields "R_Location", "R_AdmitFrom", "R_DischargedTo", "R_HospitalPrevious", "R_SurviveExpire", "R_AdmDate", "R_AdmTime", "R_TRDate", "R_TRTime", "R_DisDate", "R_DisTime", "Var1", "Var2", "Var3", "Var4", "Var5", "Var6" removed from L_Log table.


rolled Haubel 16:08, 2016 November 25 (CST)

  • Fields "R_Location", "R_AdmitFrom", "R_DischargedTo", "R_HospitalPrevious", "R_SurviveExpire", "R_AdmDate", "R_AdmTime", "R_TRDate", "R_TRTime", "R_DisDate", "R_DisTime", "Var1", "Var2", "Var3", "Var4", "Var5", "Var6" put back at the end of the L_Log table.


rolled Ttenbergen 18:21, 2016 November 24 (CST)

  • updated definition for table L_Problem to have dropdown for queryname based on same query as the dropdown for the CFE patient viewer.
  • ran Public Sub old_field_cleanup_20161121() to delete old fields "R_Location", "R_AdmitFrom", "R_DischargedTo", "R_HospitalPrevious", "R_SurviveExpire", "R_AdmDate", "R_AdmTime", "R_TRDate", "R_TRTime", "R_DisDate", "R_DisTime", "Var1", "Var2", "Var3", "Var4", "Var5", "Var6"
  • updated L_Log fields to decimal data type with appropriate # of decimal digits Ttenbergen 18:04, 2016 November 24 (CST)
    • pH - 1 decimal
    • Temp - 1 decimal
    • K - 1 decimal
    • WBC - 1 decimal


rolled out Ttenbergen 17:30, 2016 October 3 (CDT)

  • fixing mistake from bulk change of STB_B5 data:
    1. Downloaded 2016-1-5_15.31.28_PHI.mdb and connected the L_Phi table.
    2. Made query 2016-10-03_del_STB_B5old to delete all current L_PHI STB_B5old, and *ran # Made query 2016-10-03-2_add_STB_B5old_from_old_PHI to replace the STB_B5old records with their correct counterparts that were STB_B5, fixing the D_ID; and ran it
  • updated Mordon/Winkler/Boundary Trails entries for consistency
    1. ran sub Sub Morden_Winkler_Boundarytrails()


rolled out Ttenbergen 11:06, 2016 September 14 (CDT)


Rolled Ttenbergen 17:39, 2016 August 31 (CDT)

  • ran a number of queries to fix data in the new dispo field as per Julie's feedback emails over the last 2 months. Ttenbergen 17:38, 2016 August 31 (CDT)


rolled Ttenbergen 15:56, 2016 August 11 (CDT)

  • ran query 2016-08-11_D_ID_updater to update D_IDs to correspond with service_locations; had to do in steps because doing all at same time would have caused duplicates and therefore index problems. Ttenbergen 15:56, 2016 August 11 (CDT)
  • ran query 2016-08-11_1_Gra_W3S3 service location to update Ttenbergen 15:56, 2016 August 11 (CDT)
  • re-ran query 2016-08-11_D_ID_updater to update the GRA_W3/S3 change Ttenbergen 15:56, 2016 August 11 (CDT)

2016-Jul-04 (2016-07-08 automated update)

ran Conversion of old registry fields to new dispo fields


done on server Ttenbergen


done on server Ttenbergen

  • added off_ward field to table L_Log Ttenbergen 16:43, 2016 June 22 (CDT)


done on server Ttenbergen 17:07, 2016 May 5 (CDT) Added TR_info_status field Ttenbergen 17:07, 2016 May 5 (CDT)


done on server Ttenbergen 19:27, 2016 April 25 (CDT)

  • added field "Visit_Admit_DtTm" to table L_Log Ttenbergen 19:27, 2016 April 25 (CDT)


copied to server Ttenbergen 16:59, 2016 April 14 (CDT)


rolled out Ttenbergen 11:46, 2016 March 3 (CST)

  • ran following to fix blank ids in L_TmpV2:
UPDATE L_TmpV2 SET L_TmpV2.D_Tmp_ID = [d_id] & "_ccusrvman" & Minute([obstime]) & Second([obstime])
WHERE (((L_TmpV2.D_Tmp_ID) Is Null) AND ((L_TmpV2.Project)="CCU_Service"));
  • in table L_TmpV2, made D_Tmp_ID the primary key Ttenbergen 10:51, 2016 March 3 (CST)


rolled Ttenbergen 17:33, 2015 November 18 (CST)

  • added table L Labs Flowsheet DSM table Ttenbergen 17:21, 2015 November 18 (CST)


done in place


see also HSC Labs RE-import 2015-07

  • reverted the HSC lab imports from January 2015:
DELETE L_Labs_Flowsheet.D_Labs_Flowsheet_ID
FROM L_Labs_Flowsheet
WHERE (((InStr([D_Labs_Flowsheet_ID],"_i2014-12-30")>0)=True));
  • re-imported HSC labs with different parameters: import for all HSC ICU records who don't already have an entry for the labs we used to import:
INSERT INTO L_Labs_Flowsheet ( d_labs_flowsheet_id, D_ID, CBC, PT_PTT, Creatinine, MG, SGPT_ALT, Troponin, Sputum_CS, Blood_CS, Urine_CS, ABG )
SELECT Eds_Lab_formatter.d_labs_flowsheet_id, L_Log.D_ID, Eds_Lab_formatter.CBC, Eds_Lab_formatter.PT_PTT, Eds_Lab_formatter.Creatinine, Eds_Lab_formatter.MG, Eds_Lab_formatter.SGPT_ALT, Eds_Lab_formatter.Troponin, Eds_Lab_formatter.Sputum_CS, Eds_Lab_formatter.Blood_CS, Eds_Lab_formatter.Urine_CS, Eds_Lab_formatter.ABG
FROM (HSC_pts_without_importeds INNER JOIN L_Log ON HSC_pts_without_importeds.D_ID = L_Log.D_ID) INNER JOIN Eds_Lab_formatter ON L_Log.D_ID = Eds_Lab_formatter.D_ID;


Cleaned up hospital codes that started with 0 to make consistent, ran

UPDATE L_Log INNER JOIN s_HospitalCodes_updt ON L_Log.R_HospitalPrevious = s_HospitalCodes_updt.Code  
SET L_Log.[R_HospitalPrevious] = [CodeNr]
WHERE (((s_HospitalCodes_updt.Code)<>CStr([CodeNr])));


UPDATE L_Log SET L_Log.R_HospitalPrevious = "" WHERE (((L_Log.R_HospitalPrevious)="0"));


imported into master and updated version Ttenbergen 17:22, 2015 June 24 (CDT)

  • added table L Problem Ttenbergen 13:49, 2015 June 24 (CDT)


Query: UPDATE L_TmpV2 SET L_TmpV2.date_var = DateValue([date_var]) WHERE (((Hour([date_var]))<>0));

  • Query run today in CFE. 1265 profiled fixed today. This query had to do with fix dat_var in TMPV2. Something about removing TIMES from this field.PTorres 15:56, 2015 May 26 (CDT)


rolled Ttenbergen 17:10, 2015 April 1 (CDT)

  • updated L_Person table
    • removed PHIN (long) - we can't be storing the PHIN in centralized, and it would result in update nightmare Ttenbergen 13:26, 2015 April 1 (CDT)


  • updated table L_Person table
    • added PHIN (long)
    • added field last_update (date)
    • updated indices
    • made Person_ID random from incremental
  • updated table L_Log
    • added field Person_ID (long)
    • added field Hospitalization_ID (long)
    • updated indices
  • updated table L_Hospitalization
    • update Hospitalization_ID to random from incremental
    • updated indices
  • ran encounter_processing to populate L_Person table
  • imported remaining HSC Labs from the old lab export process


running queries...

  • finishing move from Var2 -> Tmp BRR/XBR
    • ran query 1_Var2_BRR_1_to_Tmp to generate tmp entries; deleted query, but should be in pre-today backup
    • ran query 1_Var2_BRR_2_cleanout_Var2 to clear out Var2 in L_Log; deleted query, but should be in pre-today backup


changes made right on server Ttenbergen 18:39, 2014 December 1 (CST)


changes made right on server Ttenbergen 17:10, 2014 October 22 (CDT)

  • moved Table Postal Code Master here Ttenbergen 17:10, 2014 October 22 (CDT)
  • confirmed that the "new" tables use random IDs Ttenbergen 17:10, 2014 October 22 (CDT)
  • confirmed that r_sex is female male, had changed it to F M without thinking it through at some point Ttenbergen 17:10, 2014 October 22 (CDT)


rolled out Ttenbergen 18:12, 2014 October 9 (CDT)


after checking in CFE 3 times and using SAS to confirm changes ran 1 use only queries on centralized.mdb on Regional server.Trish Ostryzniuk 21:09, 2014 August 12 (CDT)

  • 1-use only ARF fix (0,-1,yes,no, entered) so only yes, no or blank
  • 1-use only fix DX casing
  • 1-use only APACHE 0 after SAP Admits Jan 1.17 onward
  • 1-use only recordstatus casing: vetted to Vetted (capital)
  • 1-use only update 3000 transfer dates to null

ver 2014-02-24

ver 2014-02-03

  • added index to L_Log for r_sex and r_location to speed up linking
  • changed built-in drop-down for r_sex
  • s_AllDiagnoses table update to include all legacy codes and names where available...

ver 2014-01-10

  • added field L_Log.Data_Use_Option (text 10) to satisfy requirement from PHIA people that we have a place to store the concept of a patient opting out. Ttenbergen 11:12, 2014 January 10 (CST)

ver 2013-11-18

  • imported s-tables from current version of CCMDB.accdb Ttenbergen 21:25, 2013 November 18 (CST)
  • added L_Aux table to store some ed-data which we don't have in CCMDB.accdb Ttenbergen 15:13, 2013 November 18 (CST)
  • fixed date and time formatting in L_LogTtenbergen 21:59, 2013 November 18 (CST)
  • fixed date format and default in L_Labs_Flowsheet Ttenbergen 21:59, 2013 November 18 (CST)
  • fixed date format in L_TmpV2 Ttenbergen 21:59, 2013 November 18 (CST)
  • for L_Dxs and L_Como:
    • changed lookup to show dxs rather than codes Ttenbergen 21:59, 2013 November 18 (CST)
    • updated lookup to show fullCodeName: [MainDiagnosis] & IIf([SubDiagnosis]>"" And [SubDiagnosis]<>[MainDiagnosis],"- " & [SubDiagnosis],"") Ttenbergen 21:59, 2013 November 18 (CST)

Ver 2013-10-03

  • added sentdttm field to accomodate new data from Log#ver_2013-10-03 - Ttenbergen 15:00, 2013 October 3 (CDT)

Ver 2013-09-25

  • added Serial field to accomodate import from Ed's
  • added field SentDtTm; won't get populated until after it is also implemented in ccmdb Ttenbergen 16:07, 2013 September 25 (CDT)

Ver 2013-08-12

Changes need to be timed with CCMDB_data.mdb_Change_Log#2013-08-12 and CCMDB.mdb_Change_Log_2013#ver_2013-08-12

  • change L_Como and others to proper format (d_id(txt 18) and d_*_id(txt 30)) and changed indices and keys
    • l_como
    • L_Labs_Flowsheet
    • L_Pharm_Flowsheet
    • L_TmpV2
      • added D_Tmp_ID;
  • added relationships