Taking the latest date from a group of values(solved)

Transact-SQL
1

Hi Everyone,
I am getting duplicates when pulling a customers weight and height
I am getting 2 weight values for a user code
ex; pounds AND pounds & ounces

I am trying to pull the latest weight and height number from MOD date

SELECT DISTINCT PB.CUSTOMER_CODE,

CASE
WHEN DD.UNIT_OF_MEASURE_NAME ='Inches' OR
DD.UNIT_OF_MEASURE_NAME ='Feet & Inches' OR
DD.UNIT_OF_MEASURE_NAME ='Centimeters' THEN MAX(PD.DD_MOD_DATE)

WHEN DD.UNIT_OF_MEASURE_NAME ='Pounds' OR
DD.UNIT_OF_MEASURE_NAME ='Ounces' OR
DD.UNIT_OF_MEASURE_NAME ='Kilograms' OR
DD.UNIT_OF_MEASURE_NAME ='Pounds & Ounces' THEN MAX(PD.DD_MOD_DATE)
END AS UPDATED,

CASE WHEN DD.UNIT_OF_MEASURE_NAME = 'Feet & Inches' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS VARCHAR)+'.' + CAST(FLOOR(CAST(ROUND(PD.DISCRETE_DATA_VALUE %12,0)AS VARCHAR))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Centimeters' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE / 2.54 AS DECIMAL(10,2))/12 AS DECIMAL(10,2))
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Ounces' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE/16 AS DECIMAL(10,2))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Kilograms' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE * 2.2 AS DECIMAL(10,2))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds & Ounces' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
END AS FINAL
, DD.UNIT_OF_MEASURE_NAME

If there is a way I could tie the CUSTOMER_CODE to my first case statement
or if I could just select the MAX(PD.DD_MOD_DATE) out of the group and not the max of each..
I think this could resolve the problem

Thanks,

M

2

I don't know if this could help me with some clues been stuck on this for awhile :disappointed_relieved:

QUERY is below

SELECT DISTINCT PB.CUSTOMER_CODE, MAX(CONVERT(VARCHAR, PD.DD_MOD_DATE, 101)) AS MAXDATES, --, PD.DISCRETE_DATA_VALUE

CASE WHEN DD.UNIT_OF_MEASURE_NAME = 'Feet & Inches' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS VARCHAR)+'.' + CAST(FLOOR(CAST(ROUND(PD.DISCRETE_DATA_VALUE %12,0)AS VARCHAR))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Centimeters' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE / 2.54 AS DECIMAL(10,2))/12 AS DECIMAL(10,2))
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Ounces' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE/16 AS DECIMAL(10,2))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Kilograms' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE * 2.2 AS DECIMAL(10,2))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds & Ounces' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
END AS FINAL,
DD.UNIT_OF_MEASURE_NAME

FROM PT_BASIC AS PB INNER JOIN
PT_STATUS AS PS ON PB.CUSTOMER_ID = PS.CUSTOMER_ID INNER JOIN
PT_ADMISSION AS PA ON PS.ADMISSION_ID = PA.ADMISSION_ID INNER JOIN
PTC_DISCRETE_DATA_HEADER AS PH ON PB.CUSTOMER_ID = PH.CUSTOMER_ID INNER JOIN
PTC_DISCRETE_CLINICAL_DATA AS PCD ON PH.DISCRETE_DATA_HEADER_ID = PCD.DISCRETE_DATA_HEADER_ID INNER JOIN
PTC_DISCRETE_DATA AS PD ON PCD.DISCRETE_CLINICAL_DATA_ID = PD.DISCRETE_CLINICAL_DATA_ID INNER JOIN
DD_UNIT_OF_MEASURE AS DD ON PD.UNIT_OF_MEASURE_ID = DD.UNIT_OF_MEASURE_ID

WHERE
(PA.TERMINATION_DATE IS NULL) AND (PCD.CLINICAL_DATA_ID = 5 OR PCD.CLINICAL_DATA_ID = 6)
AND (PS.STATUS_END_DATE > '2015-10-07')

GROUP BY
PB.CUSTOMER_CODE,
PD.DD_MOD_DATE,
PD.DISCRETE_DATA_VALUE,
DD.UNIT_OF_MEASURE_NAME

Results

