SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[Fahrzeug_Bestand]
AS
SELECT convert(varchar(20), [Client_DB]) AS [Client_DB]
     , convert(varchar(50), [Modellbez]) AS [Modellbez]
     , convert(varchar(100), [FZG]) AS [FZG]
     , convert(varchar(20), [Location Code]) AS [Location Code]
     , convert(varchar(20), [VB_Einkauf]) AS [VB_Einkauf]
     , convert(decimal(28, 8), [Standtage_ori]) AS [Standtage_ori]
     , convert(varchar(20), [EZ/Eingang]) AS [EZ/Eingang]
     , convert(varchar(50), [Farbe]) AS [Farbe]
     , convert(decimal(28, 8), [Einsatz]) AS [Einsatz]
     , convert(datetime, [Invoice Date]) AS [Invoice Date]
     , convert(decimal(28, 8), [Standtage]) AS [Standtage]
     , convert(decimal(28, 8), [gepl. VK]) AS [gepl. VK]
     , convert(varchar(20), [Händlerstatus]) AS [Händlerstatus]
     , convert(decimal(28, 8), [UPE]) AS [UPE]
     , convert(varchar(50), [Model]) AS [Model]
     , convert(varchar(50), [Standtagestaffel]) AS [Standtagestaffel]
     , convert(decimal(28, 8), [Tage letzte Preisänderung_ori]) AS [Tage letzte Preisänderung_ori]
     , convert(decimal(28, 8), [Tage seit Erstzul.]) AS [Tage seit Erstzul.]
     , convert(decimal(28, 8), [Tag_Dashboard]) AS [Tag_Dashboard]
     , convert(decimal(28, 8), [Monat_Dashboard]) AS [Monat_Dashboard]
     , convert(decimal(28, 8), [Jahr_Dashboard]) AS [Jahr_Dashboard]
     , convert(varchar(20), [Fahrzeugart]) AS [Fahrzeugart]
     , convert(varchar(20), [Fahrzeugtyp]) AS [Fahrzeugtyp]
     , convert(varchar(20), [Fabrikat]) AS [Fabrikat]
     , convert(varchar(10), [Fabrikat_Sortierung]) AS [Fabrikat_Sortierung]
     , convert(varchar(20), [Hauptbetrieb_ID]) AS [Hauptbetrieb_ID]
     , convert(varchar(50), [Hauptbetrieb_Name]) AS [Hauptbetrieb_Name]
     , convert(varchar(20), [Standort_ID]) AS [Standort_ID]
     , convert(varchar(50), [Standort_Name]) AS [Standort_Name]
FROM (SELECT [Bestand].[Client_DB] AS [Client_DB]
--     , Bestand.[Hauptbetrieb]
--     , Bestand.[Fabrikat]
           , [Bestand].[Modellbez] AS [Modellbez]
--     , Bestand.[Fahrzeugart]
           , [Bestand].[FZG] AS [FZG]
           , [Bestand].[Location Code] AS [Location Code]
           , [Bestand].[VB_Einkauf] AS [VB_Einkauf]
           , [Bestand].[Standtage_ori] AS [Standtage_ori]
           , [Bestand].[EZ/Eingang] AS [EZ/Eingang]
           , [Bestand].[Farbe] AS [Farbe]
           , [Bestand].[Einsatz] AS [Einsatz]
           , [Bestand].[Invoice Date] AS [Invoice Date]
--     , Bestand.[Standtage] as [Standtage_alt]
           , [Bestand].[Standtage_ori] * 1.0 / count([Bestand].[Client_DB]) OVER (PARTITION BY ([Bestand].[FZG])) AS [Standtage]
           , [Bestand].[gepl. VK] AS [gepl. VK]
--     , Bestand.[Standort]
           , [Bestand].[Händlerstatus] AS [Händlerstatus]
           , [Bestand].[UPE] AS [UPE]
--     , Bestand.[Fahrzeugtyp]
           , [Bestand].[Model] AS [Model]
           , [Bestand].[Standtagestaffel] AS [Standtagestaffel]
           , [Bestand].[Tage letzte Preisänderung_ori] AS [Tage letzte Preisänderung_ori]
           , [Bestand].[Tage seit Erstzul.] AS [Tage seit Erstzul.]
           , [Bestand].[Tag_Dashboard] AS [Tag_Dashboard]
           , [Bestand].[Monat_Dashboard] AS [Monat_Dashboard]
           , [Bestand].[Jahr_Dashboard] AS [Jahr_Dashboard]
           , [Fahrzeugtyp].[Zuordnung_Fahrzeugart] AS [Fahrzeugart]
           , [Fahrzeugtyp].[Inventory Posting Group] AS [Fahrzeugtyp]
           , isnull([Marke].[Fabrikat], 'Fremd') AS [Fabrikat]
           , [Marke].[Fabrikat_Sortierung] AS [Fabrikat_Sortierung]
           , [Standort].[Hauptbetrieb_ID] AS [Hauptbetrieb_ID]
           , [Standort].[Hauptbetrieb_Name] AS [Hauptbetrieb_Name]
           , [Standort].[Standort_ID] AS [Standort_ID]
           , [Standort].[Standort_Name] AS [Standort_Name]
      FROM [transform].[NW_GW_BE_neu] [Bestand]
               LEFT JOIN [x_data].[Marke] [Marke] ON [Bestand].[Make Code_Vehicle] = [Marke].[Make Code]
               LEFT JOIN [x_data].[AH_Standort] [Standort] ON [Bestand].[Client_DB] = [Standort].[Client_DB]
          AND [Bestand].[Location Code] = [Standort].[Branch Code]
               LEFT JOIN [x_data].[Fahrzeugtyp] [Fahrzeugtyp] ON [Fahrzeugtyp].[Inventory Posting Group] = [Bestand].[Inventory Posting Group]) AS [V1]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO