Latest date between records *Solved*

Transact-SQL
1

Hi Everyone,

Ive' been working on this query but I am stuck on a Where clause situation

SELECT PB.PATIENT_CODE, PD.DD_MOD_DATE, 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(FLOOR(PD.DISCRETE_DATA_VALUE / (12 * 2.54)) AS VARCHAR) + '.' + CAST(FLOOR(CAST(ROUND(PD.DISCRETE_DATA_VALUE / 2.54, 2) % 12 AS VARCHAR)) AS VARCHAR) 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, PCD.ERRONEOUS_FLAG

FROM dbo.PT_BASIC AS PB LEFT OUTER JOIN
dbo.PT_STATUS AS PS ON PB.CUSTOMER_ID = PS.CUSTOMER_ID INNER JOIN
dbo.PT_ADMISSION AS PA ON PS.ADMISSION_ID = PA.ADMISSION_ID INNER JOIN
dbo.PTC_DISCRETE_DATA_HEADER AS PH ON PB.CUSTOMER_ID = PH.CUSTOMER_ID INNER JOIN
dbo.PTC_DISCRETE_CLINICAL_DATA AS PCD ON PH.DISCRETE_DATA_HEADER_ID = PCD.DISCRETE_DATA_HEADER_ID RIGHT OUTER JOIN
dbo.PTC_DISCRETE_DATA AS PD ON PCD.DISCRETE_CLINICAL_DATA_ID = PD.DISCRETE_CLINICAL_DATA_ID INNER JOIN
dbo.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.PATIENT_CODE
FROM dbo.PT_BASIC AS PB LEFT OUTER JOIN
dbo.PT_STATUS AS PS ON PB.CUSTOMER_ID = PS.CUSTOMER_ID INNER JOIN
dbo.PT_ADMISSION AS PA ON PS.ADMISSION_ID = PA.ADMISSION_ID INNER JOIN
dbo.PTC_DISCRETE_DATA_HEADER AS PH ON PB.PATIENT_ID = PH.CUSTOMER_ID INNER JOIN
dbo.PTC_DISCRETE_CLINICAL_DATA AS PCD ON PH.DISCRETE_DATA_HEADER_ID = PCD.DISCRETE_DATA_HEADER_ID RIGHT OUTER JOIN
dbo.PTC_DISCRETE_DATA AS PD ON PCD.DISCRETE_CLINICAL_DATA_ID = PD.DISCRETE_CLINICAL_DATA_ID INNER JOIN
dbo.DD_UNIT_OF_MEASURE AS DD ON PD.UNIT_OF_MEASURE_ID = DD.UNIT_OF_MEASURE_ID
WHERE
DD.UNIT_OF_MEASURE_NAME IN('Pounds','Ounces', 'Kilograms', 'Pounds & Ounces')
-- need a way to just select the latest between this group

GROUP BY
PB.CUSTOMER_CODE, DD.UNIT_OF_MEASURE_NAME) AS SubMax ON PD.DD_MOD_DATE = SubMax.LATESTDATE AND PB.CUSTOMER_CODE = SubMax.CUSTOMER_CODE
WHERE
(PCD.CLINICAL_DATA_ID = 6) AND (PA.TERMINATION_DATE IS NULL) AND (PS.STATUS_END_DATE > '2015-01-01')
AND PB.CUSTOMER_CODE = '24460'

GROUP BY PB.CUSTOMER_CODE,
DD.UNIT_OF_MEASURE_NAME,
PD.DISCRETE_DATA_VALUE,
PD.DD_MOD_DATE,
PCD.ERRONEOUS_FLAG
HAVING (PCD.ERRONEOUS_FLAG = 0)

RESULTS

24460 2015-09-25 16:57:21.913 119.00000 119.00 Pounds
24460 2015-11-17 18:02:07.307 115.05000 115.05 Pounds & Ounces

I would need the latest soo 2015-11-17 Pounds & Ounces
I think its associated with DD.UNIT_OF_MEASURE_NAME IN('Pounds','Ounces', 'Kilograms', 'Pounds & Ounces')
but not too experienced with Case statements in the WHERE clause :persevere: sorry I feel like such a "Newb"

Thanks in advance!