SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform_imr].[Zeit_Stempelungen_Pausen]
AS
SELECT "Employee Number_Employees"
     , "Name_Employees"
     , "Initials_Employees"
     , "Mechanic Number_Employees"
     , "Salesman Number_Employees"
     , "Employment Date_Employees"
     , "Termination Date_Employees"
     , "Leave Date_Employees"
     , "Employee Number_Times"
     , "Order Number_Times"
     , "Start Time_Times"
     , "Type_Times"
     , "Order Positions_Times"
     , "End Time_Times"
     , "Duration Minutes_Times"
     , "Type_Time_Types"
     , "Description_Time_Types"
     , "Start Time_Times" AS "Datum"
     , '1' AS "Hauptbetrieb"
     , "Standort"
     , "Monteur"
     , "Anwesenheit_"
     , "produktiv_"
     , "Anwesenheit_" AS "unproduktiv_"
     , "Zeitkategorie"
     , '' AS "Zeitkategorie2"
     , "Ges. Std._"
     , "Order Number_Times" AS "Order Number"
     , "Wochentag_Datum"
     , "Is Flextime_Employees"
     , "Break Time Registration_Employees"
     , "Is Latest Record_break_times"
     , "Dayofweek_break_times"
     , "Break Start_break_times"
     , "Break End_break_times"
     , "Pause"
     , "Start_Times_Stunde"
     , "Start_Times_Minute"
     , "End_Times_Stunde"
     , "End_Times_Minute"
     , "Start_Times_Minute_Dezimal"
     , "Start_Times_Dezimal"
     , "End_Times_Minute_Dezimal"
     , "End_Times_Dezimal"
     , "Pause" AS "Pausenzeit_Stunde"
     , "Dayofweek_Worktimes"
     , "Work Duration_Worktimes"
     , "Worktime Start_Worktimes"
     , "Worktime End_Worktimes"
     , "Validity Date_Worktimes"
     , "Validity Date_Break_times"
     , "Pausenzeit_Anw_mehr_als_6"
     , "Pausenzeit_Anw_mehr_als_6" AS "Anwesenheit"

FROM (SELECT [T1]."employee_number" AS "Employee Number_Employees"
           , (
        CASE
            WHEN (
                (
                    CASE
                        WHEN ([T3]."type" = 1)
                            THEN ([T2]."duration_minutes" / 60)
                        ELSE (0)
                    END
                    ) >= 6.1
                )
                THEN (([T4]."break_end" - [T4]."break_start") * - 1)
            ELSE (0)
        END
        ) AS "Pausenzeit_Anw_mehr_als_6"
           , [T4]."validity_date" AS "Validity Date_Break_times"
           , [T5]."validity_date" AS "Validity Date_Worktimes"
           , [T5]."worktime_end" AS "Worktime End_Worktimes"
           , [T5]."worktime_start" AS "Worktime Start_Worktimes"
           , [T5]."work_duration" AS "Work Duration_Worktimes"
           , [T5]."dayofweek" AS "Dayofweek_Worktimes"
           , [T4]."break_end" - [T4]."break_start" AS "Pause"
           , ((datepart(HOUR, [T2]."end_time"))) + (0.0 + ((datepart(MINUTE, [T2]."end_time"))) / 60) AS "End_Times_Dezimal"
           , (0.0 + (datepart(MINUTE, [T2]."end_time"))) / 60 AS "End_Times_Minute_Dezimal"
           , ((datepart(HOUR, [T2]."start_time"))) + ((0.0 + (datepart(MINUTE, [T2]."start_time"))) / 60) AS "Start_Times_Dezimal"
           , (0.0 + (datepart(MINUTE, [T2]."start_time"))) / 60 AS "Start_Times_Minute_Dezimal"
           , (datepart(MINUTE, [T2]."end_time")) AS "End_Times_Minute"
           , (datepart(HOUR, [T2]."end_time")) AS "End_Times_Stunde"
           , (datepart(MINUTE, [T2]."start_time")) AS "Start_Times_Minute"
           , (datepart(HOUR, [T2]."start_time")) AS "Start_Times_Stunde"
           , [T4]."break_end" AS "Break End_break_times"
           , [T4]."break_start" AS "Break Start_break_times"
           , [T4]."dayofweek" AS "Dayofweek_break_times"
           , [T4]."is_latest_record" AS "Is Latest Record_break_times"
           , [T1]."break_time_registration" AS "Break Time Registration_Employees"
           , [T1]."is_flextime" AS "Is Flextime_Employees"
           , (datepart(WEEKDAY, [T2]."start_time")) AS "Wochentag_Datum"
           , [T2]."order_number" AS "Order Number_Times"
           , [T2]."duration_minutes" / 60 AS "Ges. Std._"
           , CASE
                 WHEN ([T2]."type" = 2)
                     THEN ('produktiv')
                 ELSE ('unproduktiv')
             END AS "Zeitkategorie"
           , CASE
                 WHEN ([T3]."type" = 1)
                     THEN ((0.0 + [T2]."duration_minutes") / 60)
                 ELSE (0)
             END AS "Anwesenheit_"
           , CASE
                 WHEN ([T3]."type" = 2)
                     THEN ((0.0 + [T2]."duration_minutes") / 60)
                 ELSE (0)
             END AS "produktiv_"
           , (convert(varchar, ((([T1]."employee_number"))))) + ' - ' + [T1]."name" AS "Monteur"
           , ((([T1]."subsidiary"))) AS "Standort"
           , [T2]."start_time" AS "Start Time_Times"
           , [T3]."description" AS "Description_Time_Types"
           , [T3]."type" AS "Type_Time_Types"
           , [T2]."duration_minutes" AS "Duration Minutes_Times"
           , [T2]."end_time" AS "End Time_Times"
           , [T2]."order_positions" AS "Order Positions_Times"
           , [T2]."type" AS "Type_Times"
           , [T2]."employee_number" AS "Employee Number_Times"
           , [T1]."leave_date" AS "Leave Date_Employees"
           , [T1]."termination_date" AS "Termination Date_Employees"
           , [T1]."employment_date" AS "Employment Date_Employees"
           , [T1]."salesman_number" AS "Salesman Number_Employees"
           , [T1]."mechanic_number" AS "Mechanic Number_Employees"
           , [T1]."initials" AS "Initials_Employees"
           , [T1]."name" AS "Name_Employees"
      FROM [LOCOSOFT]."dbo"."employees" [T1]
               INNER JOIN "LOCOSOFT"."dbo"."employees_breaktimes" [T4] ON ([T1]."employee_number" = [T4]."employee_number") AND [T4].[is_latest_record] = 1
               INNER JOIN "LOCOSOFT"."dbo"."employees_worktimes" [T5] ON ([T1]."employee_number" = [T5]."employee_number") AND [T5].[is_latest_record] = 1
               INNER JOIN [LOCOSOFT]."dbo"."times" [T2] ON ([T1]."employee_number" = [T2]."employee_number")
               LEFT JOIN [LOCOSOFT]."dbo"."time_types" [T3] ON [T2]."type" = [T3]."type"
      WHERE ([T2]."type" = 1)
        AND (datepart(WEEKDAY, [T2]."start_time") = [T4]."dayofweek")
        AND (datepart(WEEKDAY, [T2]."start_time") = [T5]."dayofweek")
        AND ([T2]."start_time" >= convert(datetime, '2017-01-01T00:00:00.000'))) [V1]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
