Nested Subquery (Max)

Database

Nested Subquery (Max)

I have my sub query running correctly, but I'm looking to providing the max(AdvBoardRank), which is dervived from a case statement in the following sub query:

JOIN (SELECT Patient_DW_ID,Procedure_Rank_Num, Procedure_Code,
CASE WHEN Procedure_Code IN ('011042','011045') THEN 'Debridement Subcutaneous'
WHEN Procedure_Code IN ('097597','097598') THEN 'Debridement Non-selective Wound'
WHEN Procedure_Code IN ('093922') THEN 'Doppler Aterial TCOM'
WHEN Procedure_Code IN ('0G0277') THEN 'HBO FULL BODY 30 MIN INT'
WHEN Procedure_Code IN ('097606', '097605') THEN 'Negative Pressure'
WHEN Procedure_Code IN ('099211', '099201') THEN 'OP Visit Level 1'
WHEN Procedure_Code IN ('099212', '099202') THEN 'OP Visit Level 2'
WHEN Procedure_Code IN ('099213', '099203') THEN 'OP Visit Level 3'
WHEN Procedure_Code IN ('099214', '099204') THEN 'OP Visit Level 4'
WHEN Procedure_Code IN ('099215', '099205') THEN 'OP Visit Level 5'
WHEN Procedure_Code IN ('097602') THEN 'Wound Care Non-selective Dressing Change'
--WHEN Procedure_Code IN ('099211','099201') THEN 'Suture/Staple Removal' --incorrect codes
WHEN Procedure_Code IN ('012001') THEN 'Repair/Closure of Wound'
WHEN Procedure_Code IN ('011100', '011101') THEN 'Biospy Skin Lesion'
WHEN Procedure_Code IN ('011200', '011201') THEN 'Removal of Skin Tag'
WHEN Procedure_Code IN ('011719') THEN 'Trim Nails'
WHEN Procedure_Code IN ('011720', '011721') THEN 'Debridement Nail'
WHEN Procedure_Code IN ('011730', '011732') THEN 'Removal of Nail Plate'
WHEN Procedure_Code IN ('016000') THEN 'Initial Tx of First Degree Burn'
WHEN Procedure_Code IN ('016020') THEN 'Dressing/Debride Part-Thick Burn'
WHEN Procedure_Code IN ('017250') THEN 'Chemical Cauterization Tissue'
WHEN Procedure_Code IN ('029580') THEN 'Application of Paste Boot'
WHEN Procedure_Code IN ('029581', '029582') THEN 'Apply Multilayer Compression Lower Leg'
WHEN Procedure_Code IN ('029700') THEN 'Removal/Revision of Cast'
WHEN Procedure_Code IN ('056420') THEN 'Drainage of Gland Abcess'
WHEN Procedure_Code IN ('010060') THEN 'Drainage of Skin Abcess'
WHEN Procedure_Code IN ('010080') THEN 'Drainage of Pilonidal Cyst'
WHEN Procedure_Code IN ('010140') THEN 'Drainage of Hematoma'
WHEN Procedure_Code IN ('010120') THEN 'Removal of Foreign Body'
WHEN Procedure_Code IN ('010160') THEN 'Puncture Darnage of Abcess, Hematoma, Bulla or Cyst'
WHEN Procedure_Code IN ('015002', '015003') THEN 'Surgical Preperation'
WHEN Procedure_Code IN ('015271', '015272') THEN 'Application of Skin Sub'
WHEN Procedure_Code IN ('0Q4104') THEN 'Integra BMWD Skin Sub'
WHEN Procedure_Code IN ('0Q4105') THEN 'Integra DRT Skin Sub'
WHEN Procedure_Code IN ('0Q4108') THEN 'Integra Matrix Skin Sub'
WHEN Procedure_Code IN ('0Q4114') THEN 'Integra Flowable Wound Matrix'
WHEN Procedure_Code IN ('0Q4107') THEN 'Graftjactet Skin Sub'
WHEN Procedure_Code IN ('0Q4113') THEN 'Graftjactet Express Allograf'
WHEN Procedure_Code IN ('0Q4110') THEN 'Primatrix Skin Sub'
WHEN Procedure_Code IN ('0Q4115') THEN 'Alloskin, Per Square CM'
WHEN Procedure_Code IN ('0Q4116') THEN 'Alloderm, Per Square CM'
END AS WoundCategory,

CASE WHEN Procedure_Code IN ('093922', '0G0277', '097602', '012001', '011719', '016000', '016020', '017250', '029580', '029700', '056420', '010060', '010080', '010140',
'010120', '010160', '0Q4104', '0Q4105', '0Q4108', '0Q4114', '0Q4107', '0Q4113', '0Q4110', '0Q4115', '0Q4116') THEN Procedure_Code END AS WoundCategoryCode,

CASE WHEN Procedure_Code IN ('011042','011045') THEN '1875'
WHEN Procedure_Code IN ('097597','097598','097602') THEN '1860'
WHEN Procedure_Code IN ('093922') THEN '1160'
WHEN Procedure_Code IN ('0G0277') THEN 'Ungroupable'
WHEN Procedure_Code IN ('097606', '097605') THEN '1895'
WHEN Procedure_Code IN ('099211', '099201','099212', '099202','099213', '099203','099214', '099204','099215', '099205') THEN '230'
--WHEN Procedure_Code IN ('099211','099201') THEN 'Suture/Staple Removal' --incorrect codes
WHEN Procedure_Code IN ('012001') THEN '1885'
WHEN Procedure_Code IN ('011100', '011101') THEN '3570'
WHEN Procedure_Code IN ('011200', '011201') THEN '2305'
WHEN Procedure_Code IN ('011719') THEN '1960'
WHEN Procedure_Code IN ('011720', '011721') THEN '1970'
WHEN Procedure_Code IN ('011730', '011732') THEN '1975'
WHEN Procedure_Code IN ('016000', '016020') THEN '2010'
WHEN Procedure_Code IN ('017250') THEN '2330'
WHEN Procedure_Code IN ('029580') THEN '1820'
WHEN Procedure_Code IN ('029581', '029582') THEN '1805'
WHEN Procedure_Code IN ('029700') THEN '3065'
WHEN Procedure_Code IN ('056420') THEN '3945'
WHEN Procedure_Code IN ('010060', '010080', '010140', '010160') THEN '2320'
WHEN Procedure_Code IN ('010120') THEN '2345'
WHEN Procedure_Code IN ('015002', '015003') THEN '3080'
WHEN Procedure_Code IN ('015271', '015272') THEN '3085'
WHEN Procedure_Code IN ('0Q4104') THEN 'Ungroupable'
WHEN Procedure_Code IN ('0Q4105', '0Q4108', '0Q4114', '0Q4107', '0Q4110', '0Q4115','0Q4116') THEN '2340'
WHEN Procedure_Code IN ('0Q4113') THEN '3135'
END AS AdvBoardWoundRank,

CASE WHEN Procedure_Code IN ('011042','011045') THEN 'Debridement - Skin'
WHEN Procedure_Code IN ('097597','097598','097602') THEN 'Active Wound Care Management'
WHEN Procedure_Code IN ('093922') THEN 'Vascular Ultrasound Study'
WHEN Procedure_Code IN ('0G0277') THEN 'Ungroupable'
WHEN Procedure_Code IN ('097606', '097605') THEN 'Negative Pressure Wound Therapy'
WHEN Procedure_Code IN ('099211', '099201','099212', '099202','099213', '099203','099214', '099204','099215', '099205') THEN 'Office/Outpatient Evaluation'
--WHEN Procedure_Code IN ('099211','099201') THEN 'Suture/Staple Removal' --incorrect codes
WHEN Procedure_Code IN ('012001') THEN 'Superficial Wound Repair'
WHEN Procedure_Code IN ('011100', '011101') THEN 'Biopsy - Skin'
WHEN Procedure_Code IN ('011200', '011201') THEN 'Skin Tag Removal'
WHEN Procedure_Code IN ('011719') THEN 'Nail Trimming'
WHEN Procedure_Code IN ('011720', '011721') THEN 'Debridement - Nail'
WHEN Procedure_Code IN ('011730', '011732') THEN 'Avulsion - Nail Plate'
WHEN Procedure_Code IN ('016000', '016020') THEN 'Burn Dressing/Debridement'
WHEN Procedure_Code IN ('017250') THEN 'Benign Lesion Destruction - Skin'
WHEN Procedure_Code IN ('029580') THEN 'Strapping Application - Foot'
WHEN Procedure_Code IN ('029581', '029582') THEN 'Strapping Application - Other'
WHEN Procedure_Code IN ('029700') THEN 'Cast Application/Removal - Body'
WHEN Procedure_Code IN ('056420') THEN 'Lesion Drainage - Vulva'
WHEN Procedure_Code IN ('010060', '010080', '010140', '010160') THEN 'Benign Lesion Incision/Drainage - Skin'
WHEN Procedure_Code IN ('010120') THEN 'Foreign Body Removal - Skin'
WHEN Procedure_Code IN ('015002', '015003') THEN 'Skin Graft Preparation'
WHEN Procedure_Code IN ('015271', '015272') THEN 'Other Grafts'
WHEN Procedure_Code IN ('0Q4104') THEN 'Ungroupable'
WHEN Procedure_Code IN ('0Q4105', '0Q4108', '0Q4114', '0Q4107', '0Q4110', '0Q4115','0Q4116') THEN 'Other Procedures - Skin'
WHEN Procedure_Code IN ('0Q4113') THEN 'Allograft - Skin'
END AS AdvBoardWoundCategory

FROM EDWPF_Views.Patient_Procedure
WHERE Procedure_Type_Code = '5'
AND Procedure_Code IN ('011042', '011045', '097598', '097597', '093922', '0G0277', '097606', '097605', '099211', '099201', '099212', '099202', '099213', '099203', '099214',
'099204', '099215', '099205', '097602', '099211', '099201', '012001', '011100', '011101', '011200', '011201', '011719', '011720', '011721', '011730',
'011732', '016000', '016020', '017250', '029580', '029581', '029582', '029700', '056420', '010060', '010080', '010140', '010120', '010160', '015002',
'015003', '015271', '015272', '0Q4104', '0Q4105', '0Q4108', '0Q4114', '0Q4107', '0Q4113', '0Q4110', '0Q4115', '0Q4116' )) PP
ON FP.Patient_DW_ID = PP.Patient_DW_ID

How would I go about getting the Max(AdvBoardWoundRank), when attempting to add it in the subquery I provides me the max per row, when I want the max per patient_dw_id

2 REPLIES

Re: Nested Subquery (Max)

So I've modified my query to the following, but I still can't get the MAX(AdvBoardWoundRank) to provide the MAX only.  Part of the reason is the rank is being created in a sub query case statement that doesn't exist in the database.  I've created a seperate sub query for it but if a patient had more than one procedure it will show two different ranks when I only want the MAX.

SELECT

TRIM(TRIM(TRAILING '.' FROM FP.Patient_DW_ID)) AS Patient_DW_ID,

FP.Coid,

PP.Procedure_Code,

PP.WoundCategory,

PP.WoundCategoryCode,

PP.Procedure_Rank_Num,

PP.AdvBoardWoundRank,

PP.AdvBoardWoundCategory

FROM EDWPF_VIEWS.Fact_Patient FP

JOIN EDWPF_VIEWS.Facility_Dimension FD ON FP.COID = FD.COID

JOIN EDWFS_Views.FACILITY F ON FD.COID = F.COID AND NOT F.Financial_JV_Code = 'JV' AND NOT F.Coid = '26627' /*Excludes a Legacy AR coid that was set up incorrectly. Added 4/8/2014 - JEH*/

