WITH LabHistory AS (
    SELECT 
        [HN],
        [FULLNAME],
        [LN],
        [REQ ITEM CHECKIN DATETIME],
        -- ส่องดูวันที่ Checkin และ LN ของเทื่อก่อนหน้า
        LAG([REQ ITEM CHECKIN DATETIME]) OVER (PARTITION BY [HN] ORDER BY [REQ ITEM CHECKIN DATETIME]) AS Previous_Date,
        LAG([LN]) OVER (PARTITION BY [HN] ORDER BY [REQ ITEM CHECKIN DATETIME]) AS Previous_LN
    FROM [dbo].[view_lab_statistic_Order_List]
    WHERE [REQ ITEM CODE] = 'HM0001' -- Hb A1c
      AND [TEST INACTIVE] = '0'
)
SELECT 
    HN,
    FULLNAME,
    LN AS [Current_LN],
    [REQ ITEM CHECKIN DATETIME] AS [Current_Checkin],
    Previous_LN,
    Previous_Date,
    DATEDIFF(day, Previous_Date, [REQ ITEM CHECKIN DATETIME]) AS [Days_Gap]
FROM LabHistory
WHERE DATEDIFF(day, Previous_Date, [REQ ITEM CHECKIN DATETIME]) BETWEEN 1 AND 90
ORDER BY [REQ ITEM CHECKIN DATETIME] DESC;