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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [load].[Zeit_2_Ryma]
AS
SELECT z.[Client_DB]
	  ,z.[Employee No]
      ,z.[Datum]
      ,z.[Time Account No] AS [Stempel-Code]
      ,z.[Zeitdauer]
      ,z.[Order Number]
      ,z.[Monteur]
      ,z.[Employment Date]
      ,z.[Department No]
      ,z.[Sollzeit]
	  ,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] = 'W-fix Std.') THEN (z.[Zeitdauer]) ELSE (0) END AS [abwesend]
FROM [transform].[Zeit_2_Ryma] z
LEFT JOIN [transform_basis].[Department_Standort] d
    ON z.[Client_DB] = d.[Client_DB]
	AND z.[Department No] = d.[Department No_]
LEFT JOIN [x_data].[Time_Account] t
    ON z.[Client_DB] = t.[Client_DB]
	AND z.[Time Account No] = t.[Time Account No_]
LEFT JOIN [x_data].[Mitarbeiter] m
    ON z.[Client_DB] = m.[Client_DB]
	AND z.[Employee No] = m.[Employee No_]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO