SELECT
	BASE.CHECKUP_DATE AS CK_DATE
	,BASE.MEMBER_CODE AS CODE
	,MEM.LAST_NAME || MEM.FIRST_NAME AS NAME

	,ROUND(CAST(BASE.WEIGHT AS NUMERIC) - CAST(PREV.WEIGHT AS NUMERIC), 1) AS CHG_WT
	
FROM

	(
	SELECT
		*
		,ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) ROWNUM
	FROM
		HEALTH_CHECKUP
	) AS BASE
	
INNER JOIN MEMBER_MST AS MEM
ON MEM.MEMBER_CODE = BASE.MEMBER_CODE

INNER JOIN 
(
	SELECT
		*
		,ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) ROWNUM
	FROM
		HEALTH_CHECKUP
	) AS PREV
ON PREV.MEMBER_CODE = BASE.MEMBER_CODE
AND PREV.ROWNUM = 2
AND ROUND(ABS(CAST(ROUND(BASE.WEIGHT, 1) AS NUMERIC) - CAST(ROUND(PREV.WEIGHT, 1) AS NUMERIC)), 1) >= 5.0

WHERE
	BASE.ROWNUM = 1
	
ORDER BY
	ROUND(CAST(BASE.WEIGHT AS NUMERIC) - CAST(PREV.WEIGHT AS NUMERIC), 1) DESC
	,BASE.MEMBER_CODE DESC
;