31 lines
843 B
MySQL
31 lines
843 B
MySQL
|
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
|