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

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

GO