SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[Zeiterfassung]
AS
/* Datenquelle:
    Datum = Stempelung:
        1. Time_Clock_Entry: Stempelzeiten produktiv/unproduktiv und stundenweise Abwesenheit
        2. Zeit_2_Ryma: ganztägige Abwesenheit und Sollzeit bei Anwesenheit
    Datum = Rechnungsdatum (Posting Date):
        3. Add_Serv_Ledger_Entry_fakt_AW_Monteur: Abgerechnete Stunden aus Auftrag 
        4. Stempelzeiten_Monteurlisten_aus_ims: Stempelzeiten zu Auftrag 
*/
SELECT convert(varchar(20), [Client_DB]) AS [Client_DB]
     , convert(varchar(20), [No]) AS [No]
     , convert(varchar(20), [Document No]) AS [Document No]
     , convert(decimal(28, 8), [Zeitdauer]) AS [Zeitdauer]
     , convert(varchar(20), [Stempel-Code]) AS [Stempel-Code]
     , convert(varchar(100), [Monteur]) AS [Monteur]
     , try_convert(datetime, [Datum]) AS [Datum]
     , convert(varchar(20), [Department No]) AS [Department No]
     , convert(varchar(100), [Order Number]) AS [Order Number]
     , try_convert(datetime, [Employment Date]) AS [Employment Date]
     , convert(varchar(50), [Activity_Codes_Group1]) AS [Activity_Codes_Group1]
     , convert(varchar(50), [Activity_Codes_Group2]) AS [Activity_Codes_Group2]
     , convert(varchar(50), [Activity_Desc]) AS [Activity_Desc]
     , convert(varchar(100), [First Name]) AS [First Name]
     , convert(varchar(100), [Last Name]) AS [Last Name]
     , convert(varchar(20), [Task Type Group]) AS [Task Type Group]
     , convert(varchar(20), [Function Code]) AS [Function Code]
     , convert(varchar(20), [Group No_ 1]) AS [Group No_ 1]
     , convert(varchar(20), [Group No_ 2]) AS [Group No_ 2]
     , convert(varchar(20), [Group No_ 3]) AS [Group No_ 3]
     , convert(varchar(50), [Monteur_Gruppe]) AS [Monteur_Gruppe]
     , convert(varchar(50), [Monteur_Gruppe_2]) AS [Monteur_Gruppe_2]
     , convert(varchar(20), [Kennzahl]) AS [Kennzahl]

     , convert(decimal(28, 8), [prod.]) AS [prod.]
     , convert(decimal(28, 8), [unprod.]) AS [unprod.]
     , convert(decimal(28, 8), [Abw.]) AS [Abw.]
     , convert(decimal(28, 8), [fakt. Std.]) AS [fakt. Std.]
     , convert(decimal(28, 8), [ben. Std.]) AS [ben. Std.]
     , convert(decimal(28, 8), [Sollzeit]) AS [Sollzeit]
     , convert(decimal(28, 8), [nicht zugeordnet]) AS [nicht zugeordnet]

     , convert(decimal(28, 8), [Extern]) AS [Extern]
     , convert(decimal(28, 8), [Intern]) AS [Intern]

     , convert(decimal(28, 8), [Umsatz Lohn]) AS [Umsatz Lohn]
     , convert(decimal(28, 8), [Nachlass]) AS [Nachlass]

     , convert(varchar(50), [Source Table]) AS [Source Table]

     , convert(varchar(20), [Hauptbetrieb_ID]) AS [Hauptbetrieb_ID]
     , convert(varchar(50), [Hauptbetrieb_Name]) AS [Hauptbetrieb_Name]
     , convert(varchar(20), [Standort_ID]) AS [Standort_ID]
     , convert(varchar(50), [Standort_Name]) AS [Standort_Name]
FROM (SELECT [Z].[Client_DB]
           , [Z].[No]
           , [Z].[Document No]
           , [Z].[Zeitdauer]
           , [Z].[Stempel-Code]
           , [Z].[Monteur]
           , [Z].[Datum]
           , [Z].[Department No]
           , [Z].[Order Number]
           , [Z].[Employment Date]
           , [Z].[Activity_Codes_Group1]
           , [Z].[Activity_Codes_Group2]
           , [Z].[Activity_Desc]
           , [Z].[First Name]
           , [Z].[Last Name]
           , [Z].[Task Type Group]
           , [Z].[Function Code]
           , [Z].[Group No_ 1]
           , [Z].[Group No_ 2]
           , [Z].[Group No_ 3]
           , [Z].[Monteur_Gruppe]
           , [Z].[Monteur_Gruppe_2]
           , [Z].[Kennzahl]

           , iif([Z].[Kennzahl] = 'prod.', [Z].[Duration], 0.0) AS [prod.]
           , iif([Z].[Kennzahl] = 'unprod.', [Z].[Duration], 0.0) AS [unprod.]
           , iif([Z].[Kennzahl] = 'Abw.', [Z].[Duration], 0.0) AS [Abw.]
           , iif([Z].[Kennzahl] = 'fakt. Std.', [Z].[Duration], 0.0) AS [fakt. Std.]
           , iif([Z].[Kennzahl] = 'ben. Std.', [Z].[Duration], 0.0) AS [ben. Std.]
           , iif([Z].[Kennzahl] = 'Sollzeit', [Z].[Duration], 0.0) AS [Sollzeit]

           , iif([Z].[Kennzahl] IS NULL, [Z].[Duration], 0.0) AS [nicht zugeordnet]

           -- Produktivzeiten aufgeschlüsselt nach Umsatzart
           , [Z].[Extern]
           , [Z].[Intern]

           -- Umsatz und Nachlass bezogen auf fakturierte Stunden
           , [Z].[Umsatz Lohn]
           , [Z].[Nachlass]

           -- Definition siehe oben
           , [Z].[Source Table]

           , [S].[Hauptbetrieb_ID]
           , [S].[Hauptbetrieb_Name]
           , [S].[Standort_ID]
           , [S].[Standort_Name]

      FROM [GC_ETL_ARI].[load_2].[Zeit_gesamt] [Z]
               LEFT JOIN [transform_basis].[Department_Standort] [S] ON [Z].[Client_DB] = [S].[Client_DB] AND [Z].[Department No] = [S].[Department No_]) AS [V1]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO