Solved

Right Trim or Left Trim a column in SQL up to and including the comma separator

Posted on 2016-11-07
6
25 Views
Last Modified: 2016-11-14
I have read through many explanations and cannot find one that helps me with this.  Hopefully others will find the solution useful also.

I have a field with lastname, firstname.  I need to split these into two separate fields and include consideration for null values.
Null values should display as blank.
The fields must be 15 spaces for last name, 14 spaces for first name, where the name starts at the left of the column and leave empty spaces at the end.

Example:
Smithsonian,Charles would end up as
[Smithsonian    ]
[Charles       ]
disregard the [ ] those are for explanation only.

Also, when I add this to my existing query I get this error message.
"Msg 116, Level 16, State 1, Line 25
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

I am using SQL Server 2008 and VS 2013.  
This will one field in the entire query that will eventually end up as a stored procedure to be used in Visual Studio.
I have attached my entire query as I now understand that helps.
PLEASE disregard my attempt to do this inside the existing query, it does not work like it is supposed to. (Column SpouseLN)
metriq_for_EE.sql
0
Comment
Question by:Becky Edwards
  • 4
  • 2
6 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Try this
--
DECLARE @ AS VARCHAR(MAX) = 'Smithsonian,Charles '
SELECT 
SUBSTRING((@),CHARINDEX(',',@)+1,20) AS PatientLast
,SUBSTRING((@),1,CHARINDEX(',',@)-1) AS PatientFirst
--

Open in new window


O/p
----------
PatientLast      PatientFirst
Charles               Smithsonian

Try

SELECT 
  SUBSTRING('',1,5) AS Hospital_ID  --no hospital ids have 5 or less characters
 ,SUBSTRING((@),CHARINDEX(',',@)+1,20) AS PatientLast
 ,SUBSTRING((@),1,CHARINDEX(',',@)-1) AS PatientFirst
 ,SUBSTRING(ISNULL(p.PAT_MIDDLE_NAME,''),1,1) AS PatientMI
 ,SUBSTRING(CAST(p.PAT_MRN_ID AS VARCHAR),1,11) AS Medical_Record_Number
 ,SUBSTRING(CAST(REPLACE(p.SSN,'-','') AS VARCHAR),1,9) AS SSN
 ,SUBSTRING(convert(varchar, p.BIRTH_DATE, 112),1,10) AS Birthdate
 ,SUBSTRING('',1,4)AS ICD03
 ,SUBSTRING('',1,5) AS ICD9
 ,DATELASTCONTACT = (SELECT max(DISCH_date_time) FROM clarity.dbo.HSP_ACCOUNT hsp WHERE hsp.HSP_ACCOUNT_ID = pe.HSP_ACCOUNT_ID)
 ,CASE WHEN county.ABBR IS NULL THEN '  ' ELSE SUBSTRING(county.ABBR,1,3) END AS COUNTY
 ,SUBSTRING(p.ADD_LINE_1,1,38) AS Addr
 ,SUBSTRING(p.CITY,1,15) AS City
 ,SUBSTRING(Patient_State.NAME,1,2) AS State
 ,SUBSTRING(CAST(p.ZIP AS VARCHAR),1,5) AS Zipcode
 ,SUBSTRING('',1,4) AS Zip2
 ,SUBSTRING(CAST(REPLACE(p.HOME_PHONE,'-','') AS VARCHAR),1,10) AS Telephone
 ,SUBSTRING('',1,15) AS MaidenNm
 
 --,SpouseLN = (select ec.emerg_name, SUBSTRING(RTRIM(ec.emerg_name),2, LEN(RTRIM(ec.emerg_name))) from emergency_contacts ec
--		INNER JOIN ZC_EMERG_PAT_REL ZC ON ec.EMERG_PAT_REL_C = zc.EMERG_PAT_REL_C
--		where zc.EMERG_PAT_REL_C = 17)

 ,SUBSTRING('',1,15) AS SpouseLN
 ,SUBSTRING('',1,14) AS SpouseFN
 ,SUBSTRING('',1,3) AS PlaceOfBirth
 ,SUBSTRING('',1,1) AS Autopsy
,CASE WHEN gender.RCPT_MEM_SEX_C = 1 THEN 2 WHEN gender.RCPT_MEM_SEX_C = 2 THEN 1 ELSE gender.RCPT_MEM_SEX_C END AS Sex 

