Fairly straightforward IF question

  • Brad Allison

    Hall of Fame

    Points: 3473

    I do not do too much in the way of creating SQL code so please bear with this 20 year+ nube 🙂
    I have a simple stored procedure that is pulling data for an SSIS job that delivers a pipe-delimited .dat file to an FTP site.  There are two fields that are being pulled from a catalog table that uses foreign keys.  It is a license-type table that holds various types of licenses and the ones I am looking for are the State license (NPDBSTATEx) and the Federal license (NPDBDEAxxx).  Can I use an IF statement to say IF licensetype='NPDBSTATEx' then StateLicense=licensetype?  What would be the best way of doing this?   I need to produce only ONE record per provider and right now, here is the SQL that I am using.  By the way, this project right now is in the test phase and is not going into production until proven.

    Thanks so much for the useful insight and information
    Select Distinct
        p.ID as IntProviderID
       ,p.LastName
       ,p.FirstName
       ,p.MiddleName
       ,ISNULL(REPLACE(CONVERT (CHAR(10),p.DateOfBirth,101),'/',''),'01011901') as DateOfBirth
       ,CASE p.Sex WHEN '' THEN 'U' ELSE p.Sex END as Sex
       ,a.AddressLine1 as PrimaryAddress1
       ,a.AddressLine2 as PrimaryAddress2
       ,a.City
       ,a.[State]
       ,a.zipcode
       ,a.PHONE
       ,a.FAX
       ,'' as SecondaryAddress1
       ,'' as SecondaryAddress2
       ,'' as SecondaryCity
       ,'' as SecondaryState
       ,'' as SecondaryZIP
       ,'' as SecondaryPhone
       ,'' as SecondaryFax
       ,ref.[DESCRIPTION] as PrimarySpecialty
       ,'' as SecondarySpecialty
       ,l.LicenseNumber as DEA
       ,REPLACE(CONVERT (CHAR(10),l.ExpirationDate ,101),'/','')  as LicExpDate
       ,'' as StateLic
       ,'' as StateExpDate
       ,p.Upin
       ,p.Npi
       ,CASE p.Active WHEN 1 THEN 'Y' ELSE 'N' END as [Status]
       ,'' as InactiveDate
       ,p.DisplayDegrees_Short as ProvType
       ,'' as ProvTypeDesc

    FROM VisualCACTUS.Providers p
    Join VisualCACTUS.ProviderAddresses ak on p.Provider_K=ak.Provider_K And ak.Active=1
    Join VisualCACTUS.ADDRESSES a on ak.Address_K=a.ADDRESS_K and a.ACTIVE=1
    Join VisualCACTUS.ProviderSpecialties s on p.Provider_K=s.Provider_K
    join VisualCACTUS.ProviderLicenses l on p.Provider_K=l.Provider_K
    Join VisualCACTUS.REFTABLE ref on s.Specialty_RTK=ref.REFTABLE_K
    where ak.AddressType_RTK='NPDBPRIMAR' and (a.state<>'ID' and a.state<>'MN') and (l.License_RTK='NPDBSTATEx' or l.License_RTK='NPDBDEAxxx') and s.SpecialtyType_RTK='C4DJ0TXXIP' and p.Active=1
    Order by LastName 
  • sgmunson

    SSC Guru

    Points: 110440

    Here;s one way to handle it:
    SELECT DISTINCT
      p.ID AS IntProviderID
     ,p.LastName
     ,p.FirstName
     ,p.MiddleName
     ,ISNULL(REPLACE(CONVERT(char(10), p.DateOfBirth, 101), '/', ''), '01011901') AS DateOfBirth
     ,CASE p.Sex WHEN '' THEN 'U' ELSE p.Sex END AS Sex
     ,a.AddressLine1 AS PrimaryAddress1
     ,a.AddressLine2 AS PrimaryAddress2
     ,a.City
     ,a.[State]
     ,a.zipcode
     ,a.PHONE
     ,a.FAX
     ,'' AS SecondaryAddress1
     ,'' AS SecondaryAddress2
     ,'' AS SecondaryCity
     ,'' AS SecondaryState
     ,'' AS SecondaryZIP
     ,'' AS SecondaryPhone
     ,'' AS SecondaryFax
     ,ref.[DESCRIPTION] AS PrimarySpecialty
     ,'' AS SecondarySpecialty
     ,l.LicenseNumber AS DEA
     ,REPLACE(CONVERT (CHAR(10),l.ExpirationDate ,101),'/','') AS LicExpDate
     ,'' AS StateLic
     ,'' AS StateExpDate
     ,p.Upin
     ,p.Npi
     ,CASE p.Active WHEN 1 THEN 'Y' ELSE 'N' END AS [Status]
     ,'' AS InactiveDate
     ,p.DisplayDegrees_Short AS ProvType
     ,'' AS ProvTypeDesc
     ,NULLIF(l.License_RTK, 'NPDBDEAxxx') AS StateLicense
    FROM VisualCACTUS.Providers AS p
    INNER JOIN VisualCACTUS.ProviderAddresses AS ak
        ON p.Provider_K = ak.Provider_K
        AND ak.Active = 1
    INNER JOIN VisualCACTUS.ADDRESSES AS a
        ON ak.Address_K = a.ADDRESS_K
        AND a.ACTIVE = 1
    INNER JOIN VisualCACTUS.ProviderSpecialties AS s
        ON p.Provider_K = s.Provider_K
    INNER JOIN VisualCACTUS.ProviderLicenses AS l
        ON p.Provider_K = l.Provider_K
    INNER JOIN VisualCACTUS.REFTABLE AS ref
        ON s.Specialty_RTK = ref.REFTABLE_K
    WHERE ak.AddressType_RTK = 'NPDBPRIMAR'
        AND a.[state] NOT IN ('ID', 'MN')
        AND l.License_RTK IN ('NPDBSTATEx', 'NPDBDEAxxx')
        AND s.SpecialtyType_RTK = 'C4DJ0TXXIP'
        AND p.Active = 1
    ORDER BY LastName;

    Note that I coded this on the assumption that only 2 license types are possible, and I just NULL out the value for StateLicense if the value for a Federal license type occurs.   As it's the only value other than the State license type, this makes sense.   If that changes, then you'll be better off with a CASE statement.   The NULLIF function just nulls out the value only when it's 1st parameter is equal to the 2nd parameter, otherwise it provides the 1st parameter.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • below86

    SSChampion

    Points: 11254

    Not real sure what you are asking for.  Are ou wanting to set a field in your select statement for licensetype?  You could use a CASE statement for that.  Or are you talking about checking for this value in the where statement?

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us

  • Brad Allison

    Hall of Fame

    Points: 3473

    I am joining the License table based on Provider ID and the relationship is a one to many, however based on the license type (and for now there are two - State / Federal DEA) I only want to return the ONE record with the StateLic and DEA fields to return their respective data, if that makes sense.  The NULLIF is returning the license type, but not the actual license number that I need.  I am thinking a CASE statement might be better, right?

  • below86

    SSChampion

    Points: 11254

    Still not clear to me what you are after.

    If this is a one to many:
    INNER JOIN VisualCACTUS.ProviderLicenses AS l
      ON p.Provider_K = l.Provider_K

    Are you only wanting the 'state' license from this table?  Is there a field on the table that you can use to filter to that one record? If so, include it in the join.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us

  • Brad Allison

    Hall of Fame

    Points: 3473

    Okay, so if I have a list of 500 providers and let's take one, like Provider A.  When I run the query, Provider A has TWO records because there is a license for State and a difference license for Federal.  I want to merge those two into one record where the data from DEA will go into that DEALicense and State will go into the StateLic field.  And if there isn't a record then that field would just be blank. For instance this needs to be just one record:

    IntProviderID LastName FirstName MiddleName DateOfBirth Sex PrimaryAddress1 PrimaryAddress2 City State zipcode PHONE FAX DEA LicExpDate StateLic StateExpDate Upin
    209 A Provider                      10241973 U 123 Main                                                    Anytown ST 99999 5551234567 NP9999999 6302020
            
    209 A Provider                      10241973 U 123 Main                                                    Anytown ST 99999 5551234567

    123456789 1312019         
  • below86

    SSChampion

    Points: 11254

    Makes more sense now.  So you want columns returned for the 'Fed' values and columns returned for the 'state' if they exist.

    You could set up multiple joins to this table.  See if something like this works.
    SELECT ...

        lFed.DEA,
        lState.StateLic
    FROM ....
    ....
    INNER JOIN VisualCACTUS.ProviderLicenses AS lFed
    ON p.Provider_K = lFed.Provider_K
    AND lFed.DEA IS NOT NULL -- if it is blank and not null use <> ''
    INNER JOIN VisualCACTUS.ProviderLicenses AS lState
    ON p.Provider_K = lState.Provider_K
    AND lState.StateLic IS NOT NULL

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us

  • Brad Allison

    Hall of Fame

    Points: 3473

    Thanks so much!  That worked.  I have been working with SQL for nearly 20 years, but again, I don't author SQL scripts that much.

  • andycadley

    SSCertifiable

    Points: 5291

    I suspect you'll actually want them to be LEFT joins, otherwise you'll eliminate rows that don't have both types of license, i.e.


    SELECT ...
    IsNull(IFed.DEA, ''), -- If you want to eliminate NULLs and just get empty strings

        lState.StateLic
    FROM ....
    ....
    LEFT JOIN VisualCACTUS.ProviderLicenses AS lFed
      ON p.Provider_K = lFed.Provider_K
      AND lFed.DEA IS NOT NULL -- if it is blank and not null use  <> ''
    LEFT JOIN VisualCACTUS.ProviderLicenses AS lState
      ON p.Provider_K = lState.Provider_K
      AND lState.StateLic IS NOT NULL
    [/code]

  • Brad Allison

    Hall of Fame

    Points: 3473

    Thanks the for the information about LEFT join.  I modified the query and it did populate with approximately 50 extra records.

  • gvoshol 73146

    Hall of Fame

    Points: 3100

    Brad Allison - Tuesday, June 5, 2018 10:31 AM

    Thanks so much!  That worked.  I have been working with SQL for nearly 20 years, but again, I don't author SQL scripts that much.

    Given the data structures we get from Cactus, don't put down your SQL skills.  It takes a lot of work to get the data you want from those tables.

  • Brad Allison

    Hall of Fame

    Points: 3473

    Yeah, Symplr sent me the data table dictionary and it is pretty massive.  We need some licensing reports for our Pharmacy that we are running using the Cactus DB.  I just could not get my head around the proper joins, etc. and I thank those here that helped.

  • below86

    SSChampion

    Points: 11254

    andycadley - Wednesday, June 6, 2018 2:48 AM

    I suspect you'll actually want them to be LEFT joins, otherwise you'll eliminate rows that don't have both types of license, i.e.


    SELECT ...
    IsNull(IFed.DEA, ''), -- If you want to eliminate NULLs and just get empty strings

        lState.StateLic
    FROM ....
    ....
    LEFT JOIN VisualCACTUS.ProviderLicenses AS lFed
      ON p.Provider_K = lFed.Provider_K
      AND lFed.DEA IS NOT NULL -- if it is blank and not null use  <> ''
    LEFT JOIN VisualCACTUS.ProviderLicenses AS lState
      ON p.Provider_K = lState.Provider_K
      AND lState.StateLic IS NOT NULL
    [/code]

    Thanks for catching that, trying to go to fast.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply