Creating a pre-filled field during a Select_Into Statement

In preparation to performing a Union query between a table of procedures using CPT codes and a table of procedures using ICD-9 codes, I want my field names to match, but for a field called “PROC_CODE” to identify which code is which type once the two tables are combined.

I did a little googling and the answers I found seemed to be overly complicated for what I needed: Basically making a blank column, and then updating it. Alternatively, creating the table without that column and then adding it after the fact.

So I tried something simple and intuitive (and probably well-documented if not for my weak google-fu) – and it appears to work. Without further ado, this is my query that makes “CPT” the default string inside the “PROC_CODE_TYPE” field:

IF OBJECT_ID(‘tempdb..##EncListCPTMatches’) IS NOT NULL DROP TABLE ##EncListCPTMatches;

Select Distinct ##EncList.PAT_ID,
                             ##EncList.PAT_MRN_ID,
                             ##EncList.PAT_ENC_CSN_ID,
                             ##EncList.SURG_DT,
                              ‘CPT’ AS PROC_CODE_TYPE,
                              Proc_CPT.CPT_CODE AS PROC_CODE,
                              PROC.PROC_NAME AS PROC_NAME
INTO ##EncListCPTMatches
FROM ##EncList
INNER JOIN PAT_SURG
ON ##EncList.PAT_ID = PAT_SURG.PAT_ID
AND PAT_SURG.SURG_DATE = ##Enclist.SURG_DT
INNER JOIN PROC 
ON PAT_SURG.PROC_ID = PROC.PROC_ID
INNER JOIN Proc_CPT
ON PROC.PROC_ID = Proc_CPT.PROC_ID
AND Proc_CPT.CPT_CODE IN (/*Approved CPTs*/‘32220’, ‘32662’, ‘32674’, ‘33020’)
ORDER BY ##EncList.PAT_MRN_ID, ##EncList.SURG_DT

Advertisements

Deleting Rows from one table based on another table

In this example, I needed to delete the rows with null values in the ##EncList table where the CaseNo matched the CaseNo in the ##EncListWithProcStartEnd table.

DELETE EL
FROM ##EncList EL
INNER JOIN ##EncListWithProcStartEnd
ON ##EncList.CaseNo = ##EncListWithProcStartEnd.CaseNo
WHERE ##EncList.Start IS NULL
AND ##EncList.End IS NULL

I made the nickname for the ##EncList in the “DELETE” phrase, because as pointed out on StackOverflow, if one accidentally had “DELETE ##EncList” highlighted while running this query, you would delete your whole table.

Error: “Arithmetic overflow error converting numeric to data type numeric.”

Today I learned that if you get this error, it is probably because you did something weird with your precision and scale parameters while defining a numeric datatype.

EG – the following portion of a select statement was used to determine patient age at service:

CAST((DATEDIFF(d, PT.DOB, CASE.DOS)/365.25) AS numeric(3,1)) AS PtAge

I received the following error and the query was terminated:

“Arithmetic overflow error converting numeric to data type numeric.”

This happened because I was imagining all patients were under 100 years old in the population I was querying.

The “numeric(3,1)” portion of the clause means that the number of numeric characters allowed (precision) of 3 conflicted with the number of characters after the decimal point (scale) of 1 for patients who were over 99 years old.

The corrected portion of this query is to increase the precision to 4, so that patients with ages over 100 would not be excluded.

CAST((DATEDIFF(d, PT.DOB, CASE.DOS)/365.25) AS numeric(4,1)) AS PtAge

This allowed for ages like “101.2” because there were 4 total characters, one of which was after the decimal.