Working with Bad Data

When you are dealing with data from multiple sources, it will not always be in the correct format you need it. In this example we will look at converting data types. There are many different data types, but those I come in the most contact with dealing with healthcare data are varchar, nvarchar and datetime.

When you create or import a table, you can see what you data types are when you expand the fields in your table. Its very important that your data types are correct, especially if those are the fields that will be used in your joins.

In this example we will be changing our date from nvarchar to datetime, and using the replace function.

In the dataset below, we need the date in a more usable format and be removing the decimal in the diagnosis codes.

Using CAST allows you to select what you want the data in the field to be. In this instance we want to change the data to DATETIME. At the same time, we are using the REPLACE function to strip our diagnosis codes of their decimal.

Select Distinct

 CAST ([ServiceDate] as Datetime) as [From_Service_Date]

,[Diag Code] = CASE WHEN ltrim(rtrim(DXCode)) Is NULL THEN ''                

               ELSE REPLACE (ltrim(rtrim(DXCode)),'.','') END    

From YOUR TABLE

 Try this and see how it works for you.

Adding in filters

    We left off with a very simple Select Statement that will pull all of the data without exception. But what about when you want to see specific data. At that point you want to add a filter, or in the case of SQL, you need to add a WHERE clause. The WHERE is the place in your statement you can specify exactly what you want SQL to show. There are many ways to filter your information, this is one of many we will cover. This is where you first start t get your feet wet with specifics.

    In the statement below we will add a very simple where condition. Once again, healthcare data is all about specifics, so lets start with a few of the details we like to see in healthcare analytics. (I plan on building a database with some examples of the type of data we want to see, but it takes time to make up phantom data)


SELECT  HEALTHPLAN

,[MEMBERIDTOUSE]
,[First Name]
,[Last Name]
,CONVERT (varchar (10),[enc_Date], 101) as [From Date]
,CONVERT (varchar (10),[enc_Date], 101) as [To Date]
,ProviderType1= ltrim(rtrim('20')),Diag1= CASE WHEN ltrim(rtrim(DX_Code)) Is NULL THEN ''
ELSE REPLACE (ltrim(rtrim(DX_Code)),'.','') END
,ProviderType2=''
,Diag2= ''
,ProviderType3=''
,Diag3= ''
,ProviderType4=''
,Diag4= ''
,ProviderType5=''
,Diag5= ''
,ProviderType6=''
,Diag6= ''
,ProviderType7=''
,Diag7= ''
,ProviderType8=''
,Diag8= ''
,ProviderType9=''
,Diag9= ''
FROM DATABASE_NAME.dbo.TABLE_NAMEwhere MEMBERIDTOUSE is not null and DX_Code is not null


 So in this statement we only want to those members that have data in the fields. This statement will exclude emmty field spaces for MEMBERIDTOUSE and DX_Code. There is alot in this script that we can cover, so we will continue to point back to it. Thats it for today, short and sweet. (That's what she said)


 FYI, we use [ ] when the field names are not connected, although I sometimes use them regardless of how the field is labeled.

Writing your first SQL statement

Lets enter the world of SQL server and write your first SQL select statement. To begin, you must understand the SQL syntax. We will start with the SELECT and what it means. With SELECT you are telling SQL server the fields or results you wish to see returned

Then we move to the FROM portion of the statement. The FROM portion is telling SQL server which table the data resides or is housed.

SELECT {Field1}, {Field2}, etc
FROM {Your Table}

Voila!!!!, you have just created your first SQL Select Statement

See below for more info

http://beginner-sql-tutorial.com/sql-select-statement.htm

SQL for the new user

I remember when I first started using SQL Server, I was just starting in finance and we had just begun making the change from Access databases. The hardest part about using SQL when you first start is how to put what you want to do into the correct SQL syntax. In my current department I am surrounded with novice users that need to use SQL to query our many databases. They run across many obstacles, the biggest is translating the request to SQL language.

Aggregating Data – if you used Access you remember the group by button, this is no different.

SELECT Distinct X,COUNT(X)as VOLUME

FROM Your Table

Group By X

The aggregated column will be named volume. We could have also used Sum. Get to know these aggregating techniques, because they can save you time in the long run. This way you will not have to copy/paste the data from SQL to excel and then create a pivot table for your aggregation.  (which I know you do)

How To Deal with RAPS Submission Problems

There are many different data issues that must be be dealt when preparing your data for submission to the Risk Adjustment Processing System (RAPS). To reduce the risk of your submission file being rejected, its best to address a lot of the possible errors that could occur in your script. Below is the lay out that for RAPS submissions.

RAPS Data requires:

• HIC Number
• Diagnosis Codes
• From Date of Service
• Through Date of Service
• Provider Type

The most common errors that we want to look out for are incorrect date formats, spaces before and after the data, nulls, and periods in the diagnoisis codes.  Use the below script to handle these common errors.

SET NOCOUNT ON;SELECT DISTINCT
 ,ltrim(rtrim([HIC Number])) as [HIC Number]
,ltrim(rtrim([First Name])) as [First Name]
,ltrim(rtrim([Last Name])) as [Last Name],CONVERT (varchar (10),[DosFrom], 101) as [From Date]

,CONVERT (varchar (10),[DosTo], 101) as [To Date]
,[PT1] as [Provider Type 1]
,[Diag 1]= CASE WHEN [diag 1] Is NULL THEN ''
ELSE [Diag 1] END
,[Provider Type 2] = CASE WHEN [Diag 2] Is NULL THEN ''
ELSE '20' END
,[Diag 2]= CASE WHEN [diag 2] Is NULL THEN ''
ELSE [Diag 2] END
,[Provider Type 3] = CASE WHEN [Diag 3] Is NULL THEN ''
ELSE '20' END
,[Diag 3]= CASE WHEN [diag 3] Is NULL THEN ''
ELSE [Diag 3] END
,[Provider Type 4] = CASE WHEN [Diag 4] Is NULL THEN ''
ELSE '20' END
,[Diag 4]= CASE WHEN [diag 4] Is NULL THEN ''
ELSE [Diag 4] END
,[Provider Type 5] = CASE WHEN [Diag 5] Is NULL THEN ''
ELSE '20' END
,[Diag 5]= CASE WHEN [diag 5] Is NULL THEN ''
ELSE [Diag 5] END
,[Provider Type 6]= CASE WHEN [Diag 6] Is NULL THEN ''
ELSE '20' END
,[Diag 6]= CASE WHEN [diag 6] Is NULL THEN ''
ELSE [Diag 6] END
,[Provider Type 7] = CASE WHEN [Diag 7] Is NULL THEN ''
ELSE '20' END
,[Diag 7]= CASE WHEN [diag 7] Is NULL THEN ''
ELSE [Diag 7] END
,[Provider Type 8]= CASE WHEN [Diag 8] Is NULL THEN ''
ELSE '20' END
,[Diag 8]= CASE WHEN [diag 8] Is NULL THEN ''
ELSE [Diag 8] END
,[Provider Type 9] = CASE WHEN [Diag 9] Is NULL THEN ''
ELSE '20' END
,[Diag 9]= CASE WHEN [diag 9] Is NULL THEN ''
ELSE [Diag 9] END

This is just a general example for you to use as a guide. However, this format does work with most coding vendors extracts.