troubleshooting Question

need help with conditional select statements

Avatar of sifugreg
sifugregFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
6 Comments1 Solution435 ViewsLast Modified:
I need a serious kick in the brain here.  I've worked with Oracle SQL for years and fairly new  to MSSQL and I'm having some serious conceptual issues.  Today's is:

I am using a substring to pull a value I am then converting it to a DATE/TIME.  As long as data exists, it works fine.  When the field is NULL, it reuturns an error that  the conversion can't work as data is our of range.  This makes sense.  So I need to do a conditional select.  Something akin to the logic of  If the length of the substring is 2 then perform this conversion otherwise just use nothing.  I've tried a plethora of IF THEN and CASE WHEN logic but cant seem to tackle this one.  How is it done?  the select line looks like this:

,convert(varchar,DATEADD(day, CAST(substring(medicalrecordnumber,12,3) AS int) - 1, CONVERT(datetime, substring(medicalrecordnumber,10,2) + '0101', 12)),101) TermDate

Realizing that a very low number of people will have a value in that field.  The number it is pulling is a value that looks something like this IG2012123122013001.  Now broken down it means
IG                 2012               123                          12                   2013               001
Identifier   Start Year  Julian Start Day   Termination Reason   Term Year   Julian End Day

Active patients will not have any of the last 9 digits and will have number like IG2012123
Only when they terminate servcie do they get the remaining 9 digits.

I want to create an SQL select statement that can say if the length of the record is 18 then go ahead and pull this info otherwise ignore it.

Help??
ASKER CERTIFIED SOLUTION
sifugreg

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros