ETL_DWH_LOCOSOFT/schema/GC/views/transform.Zeit_Stempelungen_neu.sql

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

select "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",
       "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                                                                                                   AS "Abw_",
       0                                                                                                   AS "Extern",
       0                                                                                                   AS "GWL",
       0                                                                                                   AS "Intern",
       0                                                                                                   AS "Sollzeit",
       0                                                                                                   AS "TOY_produktiv_Zeit",
       0                                                                                                   AS "verr__Zeit_ohne_TOY",
       0                                                                                                   AS "ben__Zeit_ohne_TOY",
       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 end                                                                                      AS "prod__aus_o_A_",
       "GC_Mitarbeiter"."productivity_factor"                                                              AS "productivity_factor",
	   '' AS "Order Number_Mont"
from (locosoft."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