,CASE
WHEN prace.PATIENT_RACE_C is null THEN '  ' 
--when prace.PATIENT_RACE_C = 1 then 1
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '1' THEN '1' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '2' THEN '2' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '3' THEN '3'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '4' THEN '96'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '5' THEN '7'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '6' THEN '98'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '7' THEN '99'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '8' THEN '99'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '9' THEN '4'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '10' THEN '6'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '11' THEN '5' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '12' THEN '8' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '13' THEN '10' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '14' THEN '15' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '15' THEN '7' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '16' and prace.NAME = 'Guamanian' then '22'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '16' and prace.NAME = 'Chamorro' then '21'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '17' THEN '27' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '18' THEN '97' 
END AS RACE

,CASE
WHEN p.ETHNIC_GROUP_C = 1 THEN 0
WHEN p.ETHNIC_GROUP_C = 5 THEN 1
WHEN p.ETHNIC_GROUP_C = 6 THEN 2
WHEN p.ETHNIC_GROUP_C = 7 THEN 3
WHEN p.ETHNIC_GROUP_C = 8 THEN 4
WHEN p.ETHNIC_GROUP_C = 2 THEN 1
ELSE 9
END AS SpanishOriginEthnicity

,CASE
WHEN p.MARITAL_STATUS_C is null THEN '?' 
WHEN SUBSTRING(CAST(p.MARITAL_STATUS_C AS VARCHAR),1,1) = '6' THEN '9' 
ELSE SUBSTRING(CAST(p.MARITAL_STATUS_C AS VARCHAR),1,1)
END AS MaritalStatus

,SUBSTRING('',1,255) AS OTHER
,SUBSTRING(comb.REF_BILL_CODE,1,7) as ICD10

 FROM clarity.dbo.PAT_ENC						pe
 inner join CLARITY.DBO.HSP_ACCOUNT				ha ON pe.HSP_ACCOUNT_ID=ha.HSP_ACCOUNT_ID 
 INNER JOIN clarity.dbo.PATIENT					p ON pe.PAT_ID=p.PAT_ID
 INNER JOIN clarity.dbo.PAT_ENC_DX dx ON		dx.PAT_ENC_CSN_ID=pe.PAT_ENC_CSN_ID
 LEFT OUTER JOIN clarity.dbo.CLARITY_EDG		edg ON edg.DX_ID=dx.DX_ID
 LEFT OUTER JOIN clarity.dbo.V_CODING_COMBINED_DX_LIST comb ON comb.HSP_ACCOUNT_ID=pe.HSP_ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.ACCOUNT			acct ON pe.ACCOUNT_ID=acct.ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.CLARITY_EEP		eep ON acct.EMPLOYER_ID=eep.EMPLOYER_ID
 LEFT OUTER JOIN clarity.dbo.ZC_EMPY_STAT		ZC_EMPY_STAT ON acct.EMPY_STAT_C=ZC_EMPY_STAT.EMPY_STAT_C
 LEFT OUTER JOIN clarity.dbo.ZC_STATE			Guar_State ON acct.STATE_C=Guar_State.STATE_C
 LEFT OUTER JOIN clarity.dbo.ACCT_GUAR_PAT_INFO ACCT_GUAR_PAT_INFO ON acct.ACCOUNT_ID=ACCT_GUAR_PAT_INFO.ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_GUAR_REL_TO_PAT ZC_GUAR_REL_TO_PAT ON ACCT_GUAR_PAT_INFO.GUAR_REL_TO_PAT_C=ZC_GUAR_REL_TO_PAT.GUAR_REL_TO_PAT_C
 LEFT OUTER JOIN clarity.dbo.ZC_STATE			Patient_State ON p.STATE_C=Patient_State.STATE_C
 LEFT OUTER JOIN clarity.dbo.ZC_SEX				gender ON p.SEX_C=gender.RCPT_MEM_SEX_C
 LEFT OUTER JOIN clarity.dbo.CLARITY_EEP		eep_Patient ON p.EMPLOYER_ID=eep_Patient.EMPLOYER_ID
 LEFT OUTER JOIN clarity.dbo.ZC_EMPY_STATUS		ZC_EMPY_STATUS ON p.EMPY_STATUS_C=ZC_EMPY_STATUS.EMPY_STATUS_C
 LEFT OUTER JOIN clarity.dbo.CLARITY_DEP		dep ON dep.DEPARTMENT_ID=pe.DEPARTMENT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_APPT_STATUS		apptstatus ON apptstatus.APPT_STATUS_C=pe.APPT_STATUS_C
 LEFT OUTER JOIN clarity.dbo.PATIENT_RACE		race ON race.PAT_ID=p.PAT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_PATIENT_RACE	prace ON prace.PATIENT_RACE_C=race.PATIENT_RACE_C
 --LEFT OUTER JOIN clarity.dbo.CLARITY_PRC		prc ON prc.PRC_ID=pe.APPT_PRC_ID
 --LEFT OUTER JOIN clarity.dbo.ZC_LANGUAGE		lang ON lang.LANGUAGE_C=p.LANGUAGE_C
 --LEFT OUTER JOIN clarity.dbo.ZC_MARITAL_STATUS	ms ON ms.MARITAL_STATUS_C=p.MARITAL_STATUS_C
 LEFT OUTER JOIN clarity.dbo.ZC_COUNTY			county ON p.COUNTY_C=county.COUNTY_C
 --LEFT OUTER JOIN clarity.dbo.ZC_ACCT_BASECLS_HA basecls ON basecls.ACCT_BASECLS_HA_C=hsp.ACCT_BASECLS_HA_C
   
WHERE 
--(ha.DISCH_DATE_TIME >= (SELECT EPIC_UTIL.EFN_DIN('mb')))     --discharge date beginning first day of last month
--AND (ha.DISCH_DATE_TIME < (SELECT EPIC_UTIL.EFN_DIN('me')))   --discharge date end last day of last month
--AND ha.COMPLETN_STS_HA_C = 4                                 --CODING complete
--AND
--cancer codes as specified in extract specification
comb.REF_BILL_CODE IN ('2.1','2.2','7.5','7.6','9.3','33.9','C00.1','C00.2','C00.3','C00.4','C00.6','C00.9','C01',
'C02.0','C02.1','C02.2','C02.3','C02.4','C02.9','C03','C03.0','C03.1','C03.9','C03.9','C04','C04.0','C04.1','C04.9',
'C04.a','C07','C08.1','C08.9','C09','C09.0','C09.1','C09.8','C09.9','C10','C10.0','C10.1','C10.2','C10.3','C10.4',
'C10.8','C10.8','C10.9','C11','C11.0','C11.1','C11.2','C11.3','C11.8','C11.9','C12','C13','C13.0','C13.1','C13.2',
'C13.8','C13.9','C14','C14.0','C14.2','C14.8','C15','C15.0','C15.1','C15.2','C15.3','C15.4','C15.5','C15.8','C15.9',
'C16','C16.0','C16.1','C16.2','C16.3','C16.4','C16.5','C16.6','C16.8','C16.9','C17','C17.0','C17.1','C17.2','C17.3',
'C17.8','C17.9','C18','C18.0','C18.1','C18.2','C18.3','C18.4','C18.5','C18.6','C18.7','C18.8','C18.9','C19','C20',
'C21','C21.0','C21.1','C21.2','C21.8','C22','C22.0','C22.1','C22.2','C22.3','C22.4','C22.7','C22.9','C23','C24',
'C24.0','C24.1','C24.8','C24.9','C25','C25.0','C25.1','C25.2','C25.3','C25.4','C25.7','C25.8','C25.9','C26','
C26.0','C26.1','C26.8','C26.9','C30','C30.0','C30.1','C31','C31.0','C31.1','C31.2','C31.3','C31.8','C31.9',
'C32','C32.0','C32.1','C32.2','C32.3','C32.8','C32.9','C33','C34','C34.0','C34.1','C34.2','C34.3','C34.8',
'C34.9','C37','C38','C38.0','C38.1','C38.2','C38.3','C38.4','C38.8','C39','C39.0','C39.8','C39.9','C40','C40.0',
'C40.1','C40.2','C40.3','C40.8','C40.9','C41','C41.0','C41.1','C41.2','C41.3','C41.4','C41.8','C41.9','C43',
'C43.0','C43.1','C43.2','C43.3','C43.4','C43.5','C43.6','C43.7','C43.8','C43.9','C44','C44.0','C44.1','C44.2','C44.3',
'C44.4','C44.5','C44.6','C44.7','C44.8','C44.9','C45','C45.0','C45.1','C45.2','C45.7','C45.9','C46','C46.0','C46.1',
'C46.2','C46.3','C46.7','C46.8','C46.9','C47','C47.0','C47.1','C47.2','C47.3','C47.4','C47.5','C47.6','C47.8','C47.9',
'C48','C48.0','C48.1','C48.2','C48.8','C49','C49.0','C49.1','C49.2','C49.3','C49.4','C49.5','C49.6','C49.8','C49.9',
'C50','C50.0','C50.1','C50.2','C50.3','C50.4','C50.5','C50.6','C50.8','C50.9','C51','C51.0','C51.1','C51.2','C51.8',
'C51.9','C52','C53','C53.0','C53.1','C53.8','C53.9','C54','C54.0','C54.1','C54.2','C54.3','C54.8','C54.9','C55','C56',
'C57','C57.0','C57.1','C57.2','C57.3','C57.4','C57.7','C57.8','C57.9','C58','C60','C60.0','C60.1','C60.2','C60.8',
'C60.9','C61','C62','C62.0','C62.1','C62.9','C63','C63.0','C63.1','C63.2','C63.7','C63.8','C63.9','C64','C65','C66',
'C67','C67.0','C67.1','C67.2','C67.3','C67.4','C67.5','C67.6','C67.7','C67.8','C67.9','C68','C68.0','C68.1','C68.8',
'C68.9','C69','C69.0','C69.1','C69.2','C69.3','C69.4','C69.5','C69.6','C69.8','C69.9','C70','C70.0','C70.1','C70.9',
'C71','C71.0','C71.1','C71.2','C71.3','C71.4','C71.5','C71.6','C71.7','C71.8','C71.9','C72','C72.0','C72.1','C72.2',
'C72.3','C72.4','C72.5','C72.8','C72.9','C73','C74','C74.0','C74.1','C74.9','C75','C75.0','C75.1','C75.2','C75.3',
'C75.4','C75.5','C75.8','C75.9','C76','C76.0','C76.1','C76.2','C76.3','C76.4','C76.5','C76.7','C76.8','C77','C77.0',
'C77.1','C77.2','C77.3','C77.4','C77.5','C77.8','C77.9','C78','C78.0','C78.1','C78.2','C78.3','C78.4','C78.5','C78.6',
'C78.7','C78.8','C79','C79.0','C79.1','C79.2','C79.3','C79.4','C79.5','C79.6','C79.7','C79.8','C80','C81','C81.0',
'C81.1','C81.2','C81.3','C81.7','C81.9','C82','C82.0','C82.1','C82.2','C82.7','C82.9','C83','C83.0','C83.1','C83.2',
'C83.3','C83.4','C83.5','C83.6','C83.7','C83.8','C83.9','C84','C84.0','C84.1','C84.2','C84.3','C84.4','C84.5',
'C85.0','C85.1','C85.7','C85.9','C88.0','C88.1','C88.2','C88.3','C88.7','C88.9','C90','C90.0','C90.1','C90.2',
'C91','C91.0','C91.1','C91.2','C91.3','C91.4','C91.5','C91.7','C91.9','C92','C92.0','C92.1','C92.2','C92.3','C92.4',
'C92.5','C92.7','C92.9','C93','C93.0','C93.1','C93.2','C93.7','C93.9','C94','C94.0','C94.1','C94.2','C94.3','C94.4',
'C94.5','C94.7','C95','C95.0','C95.1','C95.2','C95.7','C95.9','C96','C96.0','C96.1','C96.2','C96.3','C96.7','C96.9',
'C97','CBS','CO2','CO2.a','coa.o','COG','COG.1','COG.2','COG.8','COG.9','COG.O','coo','coo.a','coo.o','COO.S',
'COS.1','COS.2','COS.9','cos.a','COS.O','D00.1','D00.2','D01','D01.0','D01.1','D01.2','D01.3','D01.4','D01.5',
'D01.7','D01.9','D02','D02.0','D02.3','D02.4','D03','D03.0','D03.1','D03.2','D03.3','D03.4','D03.5','D03.6',
'D03.7','D03.8','D03.9','D04','D04.0','D04.1','D04.2','D04.3','D04.4','D04.5','D04.6','D04.7','D04.8','D04.9',
'D05','D05.0','D05.1','D05.7','D05.9','D06','D06.0','D06.1','D06.7','D06.9','D07','D07.0','D07.1','D07.2','D07.3',
'D07.4','D09','D09.0','D09.1','D09.2','D09.7','D09.9','D32','D32.0','D32.1','D32.9','D33','D33.0','D33.1','D33.2',
'D33.3','D33.4','D33.7','D35.2','D35.3','D35.4','D42',
'D42.0','D42.1','D42.9','D43','D43.0','D43.1','D43.2','D43.3','D43.4','D43.7','D43.9','D44.3','D44.4','D44.5','D45',
'D46','D46.0','D46.1','D46.2','D46.3','D46.4','D46.7','D46.9','D47','D47.0','D47.1','D47.2','D47.3','D47.7','D47.9',
'DOO','DOO.O')

