SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform_config].[Mitarbeiter] AS
--Mitarbeiter
SELECT [T1].[Client_DB]
     , [T1].[No_] AS [Employee No_]
     , [T1].[Name]
     , [T1].[First Name]
     , [T1].[Last Name]
     , [T1].[Employment Date]
     , iif([Termination Date] = convert(datetime, '1753-01-01T00:00:00.000'), NULL, [Termination Date]) AS [Leaving Date]
     , [Task Type Group]
     , [Function Code]
     , [T2].[Group No_ 1]
     , [T2].[Group No_ 2]
     , [T2].[Group No_ 3]
     , iif([Termination Date] > convert(datetime, '1753-01-01T00:00:00.000') AND [Termination Date] < getdate(), 0, 1) AS [aktiv]

     , getdate() as [Erstellungsdatum]
     , CASE
           WHEN [Function Code] IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI', 'KFZAZUBI', 'KFZ-AZUBI', 'KFZ AZUBI', 'AZUBI KFZ', 'AUFBER.', 'AUFBEREITE', 'MECHANIKER', 'SPENG') THEN 'prod. Personal'
    END AS [Monteur_Gruppe]
     , CASE
           WHEN [Termination Date] < getdate() AND [Termination Date] <> convert(datetime, '1753-01-01T00:00:00.000') THEN 'ausgetretene Mitarbeiter'
           WHEN [Function Code] IN ('AZUBI', 'MECH', 'SERVHILF', 'SERVTECH', 'WAGENPFLEG', 'WERKMEI', 'KFZAZUBI', 'KFZ-AZUBI', 'KFZ AZUBI', 'AZUBI KFZ', 'AUFBER.', 'AUFBEREITE', 'MECHANIKER', 'SPENG') THEN [Function Code]
    END AS [Monteur_Gruppe_2]

FROM [GC_TRANSIT_ARI].[import].[Employee] [T1]
         LEFT JOIN [GC_TRANSIT_ARI].[import].[Employee_T] [T2] ON [T1].[Client_DB] = [T2].[Client_DB] AND [T1].[No_] = [T2].[No_]
WHERE trim([T1].[Name]) <> ''
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO