C11/System/ARI/SQL/schema/GC_ETL_ARI/views/load.Belege.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [load].[Belege]
AS
SELECT convert(varchar(20), b.[Client_DB]) AS [Client_DB]
	 , convert(varchar(20), b.[Department Code]) AS [Department Code]
	 , convert(varchar(20), b.[Make Code]) AS [Make Code]
	 , convert(varchar(20), b.[Branch Code]) AS [Branch Code]
     , convert(datetime, b.[Bookkeep Date]) AS [Bookkeep Date]
--     , b.[Betrieb Nr]
     , convert(varchar(20), b.[Acct Nr]) AS [Acct Nr]
     , convert(decimal(28,8), b.[Betrag]) AS [Betrag]
     , CONVERT(VARCHAR(300), b.[Text]) AS [Text]
	 , CONVERT(VARCHAR(300), b.[Beleg]) AS [Beleg]
	 , CONVERT(VARCHAR(20), b.[VIN]) AS [VIN]
--     , b.[Hauptbetrieb]
--     , b.[Marke]
	 , 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]
	 , CONVERT(varchar(20), ISNULL(m.[Fabrikat], 'Fremd')) AS [Marke]
     , CONVERT(int, m.[Fabrikat_Sortierung]) AS [Fabrikat_Sortierung]
	 		,convert(date, b.[Bookkeep Date]) AS [Datum]
  	,convert(varchar(20), FORMAT(b.[Bookkeep Date], 'yyyy/MM')) AS [Jahr/Monat]
	,CAST(GETDATE() AS DATE) AS [Aktueller Tag]
	,convert(date, (DATEADD(month, DATEDIFF(month, 0, b.[Bookkeep Date]), 0))) AS [Monatserster]
	,EOMONTH(b.[Bookkeep Date]) AS Monatsletzter
    ,convert(varchar(100),'("' + 
    CONVERT(varchar, DATEADD(month, DATEDIFF(month, 0, b.[Bookkeep Date]), 0), 23) + 
    '","' + 
    CONVERT(varchar, EOMONTH(b.[Bookkeep Date]), 23) + 
    '")') AS MUN_DATE
    ,convert(varchar(50), CONVERT(varchar, DATEADD(month, DATEDIFF(month, 0, b.[Bookkeep Date]), 0), 112) + 
    '-' + 
    CONVERT(varchar, EOMONTH(b.[Bookkeep Date]), 112)) AS Mitgliedsname
    ,DATEFROMPARTS(YEAR(b.[Bookkeep Date]), 1, 1) AS [Beginn Geschäftsjahr]
	,convert(varchar(20), FORMAT(b.[Bookkeep Date], 'MMM', 'de') + './'   + FORMAT(b.[Bookkeep Date], 'yyyy', 'de')) AS Monat_Jahr
	,convert(varchar(20), FORMAT(b.[Bookkeep Date], 'yyyy')) AS [Jahr]
	,convert(varchar(20), FORMAT(b.[Bookkeep Date], 'MM')) AS [Monat]
	,FORMAT(b.[Bookkeep Date], 'MMM', 'de-DE') AS [Monat_kurz]
FROM [transform].[Belege] b
LEFT JOIN [x_data].[Marke] m
    ON b.[Make Code] = m.[Make Code]
LEFT JOIN [x_data].[AH_Standort] l
    ON b.[Client_DB] = l.[Client_DB]
    AND b.[Branch Code] = l.[Branch Code]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO