Avatar of sifugreg
Flag for United States of America asked on

need help with conditional select statements

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.

Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

case when len(whatever) = 18 then x
       when len(whatever) > 9 then y


and, e.g.
where len(whatever) = 18

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

I put some mock data into a temp @Table. This seemed to work:
DECLARE @MockTable TABLE (MedicalRecordNumber varchar(18))

SELECT 'IG2012123122013001' UNION ALL
SELECT 'IG2012233122013061' UNION ALL

SELECT  LEFT(MedicalRecordNumber, 2)    AS [Identifier],
        DATEADD(day, CONVERT(int, SUBSTRING(MedicalRecordNumber, 7, 3)),
                     CONVERT(datetime, '01/01/' + SUBSTRING(MedicalRecordNumber, 3, 4)))
                                        AS StartDate,
        SUBSTRING(MedicalRecordNumber, 10, 2)    AS [TermReason],
        DATEADD(day, CONVERT(int, SUBSTRING(MedicalRecordNumber, 16, 3)),
                     CONVERT(datetime, '01/01/' + SUBSTRING(MedicalRecordNumber, 12, 4)))
                                        AS EndDate
FROM    @MockTable
WHERE   LEFT(MedicalRecordNumber, 2) = 'IG'

Open in new window


I did not see the subsequent post. No objections here.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

with CTE as ( select 'IG2012123122013001' as medicalrecordnumber union all
              select 'IG2012123' union all
              select null
, TermDate
, datediff(day,ComenceDate,TermDate) as Duration
from (
          dateadd(day,cast(substring(medicalrecordnumber, 7,3) as int),cast(substring(medicalrecordnumber,3 ,4)+'0101' as date))
             as ComenceDate
        , case when len(medicalrecordnumber) = 18
            then dateadd(day,cast(substring(medicalrecordnumber,16,3) as int),cast(substring(medicalrecordnumber,12,4)+'0101' as date))
          end as TermDate
        from CTE
     ) as derived

Open in new window

I was working on this may as well have it

Self resolved