C11/System/ARI/SQL/schema/GC_ETL_ARI/views/load.Zeit_gesamt.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[Zeit_gesamt] AS

-- 1. From Time_Clock_Entry
SELECT 
    c.[Client_DB],
    c.[Employee No] AS [No],
    '' AS [Document No],
    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],
    0 AS [fakt. Std.],
    0 AS [Umsatz Lohn],
    0 AS [Nachlass],
    0 AS [ben. Std.],
	0 as [Sollzeit],
    'Time_Clock_Entry' AS [Source Table]
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_]

UNION ALL

-- 2. From Zeit_2_Ryma
SELECT 
    z.[Client_DB],
    z.[Employee No] AS [No],
    '' AS [Document No],
    z.[Zeitdauer],
    z.[Time Account No] AS [Stempel-Code],
    z.[Monteur],
    z.[Datum],
    z.[Department No],
    z.[Order Number],
    z.[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],
    0 AS [prod.],
    0 AS [unprod.],
    CASE WHEN t.[Activity_Codes_Group1] = 'W-fix Std.' THEN z.[Zeitdauer] ELSE 0 END AS [Abw.],
    0 AS [Extern],
    0 AS [Intern],
    0 AS [fakt. Std.],
    0 AS [Umsatz Lohn],
    0 AS [Nachlass],
    0 AS [ben. Std.],
	z.[Sollzeit],
    'Zeit_2_Ryma' AS [Source Table]
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_]

UNION ALL

-- 3. From Add_Serv_Ledger_Entry_fakt_AW_Monteur
SELECT 
    a.[Client_DB],
    a.[No],
    a.[Document No],
    0 AS [Zeitdauer],
    'fakt. Std.' AS [Stempel-Code],
    a.[Monteur],
    a.[Datum],
    a.[Department No],
    a.[Order Number],
    a.[Employment Date],
    d.[Hauptbetrieb_ID],
    d.[Hauptbetrieb_Name],
    d.[Standort_ID],
    d.[Standort_Name],
    'fakt. Std.' AS [Activity_Codes_Group1],
    'fakt. Std.' AS [Activity_Codes_Group2],
    'fakt. Std.' AS [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],
    0 AS [prod.],
    0 AS [unprod.],
    0 AS [Abw.],
    0 AS [Extern],
    0 AS [Intern],
    a.[fakt Stunden] AS [fakt. Std.],
    a.[Umsatz Lohn],
    a.[Nachlass],
    0 AS [ben. Std.],
	0 as [Sollzeit],
    'Add_Serv_Ledger_Entry_fakt_AW_Monteur' AS [Source Table]
FROM [transform].[Add_Serv_Ledger_Entry_fakt_AW_Monteur] a
LEFT JOIN [transform_basis].[Department_Standort] d ON a.[Client_DB] = d.[Client_DB] AND a.[Department No] = d.[Department No_]
LEFT JOIN [x_data].[Mitarbeiter] m ON a.[Client_DB] = m.[Client_DB] AND a.[No] = m.[Employee No_]

UNION ALL

-- 4. From Stempelzeiten_Monteurlisten_aus_ims
SELECT 
    s.[Client_DB],
    s.[No],
    '' AS [Document No],
    0 AS [Zeitdauer],
    'ben. Std' AS [Stempel-Code],
    s.[Monteur],
    s.[Datum],
    s.[Department No],
    s.[Order Number],
    s.[Employment Date],
    d.[Hauptbetrieb_ID],
    d.[Hauptbetrieb_Name],
    d.[Standort_ID],
    d.[Standort_Name],
    'ben. Std.' AS [Activity_Codes_Group1],
    'ben. Std.' AS [Activity_Codes_Group2],
    'ben. Std.' AS [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],
    0 AS [prod.],
    0 AS [unprod.],
    0 AS [Abw.],
    0 AS [Extern],
    0 AS [Intern],
    0 AS [fakt. Std.],
    0 AS [Umsatz Lohn],
    0 AS [Nachlass],
    s.[Summe Produktiv Monteur Auftrag] AS [ben. Std.],
	0 as [Sollzeit],
    'Stempelzeiten_Monteurlisten_aus_ims' AS [Source Table]
FROM [transform].[Stempelzeiten_Monteurlisten_aus_ims] s
LEFT JOIN [transform_basis].[Department_Standort] d ON s.[Client_DB] = d.[Client_DB] AND s.[Department No] = d.[Department No_]
LEFT JOIN [x_data].[Mitarbeiter] m ON s.[Client_DB] = m.[Client_DB] AND s.[No] = m.[Employee No_]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO