Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =============================================
-- Description: Cleans the Consults
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoCleanConsults]
AS
DECLARE @TABLE_NAME varchar(100); --make constant value for use thoughout
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--Set @TABLE_NAME value
SET @TABLE_NAME = 'zraw._conConsult';
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsults;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsults') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsults;
END
--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableConsults
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', @TABLE_NAME);
--LTRIM/RTRIM any string-based fields and Null out any empty strings
UPDATE zraw._conConsult SET
ConsultIEN = NULLIF(LTRIM(RTRIM(ConsultIEN)), ''),
RequestType = NULLIF(LTRIM(RTRIM(RequestType)), ''),
ProvisionalDiagnosisCode = NULLIF(LTRIM(RTRIM(ProvisionalDiagnosisCode)), ''),
ProvisionalDiagnosis = NULLIF(LTRIM(RTRIM(ProvisionalDiagnosis)), ''),
RemoteService = NULLIF(LTRIM(RTRIM(RemoteService)), ''),
RemoteRole = NULLIF(LTRIM(RTRIM(RemoteRole)), ''),
PlaceOfConsultation = NULLIF(LTRIM(RTRIM(PlaceOfConsultation)), ''),
InpatOutpat = NULLIF(LTRIM(RTRIM(InpatOutpat)), ''),
SignificantFindings = NULLIF(LTRIM(RTRIM(SignificantFindings)), ''),
OpCode = NULLIF(LTRIM(RTRIM(OpCode)), '')
/**
--RequestType VARCHAR (50) NULL, --CDW Value; This field will indicate whether the order is a consult or procedurerequest order. C = Consult P= Procedure Request
--ProvisionalDiagnosisCode varchar (10) NULL, --CDWValue; indicates the DX upon entry of the consult
--ProvisionalDiagnosis VARCHAR (255) NULL, --CDWValue; the text definition of the DX code entered upon consult creatation
??ProvisionalDiagnosisMergeCode VARCHAR (255) NULL, --Mine and merge CDWProvisionalDx and CDWProvisionalDx to have a full saturated list
--RemoteService VARCHAR (100) NULL,
--RemoteRole VARCHAR (50) NULL,
--PlaceOfConsultation VARCHAR (50) NULL, --where the provider wrote up the consult ie, Bed side, ER, on call.
--InpatOutpat varchar(50) NULL, --results of a query do not match definition provided many unknowns...
--SignificantFindings VARCHAR (5) NULL, --inconsistant values and no magic decoder ring
**/
WHERE EXISTS (SELECT 1 FROM #batchTableConsults
WHERE zraw._conConsult.BatchLogID = #batchTableConsults.BatchLogID);
--MOVE
--set RecordStatusID based on OpCode
UPDATE zraw._conConsult SET
RecordStatusID = RecordStatuses.RecordStatusID
FROM RecordStatuses
INNER JOIN zraw._conConsult ON RecordStatuses.RecordStatusCode = zraw._conConsult.OpCode
WHERE EXISTS (SELECT 1 FROM #batchTableConsults
WHERE zraw._conConsult.BatchLogID = #batchTableConsults.BatchLogID);
--create any needed indexes to speed up cleaning
--only indexes applied are to the raw tables
--no more than 5 indexes in the cleaner
CREATE NONCLUSTERED INDEX IX_conConsult_Sta3n ON zraw._conConsult
(
Sta3n
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex;
CREATE NONCLUSTERED INDEX IX_conConsult_ConsultIEN ON zraw._conConsult
(
ConsultIEN
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex;
CREATE NONCLUSTERED INDEX IX_conConsult_RequestType ON zraw._conConsult
(
RequestType
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex;
--drop any raw index objects
--DROP INDEX IX_conConsult_ServiceName
-- ON zraw._conConsult;
--drop the #batchTableConsults if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsults;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsults') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsults;
END
END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableConsults
WHERE BatchLogs.BatchLogID = #batchTableConsults.BatchLogID);
--drop any raw index objects
DROP INDEX IX_conConsult_Sta3n
ON zraw._conConsult;
DROP INDEX IX_conConsult_ConsultIEN
ON zraw._conConsult;
DROP INDEX IX_conConsult_RequestType
ON zraw._conConsult;
--drop the #batchTableConsults if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsults;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsults') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsults;
END
PRINT 'There was an error cleaning raw Consult Factor data' + CHAR(13);
THROW;
END CATCH
END