Open in new window

0
 

Author Comment

by:Becky Edwards
Comment Utility
This piece does not work.  

,SUBSTRING((@),1,CHARINDEX(',',@)-1) AS PatientFirst

The error is:
Invalid length parameter passed to the LEFT or SUBSTRING function.

Also, is it possible to build this as a separate subquery in the main query, so that I can have the following code included:

from emergency_contacts ec
            INNER JOIN ZC_EMERG_PAT_REL ZC ON ec.EMERG_PAT_REL_C = zc.EMERG_PAT_REL_C
            where zc.EMERG_PAT_REL_C = 17)

?

Thank you.
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
try this . I am checking for join condition.

SELECT 
  SUBSTRING('',1,5) AS Hospital_ID  --no hospital ids have 5 or less characters
 ,LTRIM(RTRIM(SUBSTRING((ISNULL(p.PAT_LAST_NAME,'')),1,20))) AS PatientLast
 ,LTRIM(RTRIM(SUBSTRING((ISNULL(p.PAT_FIRST_NAME,'')),1,14))) AS PatientFirst
 ,SUBSTRING(ISNULL(p.PAT_MIDDLE_NAME,''),1,1) AS PatientMI
 ,SUBSTRING(CAST(p.PAT_MRN_ID AS VARCHAR),1,11) AS Medical_Record_Number
 ,SUBSTRING(CAST(REPLACE(p.SSN,'-','') AS VARCHAR),1,9) AS SSN
 ,SUBSTRING(convert(varchar, p.BIRTH_DATE, 112),1,10) AS Birthdate
 ,SUBSTRING('',1,4)AS ICD03
 ,SUBSTRING('',1,5) AS ICD9
 ,DATELASTCONTACT = (SELECT max(DISCH_date_time) FROM clarity.dbo.HSP_ACCOUNT hsp WHERE hsp.HSP_ACCOUNT_ID = pe.HSP_ACCOUNT_ID)
 ,CASE WHEN county.ABBR IS NULL THEN '  ' ELSE SUBSTRING(county.ABBR,1,3) END AS COUNTY
 ,SUBSTRING(p.ADD_LINE_1,1,38) AS Addr
 ,SUBSTRING(p.CITY,1,15) AS City
 ,SUBSTRING(Patient_State.NAME,1,2) AS State
 ,SUBSTRING(CAST(p.ZIP AS VARCHAR),1,5) AS Zipcode
 ,SUBSTRING('',1,4) AS Zip2
 ,SUBSTRING(CAST(REPLACE(p.HOME_PHONE,'-','') AS VARCHAR),1,10) AS Telephone
 ,SUBSTRING('',1,15) AS MaidenNm
 
 --,SpouseLN = (select ec.emerg_name, SUBSTRING(RTRIM(ec.emerg_name),2, LEN(RTRIM(ec.emerg_name))) from emergency_contacts ec
--		INNER JOIN ZC_EMERG_PAT_REL ZC ON ec.EMERG_PAT_REL_C = zc.EMERG_PAT_REL_C
--		where zc.EMERG_PAT_REL_C = 17)

 ,SUBSTRING('',1,15) AS SpouseLN
 ,SUBSTRING('',1,14) AS SpouseFN
 ,SUBSTRING('',1,3) AS PlaceOfBirth
 ,SUBSTRING('',1,1) AS Autopsy
,CASE WHEN gender.RCPT_MEM_SEX_C = 1 THEN 2 WHEN gender.RCPT_MEM_SEX_C = 2 THEN 1 ELSE gender.RCPT_MEM_SEX_C END AS Sex 

,CASE
WHEN prace.PATIENT_RACE_C is null THEN '  ' 
--when prace.PATIENT_RACE_C = 1 then 1
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '1' THEN '1' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '2' THEN '2' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '3' THEN '3'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '4' THEN '96'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '5' THEN '7'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '6' THEN '98'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '7' THEN '99'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '8' THEN '99'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '9' THEN '4'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '10' THEN '6'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '11' THEN '5' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '12' THEN '8' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '13' THEN '10' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '14' THEN '15' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '15' THEN '7' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '16' and prace.NAME = 'Guamanian' then '22'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '16' and prace.NAME = 'Chamorro' then '21'
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '17' THEN '27' 
WHEN SUBSTRING(CAST(prace.PATIENT_RACE_C AS VARCHAR),1,1) = '18' THEN '97' 
END AS RACE

,CASE
WHEN p.ETHNIC_GROUP_C = 1 THEN 0
WHEN p.ETHNIC_GROUP_C = 5 THEN 1
WHEN p.ETHNIC_GROUP_C = 6 THEN 2
WHEN p.ETHNIC_GROUP_C = 7 THEN 3
WHEN p.ETHNIC_GROUP_C = 8 THEN 4
WHEN p.ETHNIC_GROUP_C = 2 THEN 1
ELSE 9
END AS SpanishOriginEthnicity

,CASE
WHEN p.MARITAL_STATUS_C is null THEN '?' 
WHEN SUBSTRING(CAST(p.MARITAL_STATUS_C AS VARCHAR),1,1) = '6' THEN '9' 
ELSE SUBSTRING(CAST(p.MARITAL_STATUS_C AS VARCHAR),1,1)
END AS MaritalStatus

,SUBSTRING('',1,255) AS OTHER
,SUBSTRING(comb.REF_BILL_CODE,1,7) as ICD10

 FROM clarity.dbo.PAT_ENC						pe
 inner join CLARITY.DBO.HSP_ACCOUNT				ha ON pe.HSP_ACCOUNT_ID=ha.HSP_ACCOUNT_ID 
 INNER JOIN clarity.dbo.PATIENT					p ON pe.PAT_ID=p.PAT_ID
 INNER JOIN clarity.dbo.PAT_ENC_DX dx ON		dx.PAT_ENC_CSN_ID=pe.PAT_ENC_CSN_ID
 LEFT OUTER JOIN clarity.dbo.CLARITY_EDG		edg ON edg.DX_ID=dx.DX_ID
 LEFT OUTER JOIN clarity.dbo.V_CODING_COMBINED_DX_LIST comb ON comb.HSP_ACCOUNT_ID=pe.HSP_ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.ACCOUNT			acct ON pe.ACCOUNT_ID=acct.ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.CLARITY_EEP		eep ON acct.EMPLOYER_ID=eep.EMPLOYER_ID
 LEFT OUTER JOIN clarity.dbo.ZC_EMPY_STAT		ZC_EMPY_STAT ON acct.EMPY_STAT_C=ZC_EMPY_STAT.EMPY_STAT_C
 LEFT OUTER JOIN clarity.dbo.ZC_STATE			Guar_State ON acct.STATE_C=Guar_State.STATE_C
 LEFT OUTER JOIN clarity.dbo.ACCT_GUAR_PAT_INFO ACCT_GUAR_PAT_INFO ON acct.ACCOUNT_ID=ACCT_GUAR_PAT_INFO.ACCOUNT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_GUAR_REL_TO_PAT ZC_GUAR_REL_TO_PAT ON ACCT_GUAR_PAT_INFO.GUAR_REL_TO_PAT_C=ZC_GUAR_REL_TO_PAT.GUAR_REL_TO_PAT_C
 LEFT OUTER JOIN clarity.dbo.ZC_STATE			Patient_State ON p.STATE_C=Patient_State.STATE_C
 LEFT OUTER JOIN clarity.dbo.ZC_SEX				gender ON p.SEX_C=gender.RCPT_MEM_SEX_C
 LEFT OUTER JOIN clarity.dbo.CLARITY_EEP		eep_Patient ON p.EMPLOYER_ID=eep_Patient.EMPLOYER_ID
 LEFT OUTER JOIN clarity.dbo.ZC_EMPY_STATUS		ZC_EMPY_STATUS ON p.EMPY_STATUS_C=ZC_EMPY_STATUS.EMPY_STATUS_C
 LEFT OUTER JOIN clarity.dbo.CLARITY_DEP		dep ON dep.DEPARTMENT_ID=pe.DEPARTMENT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_APPT_STATUS		apptstatus ON apptstatus.APPT_STATUS_C=pe.APPT_STATUS_C
 LEFT OUTER JOIN clarity.dbo.PATIENT_RACE		race ON race.PAT_ID=p.PAT_ID
 LEFT OUTER JOIN clarity.dbo.ZC_PATIENT_RACE	prace ON prace.PATIENT_RACE_C=race.PATIENT_RACE_C
 --LEFT OUTER JOIN clarity.dbo.CLARITY_PRC		prc ON prc.PRC_ID=pe.APPT_PRC_ID
 --LEFT OUTER JOIN clarity.dbo.ZC_LANGUAGE		lang ON lang.LANGUAGE_C=p.LANGUAGE_C
 --LEFT OUTER JOIN clarity.dbo.ZC_MARITAL_STATUS	ms ON ms.MARITAL_STATUS_C=p.MARITAL_STATUS_C
 LEFT OUTER JOIN clarity.dbo.ZC_COUNTY			county ON p.COUNTY_C=county.COUNTY_C
 --LEFT OUTER JOIN clarity.dbo.ZC_ACCT_BASECLS_HA basecls ON basecls.ACCT_BASECLS_HA_C=hsp.ACCT_BASECLS_HA_C
   
