SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [template].[GC_Mitarbeiter] AS
SELECT 
    '1' AS Client_DB,
    T1.employee_number AS Employee_ID,
    T1.name AS Name,
    '' AS Department,
    '' AS Zuordnung_Funktion,
    CASE 
        WHEN T1.mechanic_number IS NOT NULL THEN 'produktiv'
        ELSE NULL
    END AS Zuordnung_Produktiv,
    CASE 
        WHEN T1.mechanic_number IS NOT NULL AND T1.leave_date < CAST(GETDATE() AS DATE) THEN 'ausgetr. MA'
        WHEN T1.mechanic_number IS NOT NULL THEN 'Monteure'
        ELSE NULL
    END AS Monteur_Ebene_1,
    NULL AS Monteur_Ebene_2,
    NULL AS Monteur_Ebene_3,
    NULL AS Zuordnung_Department_Name,
    FORMAT(T1.productivity_factor, 'N1') AS productivity_factor,
    T1.leave_date AS leave_date
FROM dbo.employees T1
WHERE 
    (T1.leave_date >= CAST('2016-01-01' AS DATE) OR T1.leave_date IS NULL);
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO