USE [DriveMaintenance];
GO

IF EXISTS
(
    SELECT 1
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_SCHEMA = 'dbo'
          AND TABLE_NAME = 'View_PM_PLAN'
)
BEGIN
    DROP VIEW [dbo].[View_PM_PLAN];
END;
GO

CREATE VIEW [dbo].[View_PM_PLAN]
AS
SELECT pln.AutoID,
       pln.EquipmentID,
       dev.EquipmentID AS DisplayEquipmentID,
       dev.EquipmentName,
       frm.VersionCode,
       pln.MaintenanceYear,
       pln.MaintenanceMonth,
       pln.MaintenanceType,
       lst.PMStartMonth,
       pln.CreatUser,
       pln.CreatDate,
       lst.ChangeDate,
       lst.ChangeUser,
       '' AS ChangeUserName,
       lst.Remarks
FROM dbo.DriveMaintencePlan pln
    INNER JOIN dbo.DriveInformation dev
        ON pln.EquipmentID = dev.AutoID
    LEFT JOIN dbo.MaintenanceFormVersion frm
        ON dev.MaintenanceFormVersion = frm.AutoID
    INNER JOIN
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY lpln.EquipmentID,
                                               lpln.MaintenanceYear
                                  ORDER BY lpln.ChangeDate DESC
                                 ) AS RowNumber,
               lpln.AutoID,
               lpln.GUID,
               lpln.EquipmentID,
               lpln.MaintenanceYear,
               lpln.PMStartMonth,
               lpln.ChangeDate,
               lpln.ChangeUser,
               lpln.Remarks
        FROM dbo.DriveMaintencePlan lpln
    ) lst
        ON lst.RowNumber = 1
           AND pln.EquipmentID = lst.EquipmentID
           AND lst.MaintenanceYear = pln.MaintenanceYear
WHERE pln.MaintenanceType IN ( 'Annual', 'Quarterly', 'Semi-an', 'Monthly' )
      AND dev.EquipmentStatus = 1 AND pln.PlanStatus = 'A'