USE [DriveMaintenance];
GO

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

CREATE VIEW [dbo].[View_PM_Plan_Progress]
AS
SELECT pln.AutoID AS PlanAutoID,
       dev.AutoID AS EquipmentID,
       pln.MaintenanceYear AS PlanYear,
       pln.MaintenanceMonth AS PlanMonth,
       pln.MaintenanceType AS PlanType,
       ISNULL(dat.AutoID, 0) AS RecordAutoID
FROM dbo.DriveMaintencePlan AS pln
    INNER JOIN dbo.DriveInformation AS dev
        ON pln.EquipmentID = dev.AutoID
           AND dev.EquipmentStatus = 1
    LEFT OUTER JOIN dbo.MaintenanceRecord AS dat
        ON pln.AutoID = dat.PlanPrimaryID
WHERE (pln.MaintenanceType IN ( 'Annual', 'Quarterly', 'Semi-an', 'Monthly' ));
GO