The ADD.NET Life

I’ve been working on a system to handle Food Stamp Applications. In doing this, I needed to create a View to pivot data out of a row based architecture into a columnar format. One of the key pieces of information we are storing are SSNs. The system that stores the information strips the ‘-’ out of any SSN and adds them in using Masked Edit boxes later. However, in the SQL view, the numbers were displaying without hyphens, which can be a little more difficult to read. I created this function as a quick way of taking any varchar and returning a formatted Social Security Number. Hope this helps someone else out there.

Here is the code:

create function AsSSN (@input varchar(12))
returns char(11)
as
begin
declare @result as char(11)' + SUBSTRING(@input,6,4)

--Strip any non numerics
declare @pos INT
set @pos = PATINDEX('%[^0-9]%',@input)
while @pos > 0
begin
set @input = STUFF(@input,@pos,1,'')
set @pos = PATINDEX('%[^0-9]%',@input)
end

--Add hyphens for formatting
set @result = LEFT(@input,3) + '-' + SUBSTRING(@input,4,2) + '-

return @result
end

          

The first, of several (unfortunately), perfect storms began with a phone call from my manager. “Chris you need to come to my office right now.” I don’t know about you, but whenever I get a phone call like that, it’s never a very pleasant feeling.

I quickly saved what I was working on, said a quick prayer, and walked the green-mile to Debbie’s office. As I get there, my manager (the Assistant Director of IS) is sitting with our CFO with a grim look on her face. “Chris, can you explain what is wrong with our patient census and what we need to do to correct it.”

Our patient census is quiet possibly the most important report my department produces. Prior to me coming on board, the process took several hours and required 4 different servers to complete and deliver. Even given the unwieldy size, the data had been completely accurate…that is, up until the point our patient management system was upgraded from an AS400 based program to a Windows/SQL Server system that not only used a different file format, but the method of tracking information was completely different. We could easily put a census count from the AS400 system or the SQL system, but the problem came with the fact that we slowly implemented the new system across a two year period. This meant the census, that was already unwieldy, now required a hybridization process that combined the two systems into a single source of data.

Due to the drastic differences in how the systems worked and the inconsistencies in the new system rollout, the process became highly susceptible to data entry errors that are bound to occur in a system of this size. What started as a single patient here and there, would be multiplied and accumulated over time to the point of creating a painfully inaccurate report.

Well, we had been aware of this issue and had made the proper individuals aware of this information and had even formulated a plan. The plan was to scrap the old census system and replace it with a true SQL Data Warehouse with an OLAP Cube and SSRS + Web + Excel access to the business intelligence contained in the new warehouse. We had a 6 month plan and were in the process of getting this plan approved when the faithful day described above began.

“…what do we need to do to correct this?” you might think this would be an excellent opportunity to sell the new data warehouse project and it was. I went through and explained the benefits of developing a new system; I explained why it was unwise to attempt to repair the old data and sold the new “insights” system as best I could…and it worked, our CFO agreed to the project and asked for a timeline. Before I started to explain our six month plan, I was stopped with “we need correct numbers for the past two years by next week’s budget meetings.” My stomach dropped, as I started to explain how this wasn’t possible I got the Cpt. Kirk to Scotty look of all the “damnit Jim I’m giving her all she’s got” were not going to convince our CFO to change his mind on the deadline.

My heart sunk, my head was spinning and I’m sure I looked like a drunken donkey as the meeting continued. I really didn’t hear another word for the rest of the meeting as I was trying to determine just what needed to be done to cram a 6 month project into 2 weeks.

As soon as the CFO left, we met with our Applications Team to come up with a game plan. At the end of the meeting, we had developed a 5 and 5 plan. In the first 5 days, we would design, develop and deploy as complete a rough beta as we could possibly manage. During the last 5 days, we would test, correct and document the system so that at the end of the two week deadline we would be able to release a system that would meet our CFO’s requirements. It was a long shot, a hail mary and a near impossible task. The next few blog posts will describe the development of the 5 day Data Warehouse that a year and a half later is still in use and continues to generate the “I love this system” responses from our users.

I look forward to hearing your comments as I walk you through this process!