1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| SELECT MDP.Maintenance_Department_Name AS Département, MWO.Main_WO_nr AS OT_Principal, DPT.Teamcode_Name AS Equipe, WTM.Worktime_Name AS Poste,
DTE.YearOfWeek AS Annee, MWO.WO_Reference AS Référence, DTE.Week AS Semaine, PCD.Date AS Taget_Date, ACD.Date AS Date_fin_réelle,
RSD.Date AS DateDebutSouhaitee, MWO.WO_nr AS [n°OT], DIS.Discipline_Name AS Discipline, CDT.Date AS Date_de_création, PER.Personnel_Name AS Exécutant,
MWO.Work_Description_Actual_Extra AS A_faire, MWO.Work_Description_Actual AS Remarques, MWO.Work_Description_from_Planner AS Desciption_AL,
MWO.Planned_Shutdown_Minutes AS Temps_Arrêt, FAI.Failure_Name AS Cause, SYM.Symptom_Name AS Symptôme, ACT.Action_Name AS Action,
MWO.WO_Name AS Action_AL, MWO.Object_Id AS [Code équipement], OBJ.Object_Name AS Equipement, SWO.Standard_WO_Nr AS [Activité Standard],
ROUND(CONVERT(FLOAT, dbo.BI_Maintenance_Manpower.Total_Minutes) / 60, 2) AS [Heures Planifiées], ROUND(CONVERT(FLOAT, MWO.Rest_Minutes) / 60, 2)
AS [Heures Restantes], CASE WHEN MWO.WO_Ready = 0 THEN 'Non' ELSE 'Oui' END AS [Terminé?], MDP.Maintenance_Department AS CodeDépartement,
DPT.Teamcode AS CodeEquipe, WTM.Worktime AS CodePoste, DTE.Date AS DateFinSouhaitee, TOW.Type_of_Work AS CodeTypedetravail,
TOW.Type_of_Work_Name AS [Type de travail], MWO.ID, MWO.Personnel_Id_Planner, WST.WO_Status_Name
FROM dbo.BI_Maintenance_Workorders AS MWO INNER JOIN
dbo.BI_Workorders AS WOS ON MWO.WO_nr = WOS.WO_nr LEFT OUTER JOIN
dbo.BI_Maintenance_Manpower ON MWO.WO_nr = dbo.BI_Maintenance_Manpower.WO_nr LEFT OUTER JOIN
dbo.BI_Objects AS OBJ ON MWO.Object_Id = OBJ.Object_Id LEFT OUTER JOIN
dbo.BI_Disciplines AS DIS ON MWO.Discipline = DIS.Discipline_Unique_Id LEFT OUTER JOIN
dbo.BI_Failures AS FAI ON MWO.Failure = FAI.Failure_Unique_Id LEFT OUTER JOIN
dbo.BI_WO_Statusses AS WST ON MWO.WO_Status = WST.WO_Status_Unique_Id LEFT OUTER JOIN
dbo.BI_Symptoms AS SYM ON MWO.Symptom = SYM.Symptom_Unique_Id LEFT OUTER JOIN
dbo.BI_Actions AS ACT ON MWO.Action = ACT.Action_Unique_Id LEFT OUTER JOIN
dbo.BI_Persons AS PER ON MWO.Personnel_Id_Who_Does_it = PER.Personnel_Unique_Id LEFT OUTER JOIN
dbo.BI_Date AS DTE ON MWO.Requested_Completion_Date = DTE.Date_Unique_Id LEFT OUTER JOIN
dbo.BI_Date AS PCD ON MWO.Planned_Completion_Date = PCD.Date_Unique_Id LEFT OUTER JOIN
dbo.BI_Date AS RSD ON MWO.Requested_Start_Date = RSD.Date_Unique_Id LEFT OUTER JOIN
dbo.BI_Date AS ACD ON MWO.Actual_Completion_Date = ACD.Date_Unique_Id LEFT OUTER JOIN
dbo.BI_Date AS CDT ON MWO.Creation_Date_Time = CDT.Date_Unique_Id LEFT OUTER JOIN
dbo.BI_Types_of_Work AS TOW ON MWO.Type_of_Work = TOW.Type_of_Work_Unique_Id LEFT OUTER JOIN
dbo.BI_Department_Teams AS DPT ON MWO.Teamcode = DPT.Teamcode_Unique_id LEFT OUTER JOIN
dbo.BI_Worktimes AS WTM ON MWO.Worktime = WTM.Worktime_Unique_id LEFT OUTER JOIN
dbo.BI_Maintenance_Departments AS MDP ON MWO.Maintenance_Department = MDP.Maintenance_Department_Unique_Id LEFT OUTER JOIN
dbo.BI_Standard_WO AS SWO ON MWO.Standard_WO_Nr = SWO.Standard_WO_Nr_Unique_Id
WHERE (MDP.Maintenance_Department IN ('%nomdep%')) AND (RSD.Date > DATEADD(mm, - 12, GETDATE())) AND (LEFT(MWO.WO_Reference, 5) = '%TypeArret%') AND
(WST.WO_Status_Name <> N'Terminé techniquement') |
Requêtes paramétrées et Excel 2010
Répondre avec citation
Partager