SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Retrieving a NULL when using the Max Aggregate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevenandler
Starting Member

USA
42 Posts

Posted - 04/30/2013 :  11:18:23  Show Profile  Reply with Quote
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 ZIP
FROM OGEN.GEN_M_DOCTOR_MAST A
INNER JOIN
(
SELECT MAX(NPI) AS LATEST_NPI,DR_FULL_NAME,ADDRESS_1,CITY,ZIP
FROM OGEN.GEN_M_DOCTOR_MAST
GROUP BY DR_FULL_NAME,ADDRESS_1,CITY,ZIP
) ABC
ON A.NPI = ABC.LATEST_NPI
WHERE A.NPI IS NULL
ORDER BY A.DR_FULL_NAME


Please 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

India
534 Posts

Posted - 04/30/2013 :  11:34:22  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
replace MAX(NPI) with isnull(MAX(NPI),0).
this will display the null value as 0

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

stevenandler
Starting Member

USA
42 Posts

Posted - 04/30/2013 :  11:48:04  Show Profile  Reply with Quote
Tried this but still doesn't work. I'm using SQL Server 2008 R2.
Any other suggestions?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 04/30/2013 :  11:50:08  Show Profile  Reply with Quote
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, ZIP
FROM (
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 RN
FROM    OGEN.GEN_M_DOCTOR_MAST A
) S WHERE RN=1;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000