SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW [load].[Aftersales] AS SELECT --TOP(10) convert(varchar(20), [Client_DB]) AS [Client_DB] , convert(varchar(20), [Department Code]) AS [Department Code] , convert(varchar(100), [Auftrags-Nr.]) AS [Auftrags-Nr.] , convert(varchar(20), [Gen Prod Posting Group]) AS [Gen Prod Posting Group] , convert(varchar(100), [Benutzer Abr.]) AS [Benutzer Abr.] , convert(varchar(100), [Serviceberater]) AS [Serviceberater] , convert(varchar(130), [Monteur]) AS [Monteur] , convert(varchar(20), [Umsatzart]) AS [Umsatzart] , convert(varchar(50), [Model]) AS [Model] , convert(varchar(100), [Fahrzeug]) AS [Fahrzeug] , convert(varchar(20), [Marke]) AS [Marke] , convert(varchar(20), [Auftragsart]) AS [Auftragsart] , convert(varchar(50), [Kundenart]) AS [Kundenart] , convert(varchar(200), [Kunde]) AS [Kunde] , convert(datetime, [Invoice Date]) AS [Invoice Date] , convert(decimal(28, 8), [ben. AW]) AS [ben. AW] , convert(decimal(28, 8), [verk. AW]) AS [verk. AW] , convert(varchar(200), [Auftragsposition]) AS [Auftragsposition] , convert(varchar(20), [SB_Gruppe]) AS [SB_Gruppe] , convert(decimal(28, 8), [Rabatt Lohn]) AS [Rabatt Lohn] , convert(decimal(28, 8), [Rabatt Teile]) AS [Rabatt Teile] , convert(varchar(20), [Rg-Nr.]) AS [Rg-Nr.] , convert(varchar(200), [RG/Auftrag/Kunde]) AS [RG/Auftrag/Kunde] , convert(varchar(200), [RG/SB/Kunde/Datum]) AS [RG/SB/Kunde/Datum] , convert(varchar(20), [Labor No]) AS [Labor No] , convert(varchar(200), [Artikel / AW-Nr.]) AS [Artikel / AW-Nr.] , convert(varchar(200), [Kunde_Verkaufskunde]) AS [Kunde_Verkaufskunde] , convert(varchar(20), [Make Code]) AS [Make Code] , convert(varchar(20), [Location Code]) AS [Location Code] , convert(decimal(28, 8), [Amount]) AS [Amount] , convert(decimal(28, 8), [Line Discount Amount]) AS [Line Discount Amount] , convert(decimal(28, 8), [Quantity]) AS [Quantity] , convert(decimal(28, 8), [Unit Cost (lcy)]) AS [Unit Cost (lcy)] , convert(varchar(20), [Source Table]) AS [Source Table] , convert(varchar(100), [PGRP-Bezeichnung]) AS [PGRP-Bezeichnung] , convert(varchar(20), [Zuordnung]) AS [Zuordnung] , convert(varchar(20), [Fabrikat]) AS [Fabrikat] , convert(int, [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] , convert(varchar(50), [Fokus-Gruppe]) AS [Fokus-Gruppe] , convert(varchar(50), [Fokus-Untergruppe]) AS [Fokus-Untergruppe] , convert(decimal(28, 8), [Lohn]) AS [Lohn] , convert(decimal(28, 8), [Teile]) AS [Teile] , convert(decimal(28, 8), [Sonst.]) AS [Sonst.] , convert(decimal(28, 8), [TÜV]) AS [TÜV] , convert(decimal(28, 8), [Fremdl.]) AS [Fremdl.] , convert(decimal(28, 8), [Mietw.]) AS [Mietw.] , convert(decimal(28, 8), [NL Lohn]) AS [NL Lohn] , convert(decimal(28, 8), [NL Teile]) AS [NL Teile] , convert(decimal(28, 8), [EW Teile]) AS [EW Teile] , convert(decimal(28, 8), [EW Fremdl.]) AS [EW Fremdl.] , convert(decimal(28, 8), [DG]) AS [DG] , convert(decimal(28, 8), [Gesamt]) AS [Gesamt] , convert(decimal(28, 8), [ben. Std. Auftrag_gesamt]) AS [ben. Std. Auftrag_gesamt] , convert(decimal(28, 8), [ben. Std. Auftrag]) AS [ben. Std. Auftrag] , convert(date, [Datum]) AS [Datum] , convert(varchar(20), [Jahr/Monat]) AS [Jahr/Monat] , convert(date, [Aktueller Tag]) AS [Aktueller Tag] , convert(date, [Monatserster]) AS [Monatserster] , convert(date, [Monatsletzter]) AS [Monatsletzter] , convert(varchar(100), [MUN_DATE]) AS [MUN_DATE] , convert(varchar(50), [Mitgliedsname]) AS [Mitgliedsname] , convert(date, [Beginn Geschäftsjahr]) AS [Beginn Geschäftsjahr] , convert(varchar(20), [Monat_Jahr]) AS [Monat_Jahr] , convert(varchar(20), [Jahr]) AS [Jahr] , convert(varchar(20), [Monat]) AS [Monat] , convert(varchar(20), [Monat_kurz]) AS [Monat_kurz] FROM (SELECT [s].[Client_DB] AS [Client_DB] , [s].[Department Code] AS [Department Code] , [s].[Service Order No] AS [Auftrags-Nr.] , [s].[Gen Prod Posting Group] AS [Gen Prod Posting Group] -- convert(varchar(100), s.[Serviceberater]) as [Serviceberater_alt], , coalesce([u].[Name], [s].[User Id], 'n.v.') AS [Benutzer Abr.] , iif([s].[Auftragsart] = ('Teile'), [s].[Serviceberater], [a].[Name]) AS [Serviceberater] , left([t].[Monteur_und_Stunden], 130) AS [Monteur] , -- s.[Hauptbetrieb], -- s.[Standort], [s].[Umsatzart] AS [Umsatzart] , [s].[Model] AS [Model] , [s].[Fahrzeug] AS [Fahrzeug] , [s].[Marke] AS [Marke] , [s].[Auftragsart] AS [Auftragsart] , [s].[Kundenart] AS [Kundenart] , [s].[Kunde] AS [Kunde] , -- s.[Umsatz Lohn], -- s.[Umsatz Teile Service], -- s.[Einsatz Teile Service], [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 * (isnull([t].[ben. Std. Auftrag], 0)) / nullif(count([s].[Client_DB]) OVER (PARTITION BY [s].[Service Order No]), 0) AS [ben. AW] , [s].[verk Std] AS [verk. AW] -- s.[DG] AS DG_ALT, , [s].[Auftragsposition] AS [Auftragsposition] , [s].[SB_Gruppe] AS [SB_Gruppe] , [s].[Rabatt Lohn] AS [Rabatt Lohn] , [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], [s].[No] AS [Rg-Nr.] , [s].[No] + ' - ' + [s].[Service Order No] + ' - ' + [s].[Kunde] AS [RG/Auftrag/Kunde] , [s].[No] + ' - ' + [s].[Serviceberater] + ' - ' + [s].[Kunde] + ' - ' + format([s].[Invoice Date], 'dd.MM.yyyy') AS [RG/SB/Kunde/Datum] , [s].[Labor No] AS [Labor No] , [s].[Position] AS [Artikel / AW-Nr.] , [s].[Kunde_Verkaufskunde] AS [Kunde_Verkaufskunde] , [s].[Make Code] AS [Make Code] , [s].[Location Code] AS [Location Code] , [s].[Amount] AS [Amount] , [s].[Line Discount Amount] AS [Line Discount Amount] , [s].[Quantity] AS [Quantity] , [s].[Unit Cost (lcy)] AS [Unit Cost (lcy)] , [s].[Source Table] AS [Source Table] , [p].[Description] AS [PGRP-Bezeichnung] , [p].[Zuordnung] AS [Zuordnung] , isnull([m].[Fabrikat], 'Fremd') AS [Fabrikat] , [m].[Fabrikat_Sortierung] AS [Fabrikat_Sortierung] , [l].[Hauptbetrieb_ID] AS [Hauptbetrieb_ID] , [l].[Hauptbetrieb_Name] AS [Hauptbetrieb_Name] , [l].[Standort_ID] AS [Standort_ID] , [l].[Standort_Name] AS [Standort_Name] , '' AS [Fokus-Gruppe] , '' AS [Fokus-Untergruppe] , iif([p].[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.'), [s].[Amount], 0.0) AS [Lohn] , iif([p].[Zuordnung] IN ('Teile'), [s].[Amount], 0.0) AS [Teile] , iif([p].[Zuordnung] IN ('Sonst.'), [s].[Amount], 0.0) AS [Sonst.] , iif([p].[Zuordnung] IN (N'TÜV'), [s].[Amount], 0.0) AS [TÜV] , iif([p].[Zuordnung] IN ('Fremdl.'), [s].[Amount], 0.0) AS [Fremdl.] , iif([p].[Zuordnung] IN ('Mietw.'), [s].[Amount], 0.0) AS [Mietw.] , iif([p].[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.'), [s].[Line Discount Amount], 0.0) AS [NL Lohn] , iif([p].[Zuordnung] IN ('Teile'), [s].[Line Discount Amount], 0.0) AS [NL Teile] , iif([p].[Zuordnung] IN ('Teile'), ([s].[Quantity] * [s].[Unit Cost (lcy)]), 0.0) AS [EW Teile] , iif([p].[Zuordnung] IN ('Fremdl.'), ([s].[Quantity] * [s].[Unit Cost (lcy)]), 0.0) AS [EW Fremdl.] , iif([s].[Service Order No] <> 'Gutschrift ohne Auftrag', (1.0 / count([s].[Client_DB]) OVER (PARTITION BY [s].[Service Order No])), 0.0) AS [DG] , ( iif([p].[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.'), [s].[Amount], 0.0) + iif([p].[Zuordnung] IN ('Teile'), [s].[Amount], 0.0) + iif([p].[Zuordnung] IN ('Sonst.'), [s].[Amount], 0.0) + iif([p].[Zuordnung] IN (N'TÜV'), [s].[Amount], 0.0) + iif([p].[Zuordnung] IN ('Fremdl.'), [s].[Amount], 0.0) + iif([p].[Zuordnung] IN ('Mietw.'), [s].[Amount], 0.0) ) AS [Gesamt] , isnull([t].[ben. Std. Auftrag], 0) AS [ben. Std. Auftrag_gesamt] , isnull([t].[ben. Std. Auftrag], 0) / nullif(count([s].[Client_DB]) OVER (PARTITION BY [s].[Service Order No]), 0) AS [ben. Std. Auftrag] , [s].[Invoice Date] AS [Datum] , getdate() AS [Aktueller Tag] , [k].[Jahr/Monat] , [k].[Monatserster] , [k].[Monatsletzter] , [k].[MUN_DATE] , [k].[Mitgliedsname] , [k].[Beginn Geschäftsjahr] , [k].[Monat_Jahr] , [k].[Jahr] , [k].[Monat] , [k].[Monat_kurz] FROM [GC_ETL_ARI].[load_2].[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] LEFT JOIN [transform].[Kalender_C11] [k] ON [s].[Invoice Date] = [k].[Datum]) AS [V1] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO GO