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

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