30717 07/25/2015 156.00 Pounds & Ounces 31531 11/12/2015 5.00 Inches 31667 11/13/2015 100.00 Pounds & Ounces 31672 10/26/2015 5.05 Feet & Inches 31898 11/15/2015 139.00 Pounds & Ounces 28681 02/11/2015 111.50 Pounds 30474 08/01/2015 90.50 Pounds 30717 08/24/2015 162.00 Pounds 30789 07/17/2015 117.06 Pounds & Ounces 31661 11/07/2015 185.00 Pounds 31763 10/31/2015 215.00 Pounds 31766 11/03/2015 130.00 Pounds 31908 11/16/2015 5.42 Centimeters 20389 03/19/2013 150.00 Pounds 20583 01/02/2014 22.08 Pounds & Ounces 24793 04/09/2014 5.07 Feet & Inches 24793 07/22/2015 140.00 Pounds **28681 04/29/2015 106.00 Pounds** **28681 05/27/2015 99.00 Pounds**
3

Solved for anyone trying to accomplish what I just did

SELECT -- , PB.CUSTOMER_CODE, PD.DD_MOD_DATE,

CASE WHEN DD.UNIT_OF_MEASURE_NAME = 'Feet & Inches' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS VARCHAR)+'.' + CAST(FLOOR(CAST(ROUND(PD.DISCRETE_DATA_VALUE %12,0)AS VARCHAR))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Centimeters' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE / 2.54 AS DECIMAL(10,2))/12 AS DECIMAL(10,2))
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Ounces' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE/16 AS DECIMAL(10,2))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Kilograms' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE * 2.2 AS DECIMAL(10,2))AS VARCHAR)
WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds & Ounces' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR)
END AS FINAL,
DD.UNIT_OF_MEASURE_NAME

FROM PT_BASIC AS PB LEFT JOIN
PT_STATUS AS PS ON PB.CUSTOMER_ID = PS.CUSTOMER_ID INNER JOIN
PT_ADMISSION AS PA ON PS.ADMISSION_ID = PA.ADMISSION_ID INNER JOIN
PTC_DISCRETE_DATA_HEADER AS PH ON PB.PATIENT_ID = PH.PATIENT_ID INNER JOIN
PTC_DISCRETE_CLINICAL_DATA AS PCD ON PH.DISCRETE_DATA_HEADER_ID = PCD.DISCRETE_DATA_HEADER_ID RIGHT JOIN
PTC_DISCRETE_DATA AS PD ON PCD.DISCRETE_CLINICAL_DATA_ID = PD.DISCRETE_CLINICAL_DATA_ID INNER JOIN
DD_UNIT_OF_MEASURE AS DD ON PD.UNIT_OF_MEASURE_ID = DD.UNIT_OF_MEASURE_ID
INNER JOIN
(
SELECT MAX(PD.DD_MOD_DATE) AS LATESTDATE, PB.CUSTOMER_CODE
FROM
PT_BASIC AS PB LEFT JOIN
PT_STATUS AS PS ON PB.CUSTOMER_ID = PS.CUSTOMER_ID INNER JOIN
PT_ADMISSION AS PA ON PS.ADMISSION_ID = PA.ADMISSION_ID INNER JOIN
PTC_DISCRETE_DATA_HEADER AS PH ON PB.PATIENT_ID = PH.PATIENT_ID INNER JOIN
PTC_DISCRETE_CLINICAL_DATA AS PCD ON PH.DISCRETE_DATA_HEADER_ID = PCD.DISCRETE_DATA_HEADER_ID RIGHT JOIN
PTC_DISCRETE_DATA AS PD ON PCD.DISCRETE_CLINICAL_DATA_ID = PD.DISCRETE_CLINICAL_DATA_ID INNER JOIN
DD_UNIT_OF_MEASURE AS DD ON PD.UNIT_OF_MEASURE_ID = DD.UNIT_OF_MEASURE_ID

                  GROUP BY 
                  PB.CUSTOMER_CODE                    
                  
                  )SubMax
                  ON PD.DD_MOD_DATE = SubMax.LATESTDATE
                  AND PB.CUSTOMER_CODE = SubMax.PATIENT_CODE

WHERE
(PA.TERMINATION_DATE IS NULL) AND (PCD.CLINICAL_DATA_ID = 5 OR PCD.CLINICAL_DATA_ID = 6)
AND (PS.STATUS_END_DATE > '2015-10-07')

GROUP BY
PB.CUSTOMER_CODE,
DD.UNIT_OF_MEASURE_NAME,
PD.DISCRETE_DATA_VALUE,
PD.DD_MOD_DATE

:laughing:

hopefully this problem pops up again so I could help

Cheers!