ETL_DWH_ARI/schema/GC_ETL_ARI/views/load.Fahrzeug_Bestand.sql

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

GO