Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
I have a following script which returns all the records with the max NPI number. The problem is if there is no NPI number, I still want the T-SQL script to return one row with the doctor's information. here is my T_SQL script:SELECT A.NPI AS NPI,A.DR_FULL_NAME AS DR, A.ADDRESS_1 AS ADDRESS,A.CITY AS CITY, A.ZIP AS ZIPFROM OGEN.GEN_M_DOCTOR_MAST AINNER JOIN(SELECT MAX(NPI) AS LATEST_NPI,DR_FULL_NAME,ADDRESS_1,CITY,ZIPFROM OGEN.GEN_M_DOCTOR_MAST GROUP BY DR_FULL_NAME,ADDRESS_1,CITY,ZIP) ABCON A.NPI = ABC.LATEST_NPIWHERE A.NPI IS NULLORDER BY A.DR_FULL_NAMEPlease suggest what I need to change or add to this script to also pick up a NULL or empty NPI.Thank you.
ahmeds08
Aged Yak Warrior
737 Posts
Posted - 2013-04-30 : 11:34:22
replace MAX(NPI) with isnull(MAX(NPI),0).this will display the null value as 0mohammad.javeed.ahmed@gmail.com
stevenandler
Starting Member
42 Posts
Posted - 2013-04-30 : 11:48:04
Tried this but still doesn't work. I'm using SQL Server 2008 R2.Any other suggestions?
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-04-30 : 11:50:08
You would also need to do that on the join condition
ON ISNULL(A.NPI,0) = ABC.LATEST_NPI
Another alternative is this:
SELECT NPI, DR, ADDRESS, CITY, ZIPFROM (SELECT A.NPI AS NPI , A.DR_FULL_NAME AS DR , A.ADDRESS_1 AS ADDRESS , A.CITY AS CITY , A.ZIP AS ZIP , ROW_NUMBER() OVER ( PARTITION BY DR_FULL_NAME, ADDRESS_1, CITY, ZIP ORDER BY NPI DESC) AS RNFROM OGEN.GEN_M_DOCTOR_MAST A) S WHERE RN=1;