SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform].[Zeit_Stempelungen_neu] AS

SELECT [Zeit_Stempelungen_neu].[Hauptbetrieb] AS [Hauptbetrieb_imr]
     , [Zeit_Stempelungen_neu].[Standort] AS [Standort_imr]
     , "GC_Department"."Hauptbetrieb_ID" AS "Hauptbetrieb_ID"
     , "GC_Department"."Hauptbetrieb_Name" AS "Hauptbetrieb_Name"
     , "GC_Department"."Standort_ID" AS "Standort_ID"
     , "GC_Department"."Standort_Name" AS "Standort_Name"
     , "Zeit_Stempelungen_neu"."Employee Number_Employees" AS [Employee_ID_imr]
     , "GC_Mitarbeiter"."Zuordnung_Produktiv" AS "Monteur_Gruppe"
     , "GC_Mitarbeiter"."Monteur_Ebene_1" AS "Monteur_Gruppe_2"
     , "Zeit_Stempelungen_neu"."Monteur" AS "Monteur"
     , "Zeit_Stempelungen_neu"."Order Number" AS "Order_Number"
     , "Zeit_Stempelungen_neu"."Zeitkategorie" AS "Activity_Codes_Group1"
     , "Zeit_Stempelungen_neu"."Zeitkategorie2" AS "Activity_Codes_Group2"
     , '' AS "Activity_Desc"
     , '' AS "Kostenstelle"
     , "Zeit_Stempelungen_neu"."Datum" AS "Invoice_Date"
     , "Zeit_Stempelungen_neu"."produktiv" AS "prod_"
     , "Zeit_Stempelungen_neu"."unproduktiv" AS "unprod__falsch"
     , 0.0 AS "Abw_"
     , 0.0 AS "Extern"
     , 0.0 AS "GWL"
     , 0.0 AS "Intern"
     , 0.0 AS "Sollzeit"
     , 0.0 AS "TOY_produktiv_Zeit"
     , 0.0 AS "verr__Zeit_ohne_TOY"
     , 0.0 AS "ben__Zeit_ohne_TOY"
     , 0.0 AS "Anz__AT"
     , "Zeit_Stempelungen_neu"."Anwesenheit" AS "Anw_"
     , "Zeit_Stempelungen_neu"."Order Positions_Times" AS "Order_Positions_Times"
     , CASE WHEN "Zeit_Stempelungen_neu"."Order Positions_Times" <> '' THEN 'offen' ELSE 'abgerechnet' END AS "Status"
     , CASE
           WHEN CASE WHEN ("Zeit_Stempelungen_neu"."Order Positions_Times" <> '') THEN 'offen' ELSE 'abgerechnet' END =
                'offen' THEN "Zeit_Stempelungen_neu"."produktiv"
           ELSE 0.0
       END AS "prod__aus_o_A_"
     , "GC_Mitarbeiter"."productivity_factor" AS "productivity_factor"
     , '' AS "Order Number_Mont"
FROM [transform_imr]."Zeit_Stempelungen_neu" "Zeit_Stempelungen_neu"
         LEFT OUTER JOIN "data"."GC_Department" "GC_Department" ON "Zeit_Stempelungen_neu"."Hauptbetrieb" = "GC_Department"."Hauptbetrieb" AND
                                                                   "Zeit_Stempelungen_neu"."Standort" = "GC_Department"."Standort"
         LEFT OUTER JOIN "data"."GC_Mitarbeiter" "GC_Mitarbeiter" ON "Zeit_Stempelungen_neu"."Employee Number_Employees" = "GC_Mitarbeiter"."Employee_ID"
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
