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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[Zeit_ben_Std_LG] 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_ben_Std_LG3"."Monteur"                                                              AS "Monteur",
       "Zeit_ben_Std_LG3"."Order_Number" + ' - ' + "Zeit_ben_Std_LG3"."Description_Labour_Types" AS "Order_Number",
       "Zeit_ben_Std_LG3"."Zeitkategorie"                                                        AS "Activity_Codes_Group1",
       "Zeit_ben_Std_LG3"."Zeitkategorie2"                                                       AS "Activity_Codes_Group2",
       ''                                                                                        AS "Activity_Desc",
       ''                                                                                        AS "Kostenstelle",
       "Zeit_ben_Std_LG3"."Datum"                                                                AS "Invoice_Date",
       0                                                                                         AS "prod__",
       0                                                                                         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",
       "Zeit_ben_Std_LG3"."ben__Std_"                                                            AS "ben__Zeit_ohne_TOY",
       0                                                                                         AS "Anz__AT",
       "Zeit_ben_Std_LG3"."ben__Std_"                                                            AS "ben__Zeit",
       ''                                                                                        AS "Order_Positions_Times",
       'abgerechnet'                                                                             AS "Status",
       "GC_Mitarbeiter"."productivity_factor"                                                    AS "productivity_factor",
       case
           when datediff(dd, "Zeit_ben_Std_LG3"."Datum",
                         convert(datetime, convert(char(8), current_timestamp, 112), 112)) < 7 then
               "Zeit_ben_Std_LG3"."Order_Number" + ' - ' + "Zeit_ben_Std_LG3"."Description_Labour_Types"
           else 'älter 7 Tage' end                                                               AS "Order_Number_Mont"
from ((select "Zeit_ben_Std_LG"."Order Number"             AS "Order_Number",
              "Zeit_ben_Std_LG"."Description_Labour_Types" AS "Description_Labour_Types",
              "Zeit_ben_Std_LG"."Zeitkategorie"            AS "Zeitkategorie",
              "Zeit_ben_Std_LG"."Zeitkategorie2"           AS "Zeitkategorie2",
              "Zeit_ben_Std_LG"."Employee Number_Employee" AS "Employee_Number_Employee",
              "Zeit_ben_Std_LG"."Monteur"                  AS "Monteur",
              "Zeit_ben_Std_LG"."Datum"                    AS "Datum",
              "Zeit_ben_Std_LG"."ben  Std "                AS "ben__Std_",
              '1'                                          AS "Hauptbetrieb",
              "Zeit_ben_Std_LG"."Subsidiary_Labours"       AS "Standort"
       from locosoft."Zeit_ben_Std_LG" "Zeit_ben_Std_LG") "Zeit_ben_Std_LG3" LEFT OUTER JOIN "data"."GC_Department" "GC_Department"
      on "Zeit_ben_Std_LG3"."Hauptbetrieb" = "GC_Department"."Hauptbetrieb" and
         "Zeit_ben_Std_LG3"."Standort" = "GC_Department"."Standort")
         LEFT OUTER JOIN "data"."GC_Mitarbeiter" "GC_Mitarbeiter"
                         on "Zeit_ben_Std_LG3"."Employee_Number_Employee" = "GC_Mitarbeiter"."Employee_ID"
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO