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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [test].[Zeit_gesamt_Z03] AS
SELECT format([Invoice_Date], 'yyyy-MM') AS [Periode]
     , [Standort_Name]
     , [Monteur_Gruppe_2]
     , [Monteur]
     , sum([Anw]) AS [Anw]
     , sum([prod]) AS [prod]
     , sum([verr_Zeit]) AS [verr_Zeit]
     , sum([ben_Zeit]) AS [ben_Zeit]
     , sum([Umsatz_Lohn]) AS [Umsatz_Lohn]
     , sum([Abw]) AS [Abw]
     , sum([Sollzeit]) AS [Sollzeit]
     , max([Prod_Faktor]) AS [Prod_Faktor]
FROM (SELECT [Invoice_Date]
           , [Standort_Name]
           , [Monteur_Gruppe_2]
           , [Monteur]
           , 0.0 AS [Anw]
           , 0.0 AS [prod]
           , 0.0 AS [verr_Zeit]
           , 0.0 AS [ben_Zeit]
           , 0.0 AS [Umsatz_Lohn]
           , [Abw_] AS [Abw]
           , 0.0 AS [Sollzeit]
           , [productivity_factor] AS [Prod_Faktor]
      FROM [transform].[Zeit_Abwesenheit_neu]
      UNION ALL
      SELECT [Invoice_Date]
           , [Standort_Name]
           , [Monteur_Gruppe_2]
           , [Monteur]
           , 0.0 AS [Anw]
           , 0.0 AS [prod]
           , 0.0 AS [verr_Zeit]
           , convert(decimal(10, 1), round([ben__Zeit], 1)) AS [ben_Zeit]
           , 0.0 AS [Umsatz_Lohn]
           , 0.0 AS [Abw]
           , 0.0 AS [Sollzeit]
           , [productivity_factor] AS [Prod_Faktor]
      FROM [transform].[Zeit_ben_Std_LG]
      UNION ALL
      SELECT [Invoice Date]
           , [Standort_Name]
           , [Monteur_Gruppe_2]
           , [Monteur]
           , 0.0 AS [Anw]
           , 0.0 AS [prod]
           , 0.0 AS [verr_Zeit]
           , 0.0 AS [ben_Zeit]
           , 0.0 AS [Umsatz_Lohn]
           , 0.0 AS [Abw]
           , [Soll-Zeit] AS [Sollzeit]
           , 0.0 AS [Prod_Faktor]
      FROM [transform].[Zeit_Sollzeit]
      UNION ALL
      SELECT [Invoice_Date]
           , [Standort_Name]
           , [Monteur_Gruppe_2]
           , [Monteur]
           , [Anw_] AS [Anw]
           , [prod_] AS [prod]
           , 0.0 AS [verr_Zeit]
           , 0.0 AS [ben_Zeit]
           , 0.0 AS [Umsatz_Lohn]
           , 0.0 AS [Abw]
           , 0.0 AS [Sollzeit]
           , 0.0 AS [Prod_Faktor]
      FROM [transform].[Zeit_Stempelungen_neu]
      UNION ALL
      SELECT [Invoice_Date]
           , [Standort_Name]
           , [Monteur_Gruppe_2]
           , [Monteur]
           , [Anw_] AS [Anw]
           , 0.0 AS [prod]
           , 0.0 AS [verr_Zeit]
           , 0.0 AS [ben_Zeit]
           , 0.0 AS [Umsatz_Lohn]
           , 0.0 AS [Abw]
           , 0.0 AS [Sollzeit]
           , 0.0 AS [Prod_Faktor]
      FROM [transform].[Zeit_Stempelungen_Pausen]
      UNION ALL
      SELECT[Invoice_Date]
           , [Standort_Name]
           , [Monteur_Gruppe_2]
           , [Monteur]
           , 0.0 AS [Anw]
           , [prod_] AS [prod]
           , 0.0 AS [verr_Zeit]
           , 0.0 AS [ben_Zeit]
           , 0.0 AS [Umsatz_Lohn]
           , 0.0 AS [Abw]
           , 0.0 AS [Sollzeit]
           , 0.0 AS [Prod_Faktor]
      FROM [transform].[Zeit_Stempelungen_Pausen_produktiv]
      UNION ALL
      SELECT [Invoice_Date]
           , [Standort_Name]
           , [Monteur_Gruppe_2]
           , [Monteur]
           , 0.0 AS [Anw]
           , 0.0 AS [prod]
           , convert(decimal(10, 1), round([verr__Zeit], 1)) AS [verr_Zeit]
           , 0.0 AS [ben_Zeit]
           , [Umsatz_Lohn] AS [Umsatz_Lohn]
           , 0.0 AS [Abw]
           , 0.0 AS [Sollzeit]
           , [productivity_factor] AS [Prod_Faktor]
      FROM [transform].[Zeit_verk_Std]) [V1]

WHERE [Invoice_Date] >= datefromparts(year(getdate()), 1, 1)
  AND [Monteur_Gruppe_2] NOT IN ('', 'ausgetr. MA')
  AND [Monteur_Gruppe_2] IS NOT NULL
GROUP BY format([Invoice_Date], 'yyyy-MM'), [Standort_Name], [Monteur_Gruppe_2], [Monteur]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO