Tech Notes:

SQL Server Scripts:

OPSROOT

CREATE TABLE [dbo].[ZIPCODES]([ZIPCODE] [nvarchar](10) NOT NULL,[CITY] [nvarchar](100) NULL,[STATE] [nvarchar](10) NULL,[COUNTY] [nvarchar](100) NULL,[TIMEZONE] [nvarchar](100) NULL,) ON [PRIMARY]

The Zipcodes table is then populated with the US Zip codes.

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2022-03-13 02:00:00' WHERE YEARID=2022 AND START_DAYLIGHT='2022-03-06 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2023-03-12 02:00:00' WHERE YEARID=2023 AND START_DAYLIGHT='2023-03-05 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2024-03-10 02:00:00' WHERE YEARID=2024 AND START_DAYLIGHT='2024-03-03 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2025-03-09 02:00:00' WHERE YEARID=2025 AND START_DAYLIGHT='2025-03-02 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2026-03-08 02:00:00' WHERE YEARID=2026 AND START_DAYLIGHT='2026-03-01 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2027-03-14 02:00:00' WHERE YEARID=2027 AND START_DAYLIGHT='2027-03-07 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2028-03-12 02:00:00' WHERE YEARID=2028 AND START_DAYLIGHT='2028-03-05 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2029-03-11 02:00:00' WHERE YEARID=2029 AND START_DAYLIGHT='2029-03-04 02:00:00'

FACILITY DATABASES

ALTER TABLE CHART ADD YAXISTYPE [numeric](18, 0) NOT NULL DEFAULT (0)
ALTER TABLE CHART ADD LINESON2NDY [numeric](18, 0) NOT NULL DEFAULT (0)
ALTER TABLE CHART ADD YAXISLABEL NVARCHAR(100) NULL DEFAULT (NULL)
ALTER TABLE CHART ADD RYAXISLABEL NVARCHAR(100) NULL DEFAULT (NULL)

CREATE TABLE [dbo].[NOTIFICATIONLIST]([ID] [int] IDENTITY(1,1) NOT NULL,[AUDITUSER] [varchar](50) NULL,[AUDITTIMESTAMP] [datetime] NULL DEFAULT (getdate()),[NAME] [nvarchar](100) NULL,[LIST] NVARCHAR (max) NULL,[NOTES] [nvarchar](500) NULL) ON [PRIMARY]

CREATE TABLE [dbo].[LC_TESTQC]([AUDITUSER] [varchar](50) NULL,[AUDITTIMESTAMP] [datetime] NULL DEFAULT (getdate()),[TESTID] [numeric](18, 0) NOT NULL DEFAULT ((0)),[VARID] [numeric](18, 0) NULL DEFAULT ((0)),[NAME] [nvarchar](100) NULL,[QCTYPE] [nvarchar](100) NULL,[SETVAL] [float] NULL,[NOTES] [nvarchar](500) NULL) ON [PRIMARY]

ALTER TABLE VARDESC ADD TextVarFormat NVARCHAR(30) NULL DEFAULT (NULL)
UPDATE VARDESC SET TextVarFormat='TEXT ENTRY' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NULL OR LEN(CAST(LIST AS nvarchar(max))) = 0
UPDATE VARDESC SET TextVarFormat='DROP DOWN LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NOT NULL AND LEN(CAST(LIST AS nvarchar(max))) > 0
UPDATE VARDESC SET TextVarFormat='DROP DOWN LIMIT TO LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = -1 AND (LIST IS NOT NULL AND LEN(CAST(LIST AS nvarchar(max))) > 0

ALTER TABLE VARDESC ADD NOTIFICATIONLISTID [numeric](18, 0) NULL DEFAULT (NULL)

ALTER TABLE LC_SAMPLEDEF ADD SCHDSTARTDATE NVARCHAR(10) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ADD SCHDENDDATE NVARCHAR(10) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ALTER COLUMN NAME NVARCHAR(200)
ALTER TABLE LC_SAMPLEDEF ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)

ALTER TABLE LOCATION ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)

ALTER TABLE LC_SAMPLEDEF ALTER COLUMN NAME NVARCHAR(200)
ALTER TABLE LC_SAMPLEDEF ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)

ALTER TABLE LC_SAMPLE ADD SAMPLENAME NVARCHAR(200) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD SAMPLEDBYNAME NVARCHAR(120) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD RCVDBYNAME NVARCHAR(120) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD SAMPLELOCATION NVARCHAR(250) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)

UPDATE LC_SAMPLE SET RCVDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.RECEIVEDBY=LC_PEOPLE.ID  
UPDATE LC_SAMPLE SET SAMPLEDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.SAMPLEDBY=LC_PEOPLE.ID
UPDATE LC_SAMPLE SET SAMPLELOCATION = LOCATION.PATH FROM LC_SAMPLE INNER JOIN LOCATION ON LC_SAMPLE.LOCATIONID=LOCATION.LOCID

ORACLE:

OPSROOT

CREATE TABLE "ZIPCODES" ("ZIPCODE" VARCHAR2(10) NOT NULL, "CITY" VARCHAR2(100) NULL, "STATE" NVARCHAR2 (10) NULL,"COUNTY" NVARCHAR2 (100) NULL,"TIMEZONE" NVARCHAR2 (100) NULL)

The Zipcodes table is then populated with the US Zip codes.

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2022-03-13 02:00:00' WHERE YEARID=2022 AND START_DAYLIGHT='2022-03-06 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2023-03-12 02:00:00' WHERE YEARID=2023 AND START_DAYLIGHT='2023-03-05 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2024-03-10 02:00:00' WHERE YEARID=2024 AND START_DAYLIGHT='2024-03-03 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2025-03-09 02:00:00' WHERE YEARID=2025 AND START_DAYLIGHT='2025-03-02 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2026-03-08 02:00:00' WHERE YEARID=2026 AND START_DAYLIGHT='2026-03-01 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2027-03-14 02:00:00' WHERE YEARID=2027 AND START_DAYLIGHT='2027-03-07 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2028-03-12 02:00:00' WHERE YEARID=2028 AND START_DAYLIGHT='2028-03-05 02:00:00'

UPDATE OPSTIMEZONE_BIAS SET START_DAYLIGHT='2029-03-11 02:00:00' WHERE YEARID=2029 AND START_DAYLIGHT='2029-03-04 02:00:00'

 

FACILITY DATABASES

ALTER TABLE CHART ADD YAXISTYPE NUMBER DEFAULT 0 NOT NULL
ALTER TABLE CHART ADD LINESON2NDY NUMBER DEFAULT 0 NOT NULL
ALTER TABLE CHART ADD YAXISLABEL NVARCHAR2(100) NULL
ALTER TABLE CHART ADD RYAXISLABEL NVARCHAR2(100) NULL

CREATE TABLE "NOTIFICATIONLIST"("ID" NUMBER NOT NULL, "AUDITUSER" VARCHAR2(50) NULL, "AUDITTIMESTAMP" DATE DEFAULT NULL, "NAME" nvarchar2 (100) NULL,"LIST" nvarchar2 (1000) NULL,"NOTES" nvarchar2 (500) NULL)
CREATE SEQUENCE "A_LC_NOTIFICATIONLIST" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER
CREATE TRIGGER "T_LC_NOTIFICATIONLIST" BEFORE INSERT ON "LC_NOTIFICATIONLIST" FOR EACH ROW BEGIN SELECT "A_LC_NOTIFICATIONLIST".NEXTVAL INTO :NEW."ID" FROM DUAL; END T_LC_NOTIFICATIONLIST;

CREATE TABLE "LC_TESTQC"("AUDITUSER" VARCHAR2(50) NULL, "AUDITTIMESTAMP" DATE DEFAULT NULL, "TESTID" NUMBER NOT NULL, "VARID" NUMBER NOT NULL, "NAME" nvarchar2 (100) NULL,"QCTYPE" nvarchar2 (100) NULL,"SETVAL" NUMBER NULL, "NOTES" nvarchar2 (500) NULL)
CREATE SEQUENCE "A_LC_TESTQC" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER

CREATE TRIGGER "T_LC_TESTQC" BEFORE INSERT ON "LC_TESTQC" FOR EACH ROW BEGIN SELECT "A_LC_TESTQC".NEXTVAL INTO :NEW."ID" FROM DUAL; END T_LC_TESTQC;

ALTER TABLE VARDESC ADD TextVarFormat NVARCHAR2(30) NULL
UPDATE VARDESC SET TextVarFormat='TEXT ENTRY' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NULL OR LEN(CAST(LIST AS nvarchar(max))) = 0
UPDATE VARDESC SET TextVarFormat='DROP DOWN LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NOT NULL AND LEN(CAST(LIST AS nvarchar(max))) > 0
UPDATE VARDESC SET TextVarFormat='DROP DOWN LIMIT TO LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = -1 AND (LIST IS NOT NULL AND LEN(CAST(LIST AS nvarchar(max))) > 0

ALTER TABLE VARDESC ADD NOTIFICATIONLISTID NUMBER NULL

ALTER TABLE LC_SAMPLEDEF ADD SCHDSTARTDATE NVARCHAR2(10) NULL
ALTER TABLE LC_SAMPLEDEF ADD SCHDENDDATE NVARCHAR2(10) NULL
ALTER TABLE LC_SAMPLEDEF ADD ADHOC NUMBER DEFAULT 0 NOT NULL
ALTER TABLE LC_SAMPLEDEF MODIFY(NAME NVARCHAR2(200)

ALTER TABLE LOCATION ADD ADHOC NUMBER DEFAULT 0 NOT NULL

ALTER TABLE LC_SAMPLEDEF ADD ADHOC NUMBER DEFAULT 0 NOT NULL
ALTER TABLE LC_SAMPLEDEF MODIFY(NAME NVARCHAR2(200))

ALTER TABLE LC_SAMPLE ADD SAMPLENAME NVARCHAR2(200) NULL
ALTER TABLE LC_SAMPLE ADD SAMPLEDBYNAME NVARCHAR2(120) NULL
ALTER TABLE LC_SAMPLE ADD RCVDBYNAME NVARCHAR2(120) NULL
ALTER TABLE LC_SAMPLE ADD SAMPLELOCATION NVARCHAR2(250) NULL
ALTER TABLE LC_SAMPLE ADD ADHOC NUMBER DEFAULT 0 NOT NULL
  
UPDATE LC_SAMPLE SET RCVDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.RECEIVEDBY=LC_PEOPLE.ID  
UPDATE LC_SAMPLE SET SAMPLEDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.SAMPLEDBY=LC_PEOPLE.ID
UPDATE LC_SAMPLE SET SAMPLELOCATION = LOCATION.PATH FROM LC_SAMPLE INNER JOIN LOCATION ON LC_SAMPLE.LOCATIONID=LOCATION.LOCID