Working with DTS Packages in SQL 2005

Once you have a repetitive task, that requires the same manipulation tasks to clean up your data, you will need to start using a combination of DTS packages and Stored Procedures.  A store procedure is nothing more than a query that is saved that you can call when needed. Stored Procedures are located in the Programmability portion of the SQL database.  Your procedure can be as simple or complex as you need it to be.  The Stored Procedure below is used to Delete data from a table

STORED PROCEDURE

USE [Dashboard]

GO

/****** Object:  StoredProcedure [dbo].[sp_Clear_Current_Dashboard]    Script Date: 11/06/2013 17:38:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE  PROCEDURE [dbo].[sp_Clear_Current_Dashboard]

AS

SET NOCOUNT ON

Delete from DATA_IMPORT_Q4_2012

Where not indicator_id in('13','21','34','35','61','200')

Once the Stored Procedure is created, you don’t need to save it. If at another time you need to update the procedure, right click on the Stored Procedure and select modify. It will open as ALTER PROCEDURE instead of CREATE.

CALL STORED PROCEDURE

To execute the Stored Procedure you simply write. That sp is for Stored Procedure.

execute [dbo].[sp_Clear_Current_Dashboard]

Statistical Sampling in SQL

 It’s been awhile since I have blogged, I have been extremely busy working with a few other SQL Server enthusiast working on the next great application, but that’s for another time. Today I want to share with you a simple way to pull a statistical sample using SQL. Using the code below, you can pull a quick sample


Select TOP 10  - The number of samples you want to pull back of your criteria

From – The Table your information is coming from

Order By NEWID() – I don’t want to cause any confusion by going into this function, just know that you need it. If you want to know more, start at this site below http://sqlmag.com/sql-server/change-behavior-rand-and-newid-sql-server-2005

Good luck

Curt

Working with Dates

As you begin to work more with data, you begin to understand the importance of working with dates. Data really has no meaning unless it is specific. As you begin to work with WHERE clauses, you are going to want to drill down to specific time frames.

You may want to see data within a specific date range, in those cases your WHERE clauses would look like this:

Where [Service Date] between '01-01-2013' and '12-01-2013'

Or you could have simply used DATEPART

Where DATEPART(YEAR,[Service Date]) = '2013'

If you will be using a script on a regular basis that may require a few date changes in several parts of the code, you may want to use DECLARATIONS. Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. 

In our example declarations will allow you to set what the date will be at the beginning of the statement, and that one date will be referenced when ever a date range is needed.

DECLARE @FromDos datetime,

        @ThruDOS datetime

SET @FromDos        = '01/01/2012' 

SET @ThruDOS        = '12/31/2012' 

So now, when a date is needed, you simply insert @FromDos and/or @ThruDOS.

Where [Service Date] between   @FromDos  and @ThruDOS               

Try it and see how it works for you.

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.