WITH
tests AS (
    SELECT
        t.PLANT_ID,
        t.WELL_ID,
        t.FUNC_LOC_NM,
        t.FUNC_LOC_SUPERIOR_ID,
        t.FUNC_LOC_MANUF_NM,
        t.FUNC_LOC_MANUFMODELNO_NO,
        t.LAST_TEST_FLG,
        t.MEASUREMENT_DATE_DT,
        t.NEXT_TEST_DATE_DT,
        t.CURRENT_PM_SCHEDULE_TYPE_CD,
        t.PM_SCHEDULE_TYPE_CD,
        LAG(t.PM_SCHEDULE_TYPE_CD, 1) OVER(PARTITION BY t.FUNC_LOC_NM ORDER BY t.MEASUREMENT_DATE_DT) AS PM_SCHEDULE_TYPE_CD_1,
        LAG(t.PM_SCHEDULE_TYPE_CD, 2) OVER(PARTITION BY t.FUNC_LOC_NM ORDER BY t.MEASUREMENT_DATE_DT ) AS PM_SCHEDULE_TYPE_CD_2,
        LAG(t.PM_SCHEDULE_TYPE_CD, 3) OVER(PARTITION BY t.FUNC_LOC_NM ORDER BY t.MEASUREMENT_DATE_DT ) AS PM_SCHEDULE_TYPE_CD_3,
        COUNT(*) OVER (PARTITION BY t.FUNC_LOC_NM) AS TEST_COUNT
        FROM WIP_PM_TEST_DETAIL t
        WHERE t.FUNC_LOC_NM LIKE '%GLV%'
        AND t.category_txt <> 'GLESDV'
        AND t.measurement_valuation_code = 'A'
        ORDER BY t.FUNC_LOC_NM
)
SELECT
     tests.PLANT_ID
    ,tests.WELL_ID
    ,tests.FUNC_LOC_NM AS FUNC_LOC
    ,tests.FUNC_LOC_SUPERIOR_ID AS POCKET
    ,tests.FUNC_LOC_MANUF_NM AS MANUFACTURER
    ,tests.FUNC_LOC_MANUFMODELNO_NO AS MODEL_NUMBER
    ,equipGroup.NAME AS LFL_GROUP
    ,equipInfo.EQUIP_VALID_FROM_DT AS INSTALL_DATE
    ,tests.MEASUREMENT_DATE_DT AS LAST_TEST_DATE
    ,tests.NEXT_TEST_DATE_DT AS NEXT_TEST_DATE
    ,tests.CURRENT_PM_SCHEDULE_TYPE_CD AS CURRENT_SCHEDULE
    ,tests.PM_SCHEDULE_TYPE_CD AS PREVIOUS_SCHEDULE_1
    ,tests.PM_SCHEDULE_TYPE_CD_1 AS PREVIOUS_SCHEDULE_2
    ,tests.PM_SCHEDULE_TYPE_CD_2 AS PREVIOUS_SCHEDULE_3
    ,tests.PM_SCHEDULE_TYPE_CD_3 AS PREVIOUS_SCHEDULE_4
    ,tests.TEST_COUNT
FROM tests

-- ## Left join only on wip_mart_equipment_rows with the most recent install date for each equipment
LEFT JOIN
    (
        SELECT wip_mart_equip_info.*
        FROM wip_mart_equip_info
        inner join (
                        SELECT FRONT_END_ID,
                        MAX(EQUIP_VALID_FROM_DT) OVER(PARTITION BY equip_manuf_nm, equip_manufmodelno_no) AS maximum_date
                        FROM wip_mart_equip_info
                    ) maxDates
                    ON maxDates.FRONT_END_ID = wip_mart_equip_info.front_end_id
                    AND maxDates.maximum_date = wip_mart_equip_info.EQUIP_VALID_FROM_DT
    ) equipInfo
    ON equipInfo.EQUIP_MANUF_NM = tests.FUNC_LOC_MANUF_NM
    AND equipInfo.EQUIP_MANUFMODELNO_NO = tests.FUNC_LOC_MANUFMODELNO_NO

LEFT JOIN WIP_EQUIPMENT_MASTER equipMaster
    ON equipInfo.EQUIP_MANUF_NM = equipMaster.MANUFACTURER
    AND equipInfo.EQUIP_MANUFMODELNO_NO = equipMaster.MODEL_NAME
LEFT JOIN WIP_EQUIPMENT_GROUP equipGroup
    ON equipMaster.EQUIPMENT_GROUP_ID = equipGroup.ID

WHERE tests.LAST_TEST_FLG='Y'
ORDER BY PLANT_ID, WELL_ID, tests.FUNC_LOC_NM, MEASUREMENT_DATE_DT