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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [load].[Time_Clock_Entry]
AS
SELECT c.[Client_DB]
      ,c.[Employee No]
      ,c.[Resource No]
      ,c.[Sorting]
      ,c.[Duration] AS [Zeitdauer]
      ,c.[Task Type Code] AS [Stempel-Code]
      ,c.[Monteur]
      ,c.[Datum]
      ,c.[Department No]
      ,c.[Order Number]
	  ,c.[Employment Date]
	  ,d.[Hauptbetrieb_ID]
	  ,d.[Hauptbetrieb_Name]
	  ,d.[Standort_ID]
	  ,d.[Standort_Name]
	  ,t.[Activity_Codes_Group1]
	  ,t.[Activity_Codes_Group2]
	  ,t.[Activity_Desc]
	  ,m.[First Name]
	  ,m.[Last Name]
	  ,m.[Task Type Group]
	  ,m.[Function Code]
	  ,m.[Group No_ 1]
	  ,m.[Group No_ 2]
	  ,m.[Group No_ 3]
	  ,m.[Monteur_Gruppe]
	  ,m.[Monteur_Gruppe_2]
	  ,CASE WHEN (t.[Activity_Codes_Group1] = 'prod.') THEN (c.[Duration]) ELSE (0) END AS [prod.]
	  ,CASE WHEN (t.[Activity_Codes_Group1] = 'W-var. Std.') THEN (c.[Duration]) ELSE (0) END AS [unprod.]
      ,CASE WHEN (t.[Activity_Codes_Group1] = 'W-fix Std.') THEN (c.[Duration]) ELSE (0) END AS [Abw.]
      ,CASE WHEN (t.[Activity_Codes_Group2] = 'extern') THEN (c.[Duration]) ELSE (0) END AS [Extern]
      ,CASE WHEN (t.[Activity_Codes_Group2] = 'intern') THEN (c.[Duration]) ELSE (0) END AS [Intern]
FROM [transform].[Time_Clock_Entry] c
LEFT JOIN [transform_basis].[Department_Standort] d
    ON c.[Client_DB] = d.[Client_DB]
	AND c.[Department No] = d.[Department No_]
LEFT JOIN [x_data].[Task_Type] t
    ON c.[Client_DB] = t.[Client_DB]
	AND c.[Task Type Code] = t.[Task Type Code]
LEFT JOIN [x_data].[Mitarbeiter] m
    ON c.[Client_DB] = m.[Client_DB]
	AND c.[Employee No] = m.[Employee No_]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO