How to find the MAX value in my query

Question

I am using SQL Server 2008R2 and I am having difficulties on how to select the maximum value (0 or 1) for each variable getting created in the CASE statement (Ex, TpH, TpL, TpK, etc.). Below is my query:

Select Person_Key
      ,Age
      ,Transplant_Dt
      ,Gender
      ,Race
      ,OriginalRace
      ,CountyCode
      ,DX
      ,DOD
      ,RecordType
      ,PlanType
      ,TpH
      ,TpK
      ,TpU
      ,TpH
      ,TpP
      ,TpM
      ,DM
      ,HTN
      ,HLP
      ,RenD
      ,CVD
-- populate table
-- into Patients
from
       (

        Select *,  ROW_NUMBER() over (partition by Person_Key order by Transplant_Dt ASC) as RowNumber
         From
           ( Select *,
              CASE
                WHEN TpL + TpK + TpU + TpH + TpP > 1 then 1
                ELSE 0
              END As TpM            
          From
                (
                  SELECT t.* ,
                    CASE
                      WHEN dx.INV_DDTL_DIAG_CD = 'V427' then 1
                      ELSE 0
                    END As TpL ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V420' then 1
                      ELSE 0
                    END As TpK ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V426' then 1
                      ELSE 0
                    END As TpU ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V421' then 1
                      ELSE 0
                    END As TpH ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V4283' then 1
                      ELSE 0
                    END As TpP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '250%' then 1
                      ELSE 0
                    END As DM ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '401%' then 1
                      ELSE 0
                    END As HTN ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '272%' then 1
                      ELSE 0
                    END As HLP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '584%' or dx.INV_DDTL_DIAG_CD like '585%' or dx.INV_DDTL_DIAG_CD like '586%' then 1
                      ELSE 0
                    END As RenD ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD BETWEEN '390' AND '45999' then 1
                      ELSE 0
                    END As CVD
                  FROM Transplant_Patients t
                  INNER JOIN FFS_INSTNL_CLAIMS_HDR instnl
                     ON instnl.FFS_IHDR_PERSON_KEY = t.PERSON_KEY
                        AND instnl.FFS_IHDR_FACILTY_TP_CD = '1'    /* Hospital Stay */
                        AND instnl.SERVICE_BEGIN_DATE BETWEEN '01-JUL-2010' AND '30-JUN-2011'
                  INNER JOIN INV_DIAG_COND_OCRNC_DET dx
                     ON dx.INV_DDTL_INVOICE_ID = instnl.FFS_IHDR_INVOICE_ID
                        AND dx.INV_DDTL_INV_CLASS_CD = instnl.FFS_IHDR_INV_CLASS_CD

                  UNION

                  SELECT t.* ,
                    CASE
                      WHEN dx.INV_DDTL_DIAG_CD = 'V427' then 1
                      ELSE 0
                    END As TpL ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V420' then 1
                      ELSE 0
                    END As TpK ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V426' then 1
                      ELSE 0
                    END As TpU ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V421' then 1
                      ELSE 0
                    END As TpH ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V4283' then 1
                      ELSE 0
                    END As TpP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '250%' then 1
                      ELSE 0
                    END As DM ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '401%' then 1
                      ELSE 0
                    END As HTN ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '272%' then 1
                      ELSE 0
                    END As HLP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '584%' or dx.INV_DDTL_DIAG_CD like '585%' or dx.INV_DDTL_DIAG_CD like '586%' then 1
                      ELSE 0
                    END As RenD ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD BETWEEN '390' AND '45999' then 1
                      ELSE 0
                    END As CVD
                  FROM Transplant_Patients t
                  INNER JOIN MC_INSTNL_CLAIMS_HDR instnl
                     ON instnl.MC_IHDR_PERSON_KEY = t.PERSON_KEY
                        AND instnl.MC_IHDR_FACILTY_TP_CD = '1'    /* Hospital Stay */
                        AND instnl.SERVICE_BEGIN_DATE BETWEEN '01-JUL-2010' AND '30-JUN-2011'
                  INNER JOIN INV_DIAG_COND_OCRNC_DET dx
                     ON dx.INV_DDTL_INVOICE_ID = instnl.MC_IHDR_INVOICE_ID                
                )  nq_diag           
           ) nq_dxfinal
       ) Diag_RowNumber 
where RowNumber = '1'

I need the maximum value for all the CASE variables that are being created and am getting stuck. The MAX value will either be a one (1) or a zero(0).

Can anybody help this newbie solve my problem?

Thanks.

Dave

Answer 1

>I need the maximum value for all the CASE variables that are being created and am getting stuck. The MAX value will either be a one (1) or a zero(0).

Put the SELECT statement with the CASE expression only into a CTE, and SELECT the MAX in an outer CTE (CTE nesting):

http://www.sqlusa.com/bestpractices2005/nestedcte/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: SQL Server 2012 Pro - Programming, Design & Business Intelligence

Answer 2

Could it be as simple as

Select Person_Key
      ,Age
      ,Transplant_Dt
      ,Gender
      ,Race
      ,OriginalRace
      ,CountyCode
      ,DX
      ,DOD
      ,RecordType
      ,PlanType
      ,MAX(TpH)
      ,MAX(TpK)
      ,MAX(TpU)
      ,MAX(TpH)
      ,MAX(TpP)
      ,MAX(TpM)
      ,MAX(DM)
      ,MAX(HTN)
      ,MAX(HLP)
      ,MAX(RenD)
      ,MAX(CVD)
FROM ...
GROUP BY Person_Key, Age, Transplant_Dt, Gender, Race, OriginalRace,
        CountyCode, DX, DOD, RecordType, PlanType

Or is there something more to it? Your question is not entirely clear.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Kalman,

I am having difficulty coding your solution based on your link.

Would you please give an example using my query?

Thanks.

Dave

Answer 4

Erland,

Your response does not give the desired results. There will be many rows per person key with either a 1 or 0; I am selecting the first row as I don't need duplicates and I need the maximum value for each CASE variable. I believe your solution will only give me the max based on rownumber=1 and I need MAX over the entire person key history and not just their first row number.

Does that clarify what I need?

Thanks.

Dave

Answer 5

Are you getting wrong result with your current query? 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Yes, I am only getting the values from the first row and not the maximum per person key. I can verify this by selecting only one person and manually examine the results and see CASE variables that contain on one (1) but the final select statement brings back a zero because the row with a row is not the first row.

Dave

Answer 7

Strange, your ROW_NUMBER() clearly has partition by Person_Key so you're supposed to get one row per each person. BTW, use 1, not '1' when comparing with ROW_NUMBER() as row_number() returns an integer value.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

I do get one row per person, but I need to get the MAX value for each CASE variable and if the MAX value occurs on row 145, I will not get that value, I will only get the values that are on the first row.

For example, person=123 returns 200 rows and some rows will have a zero (0) for say value TpL; row 15 will have a value of 1 for TpL but since row 1 has a zero that is what is returned. FYI, 1=Yes 0=No. So I need to find out if a patient (person) has ever had this condition (marked by a value=1).

Thanks for the tip about changing '1' to 1.

Does that make my issue any clearer?

Thanks again for responding.

Dave

Answer 9

Your condition based on row_number says "give me the first row for every patient", and does not match very well with So I need to find out if a patient (person) has ever had this condition (marked by a value=1).

So maybe you should just take out the row_number from the equation?

Select Person_Key
      ,Age
      ,Transplant_Dt
      ,Gender
      ,Race
      ,OriginalRace
      ,CountyCode
      ,DX
      ,DOD
      ,RecordType
      ,PlanType
      ,MAX(TpH)
      ,MAX(TpK)
      ,MAX(TpU)
      ,MAX(TpH)
      ,MAX(TpP)
      ,MAX(TpM)
      ,MAX(DM)
      ,MAX(HTN)
      ,MAX(HLP)
      ,MAX(RenD)
      ,MAX(CVD)
-- populate table
-- into Patients
from
           ( Select ,
              CASE
                WHEN TpL + TpK + TpU + TpH + TpP > 1 then 1
                ELSE 0
              END As TpM            
          From
                (
                  SELECT t. ,
                    CASE
                      WHEN dx.INV_DDTL_DIAG_CD = 'V427' then 1
                      ELSE 0
                    END As TpL ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V420' then 1
                      ELSE 0
                    END As TpK ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V426' then 1
                      ELSE 0
                    END As TpU ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V421' then 1
                      ELSE 0
                    END As TpH ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V4283' then 1
                      ELSE 0
                    END As TpP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '250%' then 1
                      ELSE 0
                    END As DM ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '401%' then 1
                      ELSE 0
                    END As HTN ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '272%' then 1
                      ELSE 0
                    END As HLP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '584%' or dx.INV_DDTL_DIAG_CD like '585%' or dx.INV_DDTL_DIAG_CD like '586%' then 1
                      ELSE 0
                    END As RenD ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD BETWEEN '390' AND '45999' then 1
                      ELSE 0
                    END As CVD
                  FROM Transplant_Patients t
                  INNER JOIN FFS_INSTNL_CLAIMS_HDR instnl
                     ON instnl.FFS_IHDR_PERSON_KEY = t.PERSON_KEY
                        AND instnl.FFS_IHDR_FACILTY_TP_CD = '1'    * Hospital Stay *
                        AND instnl.SERVICE_BEGIN_DATE BETWEEN '01-JUL-2010' AND '30-JUN-2011'
                  INNER JOIN INV_DIAG_COND_OCRNC_DET dx
                     ON dx.INV_DDTL_INVOICE_ID = instnl.FFS_IHDR_INVOICE_ID
                        AND dx.INV_DDTL_INV_CLASS_CD = instnl.FFS_IHDR_INV_CLASS_CD

                  UNION

                  SELECT t.* ,
                    CASE
                      WHEN dx.INV_DDTL_DIAG_CD = 'V427' then 1
                      ELSE 0
                    END As TpL ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V420' then 1
                      ELSE 0
                    END As TpK ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V426' then 1
                      ELSE 0
                    END As TpU ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V421' then 1
                      ELSE 0
                    END As TpH ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD = 'V4283' then 1
                      ELSE 0
                    END As TpP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '250%' then 1
                      ELSE 0
                    END As DM ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '401%' then 1
                      ELSE 0
                    END As HTN ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '272%' then 1
                      ELSE 0
                    END As HLP ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD like '584%' or dx.INV_DDTL_DIAG_CD like '585%' or dx.INV_DDTL_DIAG_CD like '586%' then 1
                      ELSE 0
                    END As RenD ,
                    CASE  WHEN dx.INV_DDTL_DIAG_CD BETWEEN '390' AND '45999' then 1
                      ELSE 0
                    END As CVD
                  FROM Transplant_Patients t
                  INNER JOIN MC_INSTNL_CLAIMS_HDR instnl
                     ON instnl.MC_IHDR_PERSON_KEY = t.PERSON_KEY
                        AND instnl.MC_IHDR_FACILTY_TP_CD = '1'    * Hospital Stay *
                        AND instnl.SERVICE_BEGIN_DATE BETWEEN '01-JUL-2010' AND '30-JUN-2011'
                  INNER JOIN INV_DIAG_COND_OCRNC_DET dx
                     ON dx.INV_DDTL_INVOICE_ID = instnl.MC_IHDR_INVOICE_ID                
                )  nq_diag           
           ) nq_dxfinal
GROUP BY Person_Key, Age, Transplant_Dt, Gender, Race, OriginalRace,
         CountyCode, DX, DOD, RecordType, PlanType

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

If you're looking for this, then your order by should be

partition by Person_Key order by TpL DESC, TpL1 DESC, ....

In other words, it will pick up the row with 1 in the first TpL and among such rows the row with TpL1 = 1, etc. if you use this ORDER BY.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Thanks everybody for your help and especially Erland for his solution.

Dave