SELECT
	R.[LN],
	MAX ( R.[HN] ) AS [HN],
	MAX ( R.[FULLNAME] ) AS [ชื่อ-สกุล],
	MAX ( R.[SEX] ) AS [เพศ],
	MAX ( R.[YEAR] ) AS [อายุ(ปี)],
	MAX ( R.[PATIENT TYPE NAME] ) AS [ประเภทผู้ป่วย],
	MAX ( R.[WARD NAME] ) AS [วอร์ด/แผนก],
	MAX ( R.[DOCTOR NAME] ) AS [แพทย์ผู้สั่ง],
	MAX ( R.[ORDER NO] ) AS [เลขที่ Order],
	MAX ( R.[REQ ITEM CHECKIN DATETIME] ) AS [วันที่ Checkin],
	MAX ( R.[PRIORITY NAME] ) AS [ความเร่งด่วน], 

	STUFF(
		(
		SELECT
			', ' + R2.[REQ ITEM NAME] 
		FROM
			dbo.view_lab_statistic_Result_List R2 
		WHERE
			R2.[LN] = R.[LN] 
			AND R2.[ORDER INACTIVE] = 'N' 
			AND R2.[RES ITEM STATE] = 'A' 
			AND R2.[REQ STATE] <> 'R' 
			AND R2.[RES ITEM INACTIVE] = 'N' 
			AND R2.[RES ITEM REJECT] = 'N' 
		GROUP BY
			R2.[REQ ITEM NAME],
			R2.[REQ ITEM TEST SEQ] 
		ORDER BY
			R2.[REQ ITEM TEST SEQ] 
		FOR XML PATH(''), TYPE 
		).value('.', 'NVARCHAR(MAX)'),
		1,
		2,
		'' 
	) AS [รายการตรวจทั้งหมด],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'SRT079' THEN R.[RES ITEM RESULT] END) AS [HbA1C (%) (4.8-5.9)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'EAG' THEN R.[RES ITEM RESULT] END) AS [Estimated FBS (mg/dL) (70-100)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'CHT003' THEN R.[RES ITEM RESULT] END) AS [Creatinine (mg/dL) (0.67-1.17)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'GFR' THEN R.[RES ITEM RESULT] END) AS [eGFR (mL/min/1.73m2) (>90)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'CRCL' THEN R.[RES ITEM RESULT] END) AS [CrCl (mL/min) (97-137)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'CHT104' THEN R.[RES ITEM RESULT] END) AS [TOTAL CHOLESTEROL (mg/dL) (0-200)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'CHT107' THEN R.[RES ITEM RESULT] END) AS [Triglyceride (mg/dL) (0-150)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'CHT105' THEN R.[RES ITEM RESULT] END) AS [HDL_C (mg/dL) (>40)],
	MAX(CASE WHEN R.[RES ITEM CODE] = 'LDLC' THEN R.[RES ITEM RESULT] END) AS [LDL_C (mg/dL) (0-100)]
	
FROM
	dbo.view_lab_statistic_Result_List R
WHERE
	R.[REQ ITEM CHECKIN DATETIME] BETWEEN '@d1' AND '@d2' 
	AND R.[WARD NAME] LIKE N'%รพ.สต.%' 
	AND R.[ORDER INACTIVE] = 'N' 
	AND R.[RES ITEM STATE] = 'A' 
	AND R.[REQ STATE] <> 'R' 
	AND R.[RES ITEM INACTIVE] = 'N' 
	AND R.[RES ITEM REJECT] = 'N' 
	AND EXISTS ( 
        SELECT 1 
        FROM dbo.view_lab_statistic_Result_List R_chk 
        WHERE
          R_chk.[LN] = R.[LN] 
          AND R_chk.[RES ITEM APPROVE STAFF CODE] = '1733' 
    ) 
GROUP BY
	R.[LN] 
ORDER BY
	R.[LN] DESC;