Link to home
Start Free TrialLog in
Avatar of sifugreg
sifugregFlag 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.

Avatar of PortletPaul
Flag of Australia image

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

and, e.g.
where len(whatever) = 18
Avatar of sifugreg
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
Avatar of sifugreg


Self resolved