WHERE 
--(ha.DISCH_DATE_TIME >= (SELECT EPIC_UTIL.EFN_DIN('mb')))     --discharge date beginning first day of last month
--AND (ha.DISCH_DATE_TIME < (SELECT EPIC_UTIL.EFN_DIN('me')))   --discharge date end last day of last month
--AND ha.COMPLETN_STS_HA_C = 4                                 --CODING complete
--AND
--cancer codes as specified in extract specification
comb.REF_BILL_CODE IN ('2.1','2.2','7.5','7.6','9.3','33.9','C00.1','C00.2','C00.3','C00.4','C00.6','C00.9','C01',
'C02.0','C02.1','C02.2','C02.3','C02.4','C02.9','C03','C03.0','C03.1','C03.9','C03.9','C04','C04.0','C04.1','C04.9',
'C04.a','C07','C08.1','C08.9','C09','C09.0','C09.1','C09.8','C09.9','C10','C10.0','C10.1','C10.2','C10.3','C10.4',
'C10.8','C10.8','C10.9','C11','C11.0','C11.1','C11.2','C11.3','C11.8','C11.9','C12','C13','C13.0','C13.1','C13.2',
'C13.8','C13.9','C14','C14.0','C14.2','C14.8','C15','C15.0','C15.1','C15.2','C15.3','C15.4','C15.5','C15.8','C15.9',
'C16','C16.0','C16.1','C16.2','C16.3','C16.4','C16.5','C16.6','C16.8','C16.9','C17','C17.0','C17.1','C17.2','C17.3',
'C17.8','C17.9','C18','C18.0','C18.1','C18.2','C18.3','C18.4','C18.5','C18.6','C18.7','C18.8','C18.9','C19','C20',
'C21','C21.0','C21.1','C21.2','C21.8','C22','C22.0','C22.1','C22.2','C22.3','C22.4','C22.7','C22.9','C23','C24',
'C24.0','C24.1','C24.8','C24.9','C25','C25.0','C25.1','C25.2','C25.3','C25.4','C25.7','C25.8','C25.9','C26','
C26.0','C26.1','C26.8','C26.9','C30','C30.0','C30.1','C31','C31.0','C31.1','C31.2','C31.3','C31.8','C31.9',
'C32','C32.0','C32.1','C32.2','C32.3','C32.8','C32.9','C33','C34','C34.0','C34.1','C34.2','C34.3','C34.8',
'C34.9','C37','C38','C38.0','C38.1','C38.2','C38.3','C38.4','C38.8','C39','C39.0','C39.8','C39.9','C40','C40.0',
'C40.1','C40.2','C40.3','C40.8','C40.9','C41','C41.0','C41.1','C41.2','C41.3','C41.4','C41.8','C41.9','C43',
'C43.0','C43.1','C43.2','C43.3','C43.4','C43.5','C43.6','C43.7','C43.8','C43.9','C44','C44.0','C44.1','C44.2','C44.3',
'C44.4','C44.5','C44.6','C44.7','C44.8','C44.9','C45','C45.0','C45.1','C45.2','C45.7','C45.9','C46','C46.0','C46.1',
'C46.2','C46.3','C46.7','C46.8','C46.9','C47','C47.0','C47.1','C47.2','C47.3','C47.4','C47.5','C47.6','C47.8','C47.9',
'C48','C48.0','C48.1','C48.2','C48.8','C49','C49.0','C49.1','C49.2','C49.3','C49.4','C49.5','C49.6','C49.8','C49.9',
'C50','C50.0','C50.1','C50.2','C50.3','C50.4','C50.5','C50.6','C50.8','C50.9','C51','C51.0','C51.1','C51.2','C51.8',
'C51.9','C52','C53','C53.0','C53.1','C53.8','C53.9','C54','C54.0','C54.1','C54.2','C54.3','C54.8','C54.9','C55','C56',
'C57','C57.0','C57.1','C57.2','C57.3','C57.4','C57.7','C57.8','C57.9','C58','C60','C60.0','C60.1','C60.2','C60.8',
'C60.9','C61','C62','C62.0','C62.1','C62.9','C63','C63.0','C63.1','C63.2','C63.7','C63.8','C63.9','C64','C65','C66',
'C67','C67.0','C67.1','C67.2','C67.3','C67.4','C67.5','C67.6','C67.7','C67.8','C67.9','C68','C68.0','C68.1','C68.8',
'C68.9','C69','C69.0','C69.1','C69.2','C69.3','C69.4','C69.5','C69.6','C69.8','C69.9','C70','C70.0','C70.1','C70.9',
'C71','C71.0','C71.1','C71.2','C71.3','C71.4','C71.5','C71.6','C71.7','C71.8','C71.9','C72','C72.0','C72.1','C72.2',
'C72.3','C72.4','C72.5','C72.8','C72.9','C73','C74','C74.0','C74.1','C74.9','C75','C75.0','C75.1','C75.2','C75.3',
'C75.4','C75.5','C75.8','C75.9','C76','C76.0','C76.1','C76.2','C76.3','C76.4','C76.5','C76.7','C76.8','C77','C77.0',
'C77.1','C77.2','C77.3','C77.4','C77.5','C77.8','C77.9','C78','C78.0','C78.1','C78.2','C78.3','C78.4','C78.5','C78.6',
'C78.7','C78.8','C79','C79.0','C79.1','C79.2','C79.3','C79.4','C79.5','C79.6','C79.7','C79.8','C80','C81','C81.0',
'C81.1','C81.2','C81.3','C81.7','C81.9','C82','C82.0','C82.1','C82.2','C82.7','C82.9','C83','C83.0','C83.1','C83.2',
'C83.3','C83.4','C83.5','C83.6','C83.7','C83.8','C83.9','C84','C84.0','C84.1','C84.2','C84.3','C84.4','C84.5',
'C85.0','C85.1','C85.7','C85.9','C88.0','C88.1','C88.2','C88.3','C88.7','C88.9','C90','C90.0','C90.1','C90.2',
'C91','C91.0','C91.1','C91.2','C91.3','C91.4','C91.5','C91.7','C91.9','C92','C92.0','C92.1','C92.2','C92.3','C92.4',
'C92.5','C92.7','C92.9','C93','C93.0','C93.1','C93.2','C93.7','C93.9','C94','C94.0','C94.1','C94.2','C94.3','C94.4',
'C94.5','C94.7','C95','C95.0','C95.1','C95.2','C95.7','C95.9','C96','C96.0','C96.1','C96.2','C96.3','C96.7','C96.9',
'C97','CBS','CO2','CO2.a','coa.o','COG','COG.1','COG.2','COG.8','COG.9','COG.O','coo','coo.a','coo.o','COO.S',
'COS.1','COS.2','COS.9','cos.a','COS.O','D00.1','D00.2','D01','D01.0','D01.1','D01.2','D01.3','D01.4','D01.5',
'D01.7','D01.9','D02','D02.0','D02.3','D02.4','D03','D03.0','D03.1','D03.2','D03.3','D03.4','D03.5','D03.6',
'D03.7','D03.8','D03.9','D04','D04.0','D04.1','D04.2','D04.3','D04.4','D04.5','D04.6','D04.7','D04.8','D04.9',
'D05','D05.0','D05.1','D05.7','D05.9','D06','D06.0','D06.1','D06.7','D06.9','D07','D07.0','D07.1','D07.2','D07.3',
'D07.4','D09','D09.0','D09.1','D09.2','D09.7','D09.9','D32','D32.0','D32.1','D32.9','D33','D33.0','D33.1','D33.2',
'D33.3','D33.4','D33.7','D35.2','D35.3','D35.4','D42',
'D42.0','D42.1','D42.9','D43','D43.0','D43.1','D43.2','D43.3','D43.4','D43.7','D43.9','D44.3','D44.4','D44.5','D45',
'D46','D46.0','D46.1','D46.2','D46.3','D46.4','D46.7','D46.9','D47','D47.0','D47.1','D47.2','D47.3','D47.7','D47.9',
'DOO','DOO.O')

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Hi,
Is the query given above worked for the first part?

Regards,
Pawan
0
 

Author Comment

by:Becky Edwards
Comment Utility
Well I thought it was.  I was just informed that in order for SSIS to keep the correct amount of characters (or spaces) in a field, we need to use the CAST function.  


SELECT
  CAST('' as char(5)) AS Hospital_ID  --no hospital ids have 5 or less characters
,CAST((p.PAT_LAST_NAME) as char(20)) AS PatientLast

This way the extract displays the correct width for each field.

See attached example.

Now I have to rebuild each of these using CAST.

I still need to split the one field into two, regardless.

Becky
metriq_formatting.docx
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Excellent logic Becky !!

Regards,
Pawan
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now