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 Request Services
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoCleanConsultFactors]
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._conConsultFactor';

--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END

--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableConsultFactors
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', @TABLE_NAME);

--LTRIM/RTRIM any string-based fields and Null out any empty strings
UPDATE zraw._conConsultFactor SET
ConsultIEN = NULLIF(LTRIM(RTRIM(ConsultIEN)), ''),
ConsultActivityIEN = NULLIF(LTRIM(RTRIM(ConsultActivityIEN)), ''),
ConsultFactorType = NULLIF(LTRIM(RTRIM(ConsultFactorType)), ''),
FactorData = NULLIF(LTRIM(RTRIM(FactorData)), ''),
DateFactorText = NULLIF(LTRIM(RTRIM(DateFactorText)), ''),
CTBItemFlag = NULLIF(LTRIM(RTRIM(CTBItemFlag)), ''),
PatientIEN = NULLIF(LTRIM(RTRIM(PatientIEN)), ''),
EnteredByStaffIEN = NULLIF(LTRIM(RTRIM(EnteredByStaffIEN)), ''),
OpCode = NULLIF(LTRIM(RTRIM(OpCode)), '')



/**


-- [ConsultIEN] VARCHAR (50) NOT NULL,

-- [ConsultActivityIEN] VARCHAR (50) NOT NULL,

--[ConsultFactorType] VARCHAR (50) NULL,
[FactorData] VARCHAR (4000) NULL,
[DateFactorText] VARCHAR (100) NULL,

[CTBItemFlag] CHAR (1) NULL,
--[PatientIEN] VARCHAR (50) NULL,
--[EnteredByStaffIEN] VARCHAR (50) NULL,

--[OpCode] CHAR (1) NULL,

**/

WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);

--MOVE
--set RecordStatusID based on OpCode
UPDATE zraw._conConsultFactor SET
RecordStatusID = RecordStatuses.RecordStatusID
FROM RecordStatuses
INNER JOIN zraw._conConsultFactor ON RecordStatuses.RecordStatusCode = zraw._conConsultFactor.OpCode
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.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_conConsultFactor_Sta3n ON zraw._conConsultFactor
(
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_conConsultFactor_ConsultIEN ON zraw._conConsultFactor
(
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_conConsultFactor_ConsultActivityIEN ON zraw._conConsultFactor
(
ConsultActivityIEN
) 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_conConsultFactor_EnteredByStaffIEN ON zraw._conConsultFactor
(
EnteredByStaffIEN
) 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_conConsultFactors_ServiceName
-- ON zraw._conConsultFactor;

--drop the #batchTableConsultFactors if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END

END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE BatchLogs.BatchLogID = #batchTableConsultFactors.BatchLogID);

--drop any raw index objects
DROP INDEX IX_dimRequestService_ServiceName
ON zraw._conConsultFactor;

--drop the #batchTableConsultFactors if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END

PRINT 'There was an error cleaning raw Consult Factor data' + CHAR(13);
THROW;
END CATCH
END