Solved

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

Posted on 2016-11-07
6
72 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41877428
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
ID: 41877522
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 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41878099
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
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41879942
Hi,
Is the query given above worked for the first part?

Regards,
Pawan
0
 

Author Comment

by:Becky Edwards
ID: 41882791
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 29

Expert Comment

by:Pawan Kumar
ID: 41883226
Excellent logic Becky !!

Regards,
Pawan
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

632 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