SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW [load].[Aftersales] AS SELECT --TOP(10) convert(varchar(20), s.[Client_DB]) as Client_DB, convert(varchar(20), s.[Department Code]) as [Department Code], convert(varchar(100), s.[Service Order No]) AS [Auftrags-Nr.], convert(varchar(20), s.[Gen Prod Posting Group]) as [Gen Prod Posting Group], -- convert(varchar(100), s.[Serviceberater]) as [Serviceberater_alt], convert(varchar(100), isnull(u.[Name], s.[User Id])) as [Benutzer Abr.], convert(varchar(100), CASE WHEN s.[Auftragsart] = ('Teile') THEN s.[Serviceberater] ELSE a.[Name] END) as [Serviceberater], convert(varchar(130), left(t.[Monteur_und_Stunden], 130)) as [Monteur], -- s.[Hauptbetrieb], -- s.[Standort], convert(varchar(20), s.[Umsatzart]) as [Umsatzart], convert(varchar(50), s.[Model]) as [Model], convert(varchar(100), s.[Fahrzeug]) as [Fahrzeug], convert(varchar(20), s.[Marke]) as [Marke], convert(varchar(20), s.[Auftragsart]) as [Auftragsart], convert(varchar(50), s.[Kundenart]) as [Kundenart], convert(varchar(200), s.[Kunde]) as [Kunde], -- s.[Umsatz Lohn], -- s.[Umsatz Teile Service], -- s.[Einsatz Teile Service], convert(datetime, s.[Invoice Date]) as [Invoice Date], -- s.[Order Number], -- s.[Order Number_Rg_Ausg], -- convert(decimal(28,8), s.[ben. AW_Time_Clock]) AS [ben. AW], 12 * (convert(decimal(28,8), ISNULL(t.[ben. Std. Auftrag], 0)) / NULLIF(COUNT(s.[Client_DB]) OVER (PARTITION BY s.[Service Order No]), 0)) AS [ben. AW], convert(decimal(28,8), s.[verk Std]) AS [verk. AW], -- s.[DG] AS DG_ALT, convert(varchar(200), s.[Auftragsposition]) as [Auftragsposition], convert(varchar(20), s.[SB_Gruppe]) as [SB_Gruppe], convert(decimal(28,8), s.[Rabatt Lohn]) as [Rabatt Lohn], convert(decimal(28,8), s.[Rabatt Teile]) as [Rabatt Teile], -- s.[Order_Desc_100], -- s.[Invoice_Desc_100], -- s.[TÜV_Amount], -- s.[FL_Lack_Amount], -- s.[Mietw_Amount], -- s.[Umsatz_Sonst_Rest], convert(varchar(20), s.[No]) AS [Rg-Nr.], convert(varchar(200), (s.[No] + ' - ' + s.[Service Order No] + ' - ' + s.[Kunde])) AS [RG/Auftrag/Kunde], convert(varchar(200), (s.[No] + ' - ' + s.[Serviceberater] + ' - ' + s.[Kunde] + ' - ' + FORMAT(s.[Invoice Date], 'dd.MM.yyyy'))) AS [RG/SB/Kunde/Datum], convert(varchar(20), s.[Labor No]) as [Labor No], convert(varchar(200), s.[Position]) AS [Artikel / AW-Nr.], convert(varchar(200), s.[Kunde_Verkaufskunde]) as [Kunde_Verkaufskunde], convert(varchar(20), s.[Make Code]) as [Make Code], convert(varchar(20), s.[Location Code]) as [Location Code], convert(decimal(28,8), s.[Amount]) as [Amount], convert(decimal(28,8), s.[Line Discount Amount]) as [Line Discount Amount], convert(decimal(28,8), s.[Quantity]) as [Quantity], convert(decimal(28,8), s.[Unit Cost (lcy)]) as [Unit Cost (lcy)], convert(varchar(20), s.[Source Table]) as [Source Table], convert(varchar(100), p.[Description]) AS [PGRP-Bezeichnung], convert(varchar(20), p.[Zuordnung]) as [Zuordnung], 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], convert(varchar(50), '') AS [Fokus-Gruppe], convert(varchar(50), '') AS [Fokus-Untergruppe], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.') THEN s.[Amount] ELSE 0.0 END) AS [Lohn], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Teile') THEN s.[Amount] ELSE 0.0 END) AS [Teile], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Sonst.') THEN s.[Amount] ELSE 0.0 END) AS [Sonst.], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('TÜV') THEN s.[Amount] ELSE 0.0 END) AS [TÜV], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Fremdl.') THEN s.[Amount] ELSE 0.0 END) AS [Fremdl.], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Mietw.') THEN s.[Amount] ELSE 0.0 END) AS [Mietw.], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.') THEN s.[Line Discount Amount] ELSE 0.0 END) AS [NL Lohn], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Teile') THEN s.[Line Discount Amount] ELSE 0.0 END) AS [NL Teile], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Teile') THEN (s.[Quantity] * s.[Unit Cost (lcy)]) ELSE 0.0 END) AS [EW Teile], convert(decimal(28,8), CASE WHEN p.[Zuordnung] IN ('Fremdl.') THEN (s.[Quantity] * s.[Unit Cost (lcy)]) ELSE 0.0 END) AS [EW Fremdl.], convert(decimal(28,8), CASE WHEN s.[Service Order No] <> 'Gutschrift ohne Auftrag' THEN (1.0 / COUNT(s.[Client_DB]) OVER (PARTITION BY s.[Service Order No])) ELSE 0.0 END) AS [DG], convert(decimal(28,8), ( CASE WHEN p.[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.') THEN s.[Amount] ELSE 0.0 END + CASE WHEN p.[Zuordnung] IN ('Teile') THEN s.[Amount] ELSE 0.0 END + CASE WHEN p.[Zuordnung] IN ('Sonst.') THEN s.[Amount] ELSE 0.0 END + CASE WHEN p.[Zuordnung] IN ('TÜV') THEN s.[Amount] ELSE 0.0 END + CASE WHEN p.[Zuordnung] IN ('Fremdl.') THEN s.[Amount] ELSE 0.0 END + CASE WHEN p.[Zuordnung] IN ('Mietw.') THEN s.[Amount] ELSE 0.0 END )) AS [Gesamt], convert(decimal(28,8), ISNULL(t.[ben. Std. Auftrag], 0)) AS [ben. Std. Auftrag_gesamt], convert(decimal(28,8), ISNULL(t.[ben. Std. Auftrag], 0)) / NULLIF(COUNT(s.[Client_DB]) OVER (PARTITION BY s.[Service Order No]), 0) AS [ben. Std. Auftrag], convert(date, s.[Invoice Date]) AS [Datum], convert(varchar(20), FORMAT(s.[Invoice Date], 'yyyy/MM')) AS [Jahr/Monat], CAST(GETDATE() AS DATE) AS [Aktueller Tag], convert(date, (DATEADD(month, DATEDIFF(month, 0, s.[Invoice Date]), 0))) AS [Monatserster], EOMONTH(s.[Invoice Date]) AS Monatsletzter,     convert(varchar(100),'("' +     CONVERT(varchar, DATEADD(month, DATEDIFF(month, 0, s.[Invoice Date]), 0), 23) +     '","' +     CONVERT(varchar, EOMONTH(s.[Invoice Date]), 23) +     '")') AS MUN_DATE, convert(varchar(50), CONVERT(varchar, DATEADD(month, DATEDIFF(month, 0, s.[Invoice Date]), 0), 112) + '-' + CONVERT(varchar, EOMONTH(s.[Invoice Date]), 112)) AS Mitgliedsname, DATEFROMPARTS(YEAR(s.[Invoice Date]), 1, 1) AS [Beginn Geschäftsjahr], convert(varchar(20), FORMAT(s.[Invoice Date], 'MMM', 'de') + './' + FORMAT(s.[Invoice Date], 'yyyy', 'de')) AS Monat_Jahr, convert(varchar(20), FORMAT(s.[Invoice Date], 'yyyy')) AS [Jahr], convert(varchar(20), FORMAT(s.[Invoice Date], 'MM')) AS [Monat], FORMAT(s.[Invoice Date], 'MMM', 'de-DE') AS [Monat_kurz] FROM [GC_ETL_ARI].[load].[Service_gesamt] s LEFT JOIN [GC_ETL_ARI].[x_data].[Produktbuchungsgruppe] p ON s.[Client_DB] = p.[Client_DB] AND s.[Gen Prod Posting Group] = p.[Gen_ Prod_ Posting Group] LEFT JOIN [GC_ETL_ARI].[x_data].[Marke] m ON s.[Make Code] = m.[Make Code] LEFT JOIN [GC_ETL_ARI].[transform_basis].[Location_Standort] l ON s.[Client_DB] = l.[Client_DB] AND s.[Location Code] = l.[Location Code] LEFT JOIN [GC_ETL_ARI].[temp].[Time_Clock_Entry_fuer_ben_AW_Auftrag] t ON s.[Client_DB] = t.[Client_DB] AND s.[Service Order No] = t.[Service Order No_] LEFT JOIN [GC_ETL_ARI].[transform].[Auftraege_Serviceberater_Mitarbeiter] a ON s.[Client_DB] = a.[Client_DB] AND s.[Service Order No] = a.[No_] LEFT JOIN [GC_ETL_ARI].[transform_basis].[User_Mitarbeiter] u ON s.[Client_DB] = u.[Client_DB] AND s.[User ID] = u.[User ID] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO GO