Fairly straightforward IF question

Question

Fairly straightforward IF question

Brad Allison

Hall of Fame

Points: 3550
More actions
June 5, 2018 at 7:24 am

#361675

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

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

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

below86 SSChampion Points: 11532 More actions · Answer 2

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
Don't fear failure, fear regret.

Brad Allison Hall of Fame Points: 3550 More actions · Answer 3

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: 11532 More actions · Answer 4

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
Don't fear failure, fear regret.

Brad Allison Hall of Fame Points: 3550 More actions · Answer 5

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: 11532 More actions · Answer 6

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
Don't fear failure, fear regret.

Brad Allison Hall of Fame Points: 3550 More actions · Answer 7

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: 5296 More actions · Answer 8

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: 3550 More actions · Answer 9

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

GaryV Hall of Fame Points: 3307 More actions · Answer 10

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: 3550 More actions · Answer 11

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: 11532 More actions · Answer 12

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
Don't fear failure, fear regret.