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.[uspDoCleanRequestServices]
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._dimRequestService';

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

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

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

--LTRIM/RTRIM any string-based fields and Null out any empty strings
UPDATE zraw._dimRequestService SET
ServiceAbbreviation = NULLIF(LTRIM(RTRIM(ServiceAbbreviation)), '')
, ServiceName = NULLIF(LTRIM(RTRIM(ServiceName)), '')
, OpCode = NULLIF(LTRIM(RTRIM(OpCode)), '')
WHERE EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

--set RecordStatusID based on OpCode
UPDATE zraw._dimRequestService SET
RecordStatusID = RecordStatuses.RecordStatusID
FROM RecordStatuses
INNER JOIN zraw._dimRequestService ON RecordStatuses.RecordStatusCode = zraw._dimRequestService.OpCode
WHERE EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

--create any needed indexes to speed up cleaning
CREATE NONCLUSTERED INDEX IX_dimRequestService_ServiceName ON zraw._dimRequestService
(
ServiceName
) 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 --don't need, less than 10,000 rows
)
ON StagingIndex;

--backfill requestServiceCategory
--the underscore is a single-character wildcard for LIKE statement, will find CHOICE-FIRST and CHOICE FIRST'
--if need to expand, replace underscore with % for any number of characters between words CHOICE and FIRST
UPDATE zraw._dimRequestService SET
RequestServiceCategory = 'CHOICE-FIRST'
WHERE ServiceName LIKE '%CHOICE_FIRST%'
AND EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

UPDATE zraw._dimRequestService SET
RequestServiceCategory = 'COMMCARE'
WHERE ServiceName LIKE '%COMMUNITY%CARE-%'
AND EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

UPDATE zraw._dimRequestService SET
RequestServiceCategory = 'NON VA CARE'
WHERE ServiceName LIKE '%NON%VA%CARE%'
AND EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

---to deal with these 3 record categories...check with Jayme to validate.
---1)DISABLED COMMUNITY CARE-SCI AGENCY SN CATHETER CARE
---2)DISABLED COMMUNITY CARE-SCI AGENCY SN WOUND CARE
---3)XXXCOMMUNITY CARE-EGD/COLONOSCOPY PB-657A4

--backfill the IsCommunityCareRelated bit field
--Set all IsCommunityCareRelated to false initially
UPDATE zraw._dimRequestService SET
IsCommunityCareRelated = 0
WHERE EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

--Set all community-care related services to true
UPDATE zraw._dimRequestService SET
IsCommunityCareRelated = 1
WHERE (ServiceName LIKE 'NON VA%'
        OR ServiceName LIKE 'NON-VA%'
OR ServiceName LIKE 'CHOICE%'
OR ServiceName LIKE 'COMMUNITY CARE%'
OR ServiceName LIKE 'VCL%'
OR ServiceName LIKE 'ZZNON VA%'
OR ServiceName LIKE 'ZZNON-VA%'
OR ServiceName LIKE 'ZZCHOICE%'
OR ServiceName LIKE 'ZZCOMMUNITY CARE%'
OR ServiceName LIKE 'ZZVCL%')
--can't do Stopcode check here
--OR StopCode=669
AND EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

--backfill StationID based on Station6A
EXEC zraw.uspDoDynamicSliceStation6A @TABLE_NAME, 'ServiceName';

--fill in the remaining StationIDs
UPDATE zraw._dimRequestService SET
Station6A = Sta3n
WHERE Station6A IS NULL
AND EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

--because there are station6As involved, cannot limit Stations list to only ParentStations (VAMCs)
UPDATE zraw._dimRequestService SET StationID = Stations.StationID
FROM Stations
INNER JOIN zraw._dimRequestService ON zraw._dimRequestService.Station6A = Stations.Station6A
WHERE zraw._dimRequestService.StationID IS NULL
AND EXISTS (SELECT 1 FROM #batchTableRequestServices
WHERE zraw._dimRequestService.BatchLogID = #batchTableRequestServices.BatchLogID);

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

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

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

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

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

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

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

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