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 Stop Codes
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoCleanAssociatedStopCodes]
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._dimAssociatedStopCode';
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableAssociatedStopCode;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableAssociatedStopCode') IS NOT NULL
BEGIN
DROP TABLE #batchTableAssociatedStopCode
END
--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableAssociatedStopCode
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', @TABLE_NAME);
--LTRIM/RTRIM any string-based fields
UPDATE zraw._dimAssociatedStopCode SET
OpCode = NULLIF(LTRIM(RTRIM(OpCode)), '')
, AssociatedStopCodeIEN = NULLIF(LTRIM(RTRIM(AssociatedStopCodeIEN)), '')
, RequestServiceIEN = NULLIF(LTRIM(RTRIM(RequestServiceIEN)), '')
, StopCodeIEN = NULLIF(LTRIM(RTRIM(StopCodeIEN)), '')
WHERE EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE zraw._dimAssociatedStopCode.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--create any needed indexes to speed up cleaning
--identify IsCommunityCareRelated, using the IsCommunityCareRelated bit from RequestServices
--AND where StopCode = 669
--Set all IsCommunityCareRelated to false initially
UPDATE zraw._dimAssociatedStopCode SET
IsCommunityCareRelated = 0
WHERE EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE zraw._dimAssociatedStopCode.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--identify IsCommunityCareRelated, using the IsCommunityCareRelated bit from RequestServices
UPDATE zraw._dimAssociatedStopCode SET
IsCommunityCareRelated = 1
WHERE EXISTS (SELECT 1 FROM RequestServices
WHERE RequestServices.CDWRequestServiceSysNumber = zraw._dimAssociatedStopCode.RequestServiceSID
AND IsCommunityCareRelated = 1)
AND EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE zraw._dimAssociatedStopCode.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--AND where StopCode = 669
UPDATE zraw._dimAssociatedStopCode SET
IsCommunityCareRelated = 1
WHERE StopCode = 669
AND EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE zraw._dimAssociatedStopCode.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--Set RequestServiceID; will have to use RequestServiceIEN and CDWRequestServiceSysNumber
--Request service station will be different due to station6a slicing
UPDATE zraw._dimAssociatedStopCode SET
RequestServiceID = RequestServices.RequestServiceID
FROM RequestServices
INNER JOIN zraw._dimAssociatedStopCode ON RequestServices.RequestServiceIEN = zraw._dimAssociatedStopCode.RequestServiceIEN
AND RequestServices.CDWRequestServiceSysNumber = zraw._dimAssociatedStopCode.RequestServiceSID
WHERE EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE zraw._dimAssociatedStopCode.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--Set StopCodeID
--TODO: This may have to be adjusted during testing
UPDATE zraw._dimAssociatedStopCode SET
StopCodeID = StopCodes.StopCodeID
FROM StopCodes
INNER JOIN zraw._dimAssociatedStopCode ON StopCodes.StopCodeIEN = zraw._dimAssociatedStopCode.StopCodeIEN
AND StopCodes.CDWStopCodeSysNumber = zraw._dimAssociatedStopCode.StopCodeSID
WHERE EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE zraw._dimAssociatedStopCode.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--set RecordStatusID based on OpCode
UPDATE zraw._dimAssociatedStopCode SET
RecordStatusID = RecordStatuses.RecordStatusID
FROM RecordStatuses
INNER JOIN zraw._dimAssociatedStopCode ON RecordStatuses.RecordStatusCode = zraw._dimAssociatedStopCode.OpCode
WHERE EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE zraw._dimAssociatedStopCode.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--drop any raw index objects
--drop the #batchTableAssociatedStopCode if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableAssociatedStopCode;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableAssociatedStopCode') IS NOT NULL
BEGIN
DROP TABLE #batchTableAssociatedStopCode
END
END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableAssociatedStopCode
WHERE BatchLogs.BatchLogID = #batchTableAssociatedStopCode.BatchLogID);
--drop any raw index objects
--drop the #batchTableAssociatedStopCode if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableAssociatedStopCode;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableAssociatedStopCode') IS NOT NULL
BEGIN
DROP TABLE #batchTableAssociatedStopCode
END
PRINT 'There was an error cleaning raw Associated Stop Codes data' + CHAR(13);
THROW;
END CATCH
END