First, here is the sample code:
CREATE TABLE dbo.#Isolates
(
MICSourceID VARCHAR(3)
,VisitID VARCHAR(256)
,PatientID VARCHAR(256)
,AccountNumber VARCHAR(256)
,UnitNumber VARCHAR(256)
,EmrNumber VARCHAR(256)
,Name VARCHAR(256)
,NameLast VARCHAR(256)
,NameFirst VARCHAR(256)
,BirthDateTime DATETIME
,Sex VARCHAR(256)
,InAdmitDateTime DATETIME
,FacilityID VARCHAR(256)
,SpecimenID VARCHAR(255)
,SpecimenNumber VARCHAR(255)
,CollectionDateTime DATETIME
,CollectionDate DATE
,[Status] VARCHAR(255)
,CollectionLocationID VARCHAR(255)
,[Source] VARCHAR(255)
,ProcedureID VARCHAR(256)
,ResultSeqID INT
,OrganismID VARCHAR(256)
,DMicOrganismID VARCHAR(256)
,OrganismName VARCHAR(256)
,OrganismCode VARCHAR(255)
,DuplicateOrganismCode VARCHAR(255)
,MTOrganismCode VARCHAR(255)
,AROPathogenCategory VARCHAR(256)
,OrganismPreferredTerm VARCHAR(256)
,EligibleAntimicrobialType VARCHAR(256)
,LocationEntryDescription VARCHAR(256)
,LocationCode VARCHAR(256)
,LocationDescription VARCHAR(256)
,LocationLabel VARCHAR(256)
,InpatientLocation CHAR(1)
,DMicSourceID VARCHAR(256)
,SourceName VARCHAR(256)
,SourceNHSNCode VARCHAR(256)
,SourceConceptID VARCHAR(256)
,SourcePreferredTerm VARCHAR(256)
,SourceARSpecimenSource CHAR(1)
,SourceLowerResp CHAR(1)
,SourceBlood CHAR(1)
,SourceCSF CHAR(1)
,SourceUrine CHAR(1)
,SourceType CHAR(1)
,StaphylococcusAureus CHAR(1)
,PBP2aMTResult VARCHAR(256)
,PBP2aNHSNResult VARCHAR(3)
,PCRMecMTResult VARCHAR(256)
,PCRMecNHSNResult VARCHAR(3)
,EscapedIdentifierFileName NVARCHAR(MAX)
,EscapedIdentifierSetID NVARCHAR(MAX)
,IsolateCollectedDuringMonth CHAR(1)
,FinalStatus CHAR(1)
,OrganismInNHSNCodeset CHAR(1)
DEFAULT 'N'
,LocationAUR CHAR(1)
,LocationDefaultCode CHAR(1)
,SourceInNHSNCodeset CHAR(1)
DEFAULT 'N'
,IsolateIdentifierTooLong CHAR(1)
DEFAULT 'N'
,NoTestsDone CHAR(1)
DEFAULT 'N'
,PatientIdentifierInvalid CHAR(1)
DEFAULT 'N'
,LocationAgeGenderRulesCheck CHAR(1)
DEFAULT 'Y'
,AdmitDateTimeInvalid CHAR(1)
DEFAULT 'N'
,BirthDateTimeInvalid CHAR(1)
DEFAULT 'N'
,SameDayDuplicateGroup BIGINT
,SameDayDuplicate CHAR(1)
DEFAULT 'N'
,LastInvasiveResult14DaysOrLess CHAR(1)
DEFAULT 'N'
,FirstNonInvasiveResultInMonth CHAR(1)
DEFAULT 'Y'
,IsolateCountedForReport CHAR(1)
);
WITH CTEIsolateMonth
AS
( SELECT
I.MICSourceID
,I.PatientID
,I.SpecimenID
,I.CollectionDateTime
,I.ProcedureID
,I.ResultSeqID
,COALESCE(I.DuplicateOrganismCode, I.OrganismCode) AS OrganismCode
,I.OrganismID
,I.FirstNonInvasiveResultInMonth
,I.IsolateCountedForReport
,ROW_NUMBER() OVER ( PARTITION BY
I.PatientID
,COALESCE(I.DuplicateOrganismCode, I.OrganismCode)
ORDER BY
I.CollectionDateTime
) AS RowNum
FROM
dbo.#Isolates AS I
WHERE
/* Only those in the calendar month count for the report */
I.IsolateCountedForReport = 'Y'
/* Non-Invasive Source Type */
AND I.SourceType = 'N' )
UPDATE
dbo.#Isolates
SET
FirstNonInvasiveResultInMonth = 'N'
,IsolateCountedForReport = 'N'
WHERE
#Isolates.PatientID = CTEIsolateMonth.PatientID
AND COALESCE(#Isolates.DuplicateOrganismCode, #Isolates.OrganismCode) = CTEIsolateMonth.OrganismCode
AND #Isolates.CollectionDateTime = CTEIsolateMonth.CollectionDateTime
AND CTEIsolateMonth.RowNum > 1;
The behavior I'm getting when formatting with SQL Prompt is:
1) It strips away all of the "#Isolates" in the WHERE clause.
2) Then, if I format a second time, it inserts "CTEIsolateMonth" where the "#Isolates" was, for everything except DuplicateOrganismCode (which doesn't exist in CTEIsolateMonth).
This changes the meaning of the code, obviously. Is there a setting I can use to tell it to hang on to the existing tablename, whether the format tool thinks it's necessary or not?
The behavior I'm getting when formatting with SQL Prompt is:
1) It strips away all of the "#Isolates" in the WHERE clause.
2) Then, if I format a second time, it inserts "CTEIsolateMonth" where the "#Isolates" was, for everything except DuplicateOrganismCode (which doesn't exist in CTEIsolateMonth).
This changes the meaning of the code, obviously. Is there a setting I can use to tell it to hang on to the existing tablename, whether the format tool thinks it's necessary or not?