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

SELECT "offene_Auftraege_DG_Anz_Tage"."Hauptbetrieb" AS [Hauptbetrieb_imr]
     , "offene_Auftraege_DG_Anz_Tage"."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"
     , "offene_Auftraege_DG_Anz_Tage"."Serviceberater" AS "Serviceberater"
     , "offene_Auftraege_DG_Anz_Tage"."Order Number" AS "Order_Number"
     , "offene_Auftraege_DG_Anz_Tage"."Fabrikat" AS [Fabrikat_imr]
     , CASE WHEN "GC_Marken"."Fabrikat" IS NULL THEN 'Fremd' ELSE "GC_Marken"."Fabrikat" END AS "Fabrikat"
     , CASE
           WHEN CASE WHEN "GC_Marken"."Fabrikat" IS NULL THEN 'Fremd' ELSE "GC_Marken"."Fabrikat" END = 'Fremd' THEN 9
           ELSE "GC_Marken"."Order_By"
       END AS "Fabrikat_Order_By"
     , "offene_Auftraege_DG_Anz_Tage"."Model" AS "Model"
     , '' AS "Fahrzeug"
     , '' AS "Kostenstelle"
     , "offene_Auftraege_DG_Anz_Tage"."Description_Makes" AS "Marke"
     , "offene_Auftraege_DG_Anz_Tage"."Kunde" AS "Kunde"
     , "offene_Auftraege_DG_Anz_Tage"."Umsatzart" AS "Turnover_Type_Desc"
     , "offene_Auftraege_DG_Anz_Tage"."Anzahl Tage" AS "Tage_offen"
     , "offene_Auftraege_DG_Anz_Tage"."DG" AS "DG"
     , 0.0 AS "Lohn"
     , 0.0 AS "Teile"
     , 0.0 AS "Sonst_"
     , "offene_Auftraege_DG_Anz_Tage"."Order Date" AS "Invoice_Date"
	 , "offene_Auftraege_DG_Anz_Tage"."Anzahl Tage_1" AS "Anzahl_Tage_1"
FROM [transform_imr]."offene_Auftraege_DG_Anz_Tage" "offene_Auftraege_DG_Anz_Tage"
         LEFT OUTER JOIN "data"."GC_Department" "GC_Department" ON "offene_Auftraege_DG_Anz_Tage"."Hauptbetrieb" = "GC_Department"."Hauptbetrieb" AND "offene_Auftraege_DG_Anz_Tage"."Standort" = "GC_Department"."Standort"
         LEFT OUTER JOIN "data"."GC_Marken" "GC_Marken" ON "offene_Auftraege_DG_Anz_Tage"."Fabrikat" = "GC_Marken"."Description"
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
