Solved

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

Posted on 2016-11-07
6
52 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 28

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 28

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 28

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 28

Expert Comment

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

Regards,
Pawan
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Reporting Services Date Issue 2 51
SSRS 2008 r2 - finding the selection Screen 3 144
Upgrading my SSIS package in VS 2012 6 83
SSRS 2016 Rendering HTML tables 3 54
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

680 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