ETL_DWH_LOCOSOFT/schema/GC_ETL_LOCOSOFT/views/test.Aftersales_S03.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [test].[Aftersales_S03] AS
SELECT format([Invoice_Date], 'yyyy-MM') AS [Periode]
     , [Serviceberater]
     , sum([Lohn]) AS [Lohn]
     , sum([Teile]) AS [Teile]
     , sum([Fremdl]) AS [Fremdl]
     , sum([Mietwagen]) AS [Mietwagen]
     , sum([ben_Std]) AS [ben_Std]
     , sum([verk_Std]) AS [verk_Std]
     , sum([DG]) AS [DG]
FROM (SELECT [Invoice_Date]
           , [Hauptbetrieb_ID]
           , [Standort_ID]
           , [Serviceberater]
           , [Lohn_Umsatz] + [NL_Lohn]  AS [Lohn]
           , [Teile_Umsatz] + [NL_Teile] AS [Teile]
           , [Fremdl_] AS [Fremdl]
           , [Mietw_] AS [Mietwagen]
           , 0.0 AS [ben_Std]
           , 0.0 AS [verk_Std]
           , [DG] AS [DG]
      FROM [transform].[Aftersales_Rechnungen_neu]
      UNION ALL
      SELECT [Invoice_Date]
           , [Hauptbetrieb_ID]
           , [Standort_ID]
           , [Serviceberater]
           , 0.0 AS [Lohn]
           , 0.0 AS [Teile]
           , 0.0 AS [Fremdl]
           , 0.0 AS [Mietwagen]
           , convert(decimal(10, 2), [ben__Std_]) AS [ben_Std]
           , 0.0 AS [verk_Std]
           , 0.0 AS [DG]
      FROM [transform].[Aftersales_Rechnungen_ben_AW_final]
      UNION ALL
      SELECT [Invoice_Date]
           , [Hauptbetrieb_ID]
           , [Standort_ID]
           , [Serviceberater]
           , 0.0 AS [Lohn]
           , 0.0 AS [Teile]
           , 0.0 AS [Fremdl]
           , 0.0 AS [Mietwagen]
           , 0.0 AS [ben_Std]
           , convert(decimal(10, 2), [verk__Std_]) AS [verk_Std]
           , 0.0 AS [DG]
      FROM [transform].[Aftersales_Rechnungen_verk_AW_final]) [V1]
WHERE [Invoice_Date] >= datefromparts(year(getdate()), 1, 1)
GROUP BY format([Invoice_Date], 'yyyy-MM'), [Serviceberater]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO