C11/System/ARI/IQD/Zeit/Time_Clock_Entry.sql

select T1."Entry No_" as "Entry No",
	   T1."Employee No_" as "Employee No",
	   T1."Resource No_" as "Resource No",
	   T1."Address No_" as "Address No",
	   T1."Date" as "Date",
	   T1."Time" as "Time",
	   T1."Sorting" as "Sorting",
	   (convert(float, T1."Duration")) as "Duration",
	   T1."Task Type Code" as "Task Type Code",
	   T1."Type" as "Type",
	   T1."Linked to Entry No_" as "Linked To Entry No",
	   T1."Leaving" as "Leaving",
	   T1."Service Order No_" as "Service Order No",
	   T1."Service Job No_" as "Service Job No",
	   T1."Service Line No_" as "Service Line No",
	   T1."Labor Standard Time Type" as "Labor Standard Time Type",
	   T1."Approved" as "Approved",
	   T1."Approved by Employee No_" as "Approved By Employee No",
	   T1."Approval Timestamp" as "Approval Timestamp",
	   T1."Canceled" as "Canceled",
	   T1."Canceled by User ID" as "Canceled By User Id",
	   T1."Cancelation Timestamp" as "Cancelation Timestamp",
	   T1."Created by User ID" as "Created By User Id",
	   T1."Creation Timestamp" as "Creation Timestamp",
	   T1."Productive" as "Productive",
	   T1."Statistics Group" as "Statistics Group",
	   T1."Considered as Working" as "Considered As Working",
	   T1."Order Location Code" as "Order Location Code",
	   T1."Resource Location Code" as "Resource Location Code",
	   T1."Make Code" as "Make Code",
	   T1."Branch Code" as "Branch Code",
	   T1."Labor No_" as "Labor No",
	   T1."Description" as "Description",
	   T1."Source Code" as "Source Code",
	   CASE WHEN (((convert(float, T1."Efficiency _")) = 0) and (T2."Efficiency" <> 0)) THEN (T2."Efficiency") ELSE ((convert(float, T1."Efficiency _"))) END as "Efficiency",
	   T1."Automatic" as "Automatic",
	   T1."Passed" as "Passed",
	   T3."CODE" as "Code_Task_Type",
	   T3."DESCRIPTION" as "Description_Task_Type",
	   T4."No_" as "No_Employee_T",
	   T4."Name" as "Name_Employee_T",
	   T4."Last Name" as "Last Name_Employee_T",
	   T4."First Name" as "First Name_Employee_T",
	   T4."Group No_ 1" as "Group No 1_Employee_T",
	   T4."Group No_ 2" as "Group No 2_Employee_T",
	   T4."Group No_ 3" as "Group No 3_Employee_T",
	   T4."Leaving Date" as "Leaving Date_Employee_T",
	   CASE WHEN (T5."Task Type Group" IN ('LEHRLINGE','MEISTER','MONTEURE')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as "produktiv/unproduktiv",
	   CASE WHEN (T4."Last Name" = 'Silberbauer') THEN ('Leonhard Silberbauer') WHEN (T4."Last Name" = 'Liebick') THEN ('Lukas Jonathan Josef Liebick') ELSE (T4."First Name" + ' ' + T4."Last Name") END as "Monteur",
	   T5."Function Code" as "Monteur_Gruppe_ori",
	   (day((now()) - T4."Leaving Date")) as "Tage Heute Leaving Date",
	   CASE WHEN ((T4."Leaving Date" < (now())) and (T4."Leaving Date" <> convert(datetime, '1753-01-01 00:00:00.000'))) THEN ('ausgetretene Mitarbeiter') ELSE (T5."Function Code") END as "Monteur_Gruppe_2",
	   ((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END)) as "Datum",
	   (convert(datetime, (((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END))) - cinterval(day((((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END)))) - 1))) as "Monatserster",
	   (convert(datetime, lastday(((((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END))))))) as "Monatsletzter",
	   (now()) - INTERVAL '001 10:00:00.000' as "Heute",
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((convert(datetime, (((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END))) - cinterval(day((((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END)))) - 1)))) AND ((convert(datetime, lastday(((((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END))))))))) THEN ((((CASE WHEN ((T1."Employee No_" IN ('0382','0378')) and (T1."Date" <= convert(datetime, '2019-02-22 00:00:00.000'))) THEN (convert(date, '1900-01-01')) ELSE (T1."Date") END)))) ELSE null END as "Datum Tagesbericht",
	   '1' as "Hauptbetrieb_ID",
	   CASE WHEN ((left(T4."Department No_",2)) = '10') THEN ('LBS') WHEN ((left(T4."Department No_",2)) = '20') THEN ('WLS') ELSE null END as "Standort",
	   CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((convert(float, T1."Duration")))) ELSE (0) END as "prod.",
	   CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'W-var. Std.') THEN (((convert(float, T1."Duration")))) ELSE (0) END as "unprod.",
	   CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'W-fix Std.') THEN (((convert(float, T1."Duration")))) ELSE (0) END as "Abw.",
	   (CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((convert(float, T1."Duration")))) ELSE (0) END) * ((CASE WHEN (((convert(float, T1."Efficiency _")) = 0) and (T2."Efficiency" <> 0)) THEN (T2."Efficiency") ELSE ((convert(float, T1."Efficiency _"))) END) / 100) as "produktiv_für_Berechnung_LG",
	   CASE WHEN (T3."ACTIVITY_DESC" IN ('243 - Servicemobil')) THEN (((convert(float, T1."Duration")))) ELSE (0) END as "Servicemobil",
	   CASE WHEN (T3."ACTIVITY_DESC" = '319 - Krankheit') THEN (((convert(float, T1."Duration")))) ELSE (0) END as "krank",
	   CASE WHEN (T3."ACTIVITY_DESC" IN ('242 - Meistervertretung Werkstatt','244 - Meistervertretung Serviceberater')) THEN (((convert(float, T1."Duration")))) ELSE (0) END as "Meistervertr.",
	   T3."ACTIVITY_DESC" as "Activity_Desc",
	   CASE WHEN ((left(T4."Department No_",2)) = '10') THEN ('10') WHEN ((left(T4."Department No_",2)) = '20') THEN ('20') ELSE null END as "Standort_ID",
	   (CASE WHEN (T5."Task Type Group" IN ('LEHRLINGE','MEISTER','MONTEURE')) THEN ('prod. Personal') ELSE ('unprod. Personal') END) as "Monteur_Gruppe",
	   T4."Department No_" as "Department No",
	   T1."Service Order No_" as "Order Number",
	   T5."Task Type Group" as "Task Type Group",
	   CASE WHEN (T3."ACTIVITY_DESC" = '111 - Auftrag extern AW') THEN ((CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((convert(float, T1."Duration")))) ELSE (0) END)) ELSE (0) END as "Extern",
	   CASE WHEN (T3."ACTIVITY_DESC" = '150 - Auftrag intern AW') THEN ((CASE WHEN (T3."ACTIVITY_CODES_GROUP1" = 'prod.') THEN (((convert(float, T1."Duration")))) ELSE (0) END)) ELSE (0) END as "Intern"
from "ims"."Resource_ims" T2,
	((("Vogl7x"."dbo"."BMW AH Vogl$Time Clock Entry" T1 left outer join "ims"."Zuordnung_Task_Type" T3 on T3."CODE" = T1."Task Type Code") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee_T" T4 on T1."Employee No_" = T4."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T5 on T1."Resource No_" = T5."No_")
where (T1."Resource No_" = T2."No_")
and (((((T1."Sorting" = 0) and (T1."Canceled" = 0)) and (T1."Date" >= convert(datetime, '2020-01-01 00:00:00.000'))) and ((CASE WHEN (T5."Task Type Group" IN ('LEHRLINGE','MEISTER','MONTEURE')) THEN ('prod. Personal') ELSE ('unprod. Personal') END) = 'prod. Personal')) and (not T3."CODE" IN ('312')))
-- order by "Resource No" asc,"Date" asc