58 lines
1.6 KiB
MySQL
58 lines
1.6 KiB
MySQL
|
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'
|