JOIN EDWFS_Views.CHUNIT_Summary_Level CHS ON F.COID = CHS.COID

                                                                AND SUBSTRING(TRIM(CHS.Summary_Num), 1, 1) = 'F'

                                                                AND SUBSTRING(TRIM(CHS.Summary_Num), CHARACTER_LENGTH(TRIM(Summary_Num))-1, 2 ) = '20'

JOIN (SELECT PP.Patient_DW_ID, MAX(AdvBoardWoundRank) AS MaxAdvBoardWoundRank

            FROM (SELECT Patient_DW_ID,

            CASE WHEN Procedure_Code IN ('011042','011045') THEN '1875'  

                    WHEN Procedure_Code IN ('097597','097598','097602') THEN '1860'

                    WHEN Procedure_Code IN ('093922') THEN '1160'

                    WHEN Procedure_Code IN ('0G0277') THEN 'Ungroupable'

                    WHEN Procedure_Code IN ('097606', '097605') THEN '1895'

                       WHEN Procedure_Code IN ('099211', '099201','099212', '099202','099213', '099203','099214', '099204','099215', '099205') THEN '230'

                       --WHEN Procedure_Code IN ('099211','099201') THEN 'Suture/Staple Removal'    --incorrect codes

                    WHEN Procedure_Code IN ('012001') THEN '1885'

                    WHEN Procedure_Code IN ('011100', '011101') THEN '3570'

                    WHEN Procedure_Code IN ('011200', '011201') THEN '2305'

                    WHEN Procedure_Code IN ('011719') THEN '1960'

                    WHEN Procedure_Code IN ('011720', '011721') THEN '1970'

                    WHEN Procedure_Code IN ('011730', '011732') THEN '1975'

                    WHEN Procedure_Code IN ('016000', '016020') THEN '2010'

                    WHEN Procedure_Code IN ('017250') THEN '2330'

                    WHEN Procedure_Code IN ('029580') THEN '1820'

                    WHEN Procedure_Code IN ('029581', '029582') THEN '1805'

                    WHEN Procedure_Code IN ('029700') THEN '3065'

                    WHEN Procedure_Code IN ('056420') THEN '3945'

                    WHEN Procedure_Code IN ('010060', '010080', '010140', '010160') THEN '2320'

                    WHEN Procedure_Code IN ('010120') THEN '2345'

                    WHEN Procedure_Code IN ('015002', '015003') THEN '3080'

                    WHEN Procedure_Code IN ('015271', '015272') THEN '3085'

                    WHEN Procedure_Code IN ('0Q4104') THEN 'Ungroupable' 

                    WHEN Procedure_Code IN ('0Q4105', '0Q4108', '0Q4114', '0Q4107', '0Q4110', '0Q4115','0Q4116') THEN '2340'

                    WHEN Procedure_Code IN ('0Q4113') THEN '3135'         

                    END AS AdvBoardWoundRank

            FROM EDWPF_Views.Patient_Procedure

            WHERE Procedure_Type_Code = '5'

            AND Procedure_Code IN ('011042','011045','097598','097597','093922','0G0277','097606','097605','099211','099201','099212','099202','099213','099203','099214','099204','099215','099205','097602','099211','099201',

               '012001','011100','011101','011200','011201','011719','011720','011721','011730','011732','016000','016020','017250','029580','029581','029582','029700','056420','010060','010080','010140',

               '010120','010160','015002','015003','015271','015272','0Q4104','0Q4105','0Q4108','0Q4114','0Q4107','0Q4113','0Q4110','0Q4115','0Q4116' )) PP  

                                                GROUP BY 1) MPP ON FP.Patient_DW_ID = MPP.Patient_DW_ID

JOIN (SELECT Patient_DW_ID,Procedure_Rank_Num, Procedure_Code,

                    CASE WHEN Procedure_Code IN ('011042','011045') THEN 'Debridement Subcutaneous'  

                                WHEN Procedure_Code IN ('097597','097598') THEN 'Debridement Non-selective Wound'

                                WHEN Procedure_Code IN ('093922') THEN 'Doppler Aterial TCOM'

                                WHEN Procedure_Code IN ('0G0277') THEN 'HBO FULL BODY 30 MIN INT'

                                WHEN Procedure_Code IN ('097606', '097605') THEN 'Negative Pressure'

                                WHEN Procedure_Code IN ('099211', '099201') THEN 'OP Visit Level 1'

                                WHEN Procedure_Code IN ('099212', '099202') THEN 'OP Visit Level 2' 

                                WHEN Procedure_Code IN ('099213', '099203') THEN 'OP Visit Level 3'

                                WHEN Procedure_Code IN ('099214', '099204') THEN 'OP Visit Level 4'

                                WHEN Procedure_Code IN ('099215', '099205') THEN 'OP Visit Level 5'

                                WHEN Procedure_Code IN ('097602') THEN 'Wound Care Non-selective Dressing Change'

                                --WHEN Procedure_Code IN ('099211','099201') THEN 'Suture/Staple Removal'   --incorrect codes

                                WHEN Procedure_Code IN ('012001') THEN 'Repair/Closure of Wound'

                                WHEN Procedure_Code IN ('011100', '011101') THEN 'Biospy Skin Lesion'

                                WHEN Procedure_Code IN ('011200', '011201') THEN 'Removal of Skin Tag'

                                WHEN Procedure_Code IN ('011719') THEN 'Trim Nails'

                                WHEN Procedure_Code IN ('011720', '011721') THEN 'Debridement Nail'

                                WHEN Procedure_Code IN ('011730', '011732') THEN 'Removal of Nail Plate'

                                WHEN Procedure_Code IN ('016000') THEN 'Initial Tx of First Degree Burn'

                                WHEN Procedure_Code IN ('016020') THEN 'Dressing/Debride Part-Thick Burn'

                                WHEN Procedure_Code IN ('017250') THEN 'Chemical Cauterization Tissue'

                                WHEN Procedure_Code IN ('029580') THEN 'Application of Paste Boot'

                                WHEN Procedure_Code IN ('029581', '029582') THEN 'Apply Multilayer Compression Lower Leg'

                                WHEN Procedure_Code IN ('029700') THEN 'Removal/Revision of Cast'

                                WHEN Procedure_Code IN ('056420') THEN 'Drainage of Gland Abcess'

                                WHEN Procedure_Code IN ('010060') THEN 'Drainage of Skin Abcess'

                                WHEN Procedure_Code IN ('010080') THEN 'Drainage of Pilonidal Cyst'

                                WHEN Procedure_Code IN ('010140') THEN 'Drainage of Hematoma'

                                WHEN Procedure_Code IN ('010120') THEN 'Removal of Foreign Body'

                                WHEN Procedure_Code IN ('010160') THEN 'Puncture Darnage of Abcess, Hematoma, Bulla or Cyst'          

                                WHEN Procedure_Code IN ('015002', '015003') THEN 'Surgical Preperation'

                                WHEN Procedure_Code IN ('015271', '015272') THEN 'Application of Skin Sub' 

                                WHEN Procedure_Code IN ('0Q4104') THEN 'Integra BMWD Skin Sub'

                                WHEN Procedure_Code IN ('0Q4105') THEN 'Integra DRT Skin Sub'

                                WHEN Procedure_Code IN ('0Q4108') THEN 'Integra Matrix Skin Sub'

                                WHEN Procedure_Code IN ('0Q4114') THEN 'Integra Flowable Wound Matrix'

                                WHEN Procedure_Code IN ('0Q4107') THEN 'Graftjactet Skin Sub'

                                WHEN Procedure_Code IN ('0Q4113') THEN 'Graftjactet Express Allograf'

                                WHEN Procedure_Code IN ('0Q4110') THEN 'Primatrix Skin Sub'

                                WHEN Procedure_Code IN ('0Q4115') THEN 'Alloskin, Per Square CM'

                                WHEN Procedure_Code IN ('0Q4116') THEN 'Alloderm, Per Square CM'

                    END AS WoundCategory,

                    CASE WHEN Procedure_Code IN ('093922','0G0277','097602','012001','011719','016000','016020','017250','029580','029700','056420','010060','010080','010140','010120','010160','0Q4104','0Q4105','0Q4108','0Q4114','0Q4107','0Q4113',

                      '0Q4110','0Q4115','0Q4116') THEN Procedure_Code END AS WoundCategoryCode,

                    CASE WHEN Procedure_Code IN ('011042','011045') THEN '1875'  

                                WHEN Procedure_Code IN ('097597','097598','097602') THEN '1860'

                                WHEN Procedure_Code IN ('093922') THEN '1160'

                                WHEN Procedure_Code IN ('0G0277') THEN 'Ungroupable'

                                WHEN Procedure_Code IN ('097606', '097605') THEN '1895'

                                WHEN Procedure_Code IN ('099211', '099201','099212', '099202','099213', '099203','099214', '099204','099215', '099205') THEN '230'

                                --WHEN Procedure_Code IN ('099211','099201') THEN 'Suture/Staple Removal'    --incorrect codes

                                WHEN Procedure_Code IN ('012001') THEN '1885'

                                WHEN Procedure_Code IN ('011100', '011101') THEN '3570'

                                WHEN Procedure_Code IN ('011200', '011201') THEN '2305'

                                WHEN Procedure_Code IN ('011719') THEN '1960'

                                WHEN Procedure_Code IN ('011720', '011721') THEN '1970'

                                WHEN Procedure_Code IN ('011730', '011732') THEN '1975'

                                WHEN Procedure_Code IN ('016000', '016020') THEN '2010'

                                WHEN Procedure_Code IN ('017250') THEN '2330'

                                WHEN Procedure_Code IN ('029580') THEN '1820'

                                WHEN Procedure_Code IN ('029581', '029582') THEN '1805'

                                WHEN Procedure_Code IN ('029700') THEN '3065'

                                WHEN Procedure_Code IN ('056420') THEN '3945'

                                WHEN Procedure_Code IN ('010060', '010080', '010140', '010160') THEN '2320'

                                WHEN Procedure_Code IN ('010120') THEN '2345'

                                WHEN Procedure_Code IN ('015002', '015003') THEN '3080'

                                WHEN Procedure_Code IN ('015271', '015272') THEN '3085'

                                WHEN Procedure_Code IN ('0Q4104') THEN 'Ungroupable' 

                                WHEN Procedure_Code IN ('0Q4105', '0Q4108', '0Q4114', '0Q4107', '0Q4110', '0Q4115','0Q4116') THEN '2340'

                                WHEN Procedure_Code IN ('0Q4113') THEN '3135'         

                    END AS AdvBoardWoundRank,

                    CASE WHEN Procedure_Code IN ('011042','011045') THEN 'Debridement - Skin'  

                                WHEN Procedure_Code IN ('097597','097598','097602') THEN 'Active Wound Care Management'

                                WHEN Procedure_Code IN ('093922') THEN 'Vascular Ultrasound Study'

                                WHEN Procedure_Code IN ('0G0277') THEN 'Ungroupable'

                                WHEN Procedure_Code IN ('097606', '097605') THEN 'Negative Pressure Wound Therapy'

                                WHEN Procedure_Code IN ('099211', '099201','099212', '099202','099213', '099203','099214', '099204','099215', '099205') THEN 'Office/Outpatient Evaluation'

                                --WHEN Procedure_Code IN ('099211','099201') THEN 'Suture/Staple Removal'    --incorrect codes

                                WHEN Procedure_Code IN ('012001') THEN 'Superficial Wound Repair'

                                WHEN Procedure_Code IN ('011100', '011101') THEN 'Biopsy - Skin'

                                WHEN Procedure_Code IN ('011200', '011201') THEN 'Skin Tag Removal'

                                WHEN Procedure_Code IN ('011719') THEN 'Nail Trimming'

                                WHEN Procedure_Code IN ('011720', '011721') THEN 'Debridement - Nail'

                                WHEN Procedure_Code IN ('011730', '011732') THEN 'Avulsion - Nail Plate'

                                WHEN Procedure_Code IN ('016000', '016020') THEN 'Burn Dressing/Debridement'

                                WHEN Procedure_Code IN ('017250') THEN 'Benign Lesion Destruction - Skin'

                                WHEN Procedure_Code IN ('029580') THEN 'Strapping Application - Foot'

                                WHEN Procedure_Code IN ('029581', '029582') THEN 'Strapping Application - Other'

                                WHEN Procedure_Code IN ('029700') THEN 'Cast Application/Removal - Body'

                                WHEN Procedure_Code IN ('056420') THEN 'Lesion Drainage - Vulva'

                                WHEN Procedure_Code IN ('010060', '010080', '010140', '010160') THEN 'Benign Lesion Incision/Drainage - Skin'

                                WHEN Procedure_Code IN ('010120') THEN 'Foreign Body Removal - Skin'

                                WHEN Procedure_Code IN ('015002', '015003') THEN 'Skin Graft Preparation'

                                WHEN Procedure_Code IN ('015271', '015272') THEN 'Other Grafts'

                                WHEN Procedure_Code IN ('0Q4104') THEN 'Ungroupable' 

                                WHEN Procedure_Code IN ('0Q4105', '0Q4108', '0Q4114', '0Q4107', '0Q4110', '0Q4115','0Q4116') THEN 'Other Procedures - Skin'

                                WHEN Procedure_Code IN ('0Q4113') THEN 'Allograft - Skin'         

                     END AS AdvBoardWoundCategory                                              

           FROM EDWPF_Views.Patient_Procedure

           WHERE Procedure_Type_Code = '5'

           AND Procedure_Code IN ('011042','011045','097598','097597','093922','0G0277','097606','097605','099211','099201','099212','099202','099213','099203','099214','099204','099215','099205','097602','099211','099201',

               '012001','011100','011101','011200','011201','011719','011720','011721','011730','011732','016000','016020','017250','029580','029581','029582','029700','056420','010060','010080','010140',

               '010120','010160','015002','015003','015271','015272','0Q4104','0Q4105','0Q4108','0Q4114','0Q4107','0Q4113','0Q4110','0Q4115','0Q4116' )) PP  

           ON FP.Patient_DW_ID = PP.Patient_DW_ID AND MPP.MaxAdvBoardWoundRank = PP.AdvBoardWoundRank

WHERE F.Company_Code = 'H'

AND F.COID = '32819'

AND F.LOB_Code IN ('HOS','OTH')

AND FP.Admission_Date BETWEEN '2014-01-01' AND '2014-12-31'

AND FP.Total_Billed_Charges > 0

/*AND FP.Final_Bill_Date <> '0001-01-01'*/

AND FP.Patient_Type_Code_Pos1 IN ('I','E','O','S')

AND NOT FP.Admission_Patient_Type_Code LIKE '%P%'

ORDER BY 1,4;

Enthusiast

Re: Nested Subquery (Max)

Why dont u try using MAX in your outer query instead of subquery?