Centralized data.mdb Change Log

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

2023-01-17

copied to regional server live location

• imported whole database into a new repository because there was corruption.

2022-08-31

Pagasa will put back into circulation as part of usual push/pulls Ttenbergen 11:49, 2022 August 31 (CDT)

• removed L Dxs table and L Como table Ttenbergen 11:49, 2022 August 31 (CDT)

2022-08-15

copied to server Ttenbergen 17:41, 2022 August 15 (CDT)

• reverted to file from 2022-08-10 and re-did the changes since the last change was done on an outdated version of centralized_data
• re-applied the data updates to Postal Code Master table

2022-08-10

copied to server Ttenbergen 14:34, 2022 August 10 (CDT)

• update L_Log table:
  • added ClientGUID field and ClientVisitGUID field
  • removed Room_nr, Pharm Flow Complete, ApLab_Complete, FinalCheck field

2021-11-17

copied to server Ttenbergen 00:17, 2021 November 18 (CST)

• moved L_Problems_D_ID table to this file from CFE so its contents will be preserved when CFE is updated. Ttenbergen 00:04, 2021 November 18 (CST)

2019-09-26

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

PTorres 17:41, 2019 September 26 (CDT)

2018-12-02

• changed the subdatasheet parameter to "None", as this allegedly improves performance

2018-04-24

• removed old table L_CCI (see instead * L_CCI_Component table and L_CCI_Picklist table)
• removed old table L_ICD10_old (see instead L_ICD10 table)
• removed old table L_Labs_Flowsheet_DSM, data is kept in DSM Labs data.accdb instead
• confirmed indexes and relationships for new tables

2018-04-03

Added

• L_CCI_Component table
• L_CCI_Picklist table
• L_ICD10 table - the new version; old one renamed to L_ICD10_Old; data moved over

Then:

• changed Patient_ID to D_ID
• added relational integrity constraints

2018-Jan-04

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

2017-Jan-30

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

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

2017-Jan-17

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.

2016-Nov-25

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.

2016-Nov-24

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

2016-Oct-03

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()

2016-Sep-14

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

• added Accept DtTm missing field Ttenbergen 11:06, 2016 September 14 (CDT)
• changed any instance of "STB Cardiac Sciences" to STB CICU in
  • Pre-admit Inpatient Institution field
  • Previous Location field
  • Service/Location field
  • Dispo field

2016-Aug-31

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)

2016-Aug-11

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

2016-Jul-04

done on server Ttenbergen

• changed L_Log table to show resolved values for new dispo fields when the corresponding s_tables are available:
  • Pre acute living situation
  • Pre-admit Inpatient Institution
  • Previous_Location
  • Previous_Service
  • Service/Location
  • Dispo

2016-Jun-22

done on server Ttenbergen

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

2016-May-05

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

2016-Apr-25

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)

2015-Apr-14

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

• added VBG column to L_Labs_Flowsheet - Ttenbergen 16:09, 2016 April 14 (CDT)
• Added fields to L_Log in prep for starting 2016 Time and Place changes
  • Postal Code field
  • LastOpened_DtTm field
  • Pre acute living situation field
  • Pre-admit Inpatient Institution field
  • Previous Location field
  • Previous Service field
  • Service/Location field
  • Dispo field
  • Accept_DtTm field
  • Arrive_DtTm field
  • Dispo_DtTm field
  • Transfer_Ready_DtTm field

2016-Mar-03

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)

2015-Nov-18

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

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

2015-Aug-17

done in place

• added L_Communication_Notes table

2015-Jul-22

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;

2015-Jul-06

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])));

and

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

2015-Jun-24

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

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

2015-May-26

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)

2015-APR-01

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)

2015-Jan-29

• 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

2015-Jan-19

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

2014-Dec-01

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

• changed indexes for L_Como and L_Dxs to see if this will speed up sending Ttenbergen 18:39, 2014 December 1 (CST)
• kept a diff backup for this since we also did b5->5B change at same time. See details: Site_and_Location_table#Dec_1.14_-_Updated_query_for_CFE_Var_1_.26_2_-_fix_STB_med_B5_to_5B

2014-0ct-22

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

• moved Postal_Code_Master table 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)

2014-Oct-09

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

• Eliminated s_ tables; will be pulled from CCMDB.accdb by CFE as of 18:12, 2014 October 9 (CDT)
• added tables L_PCH table, L_Person table, L_Hospitalization table, S_PCH table, I will probably still need to tweak the structure, just wanted them there for now. Ttenbergen 18:12, 2014 October 9 (CDT)

2014-Aug-12

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

• updated s_AllDiagnoses table entry for HAP to list as "Pneumonia-Hospital Acquired HAP"

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