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: Synchs the One Consult Stations from OneConsult data pull
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- Josh --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.uspDoSynchRawFKsConsults
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--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', 'zraw._conConsult');
--update _conOneConsult's new status type id
UPDATE [zraw].[_conConsult] SET
StatusTypeID = ST.StatusTypeID
FROM dbo.StatusTypes ST
INNER JOIN zraw._conConsult ON zraw._conConsult.CPRSStatus = ST.StatusTypeName
INNER JOIN #batchTableConsults bt ON zraw._conConsult.BatchLogID = bt.BatchLogID
--TODO: finish cleaning up and adding to the list.
--build a table variable
DECLARE @tableVar TABLE
(
ConsultSID BIGINT,
MidStepStationNumber varchar(10),
StationID SMALLINT
UNIQUE NONCLUSTERED (ConsultSID, MidStepStationNumber, StationID)--look up SQL14 and above indexes
)
--insert ConsultSID and MidStepStationNumber to table variable
INSERT INTO @tableVar
SELECT [ConsultSID],
CASE WHEN ToRequestServiceName like '%-589A4' THEN '589A4'
WHEN ToRequestServiceName like '%-589A5' THEN '589A5'
WHEN ToRequestServiceName like '%-589A7' THEN '589A7'
WHEN ToRequestServiceName like '%-657A4' THEN '657A4'
WHEN ToRequestServiceName like '%-657A5' THEN '657A5'
WHEN ToRequestServiceName like '%-528A5' THEN '528A5'
WHEN ToRequestServiceName like '%-528A6' THEN '528A6'
WHEN ToRequestServiceName like '%-528A7' THEN '528A7'
WHEN ToRequestServiceName like '%-528A8' THEN '528A8'
WHEN ToRequestServiceName like '%-636A6' THEN '636A6'
WHEN ToRequestServiceName like '%-636A8' THEN '636A8'
WHEN ToRequestServiceName like '%STL' THEN '657' --'(657) VA Heartland East (Saint Louis MO)'
WHEN ToRequestServiceName like '%WNY' THEN '528' --'(528) Upstate New York HCS'
WHEN ToRequestServiceName like '%WI' THEN '589A7'
ELSE cast(Sta3n as varchar(50))
END AS MidStepStationNumber
,NULL AS StationID
FROM [zraw].[_conConsult] CON
INNER JOIN #batchTableConsults bt ON CON.BATCHLOGID = bt.BatchLogID;
--update @tableVar StationID by joining Stations to @tableVar
UPDATE @tableVar
SET StationID = ST.StationID
FROM dbo.Stations ST
INNER JOIN @tableVar ON ST.Station6A = [@tableVar].[MidStepStationNumber];
--Update raw table StationID FK from @tableVar by joining ConsultSID
UPDATE [zraw].[_conConsult] SET StationID = [@tableVar].StationID
FROM @tableVar
INNER JOIN [zraw].[_conConsult] CON ON [@tableVar].ConsultSID = CON.ConsultSID
END TRY
BEGIN CATCH
PRINT 'There was an error synchronizing OneConsult Stations Type data' + CHAR(13);
THROW;
END CATCH
END