C11/System/NAVISION/IQD/Zeiten/fakt_stunden_aus_ims_mit_nachlass.sql

select T1."Service Order No_" as "Service Order No",
	   T1."Summe verk.Stunden" as "Summe Verk.stunden",
	   T1."Posting Date" as "Posting Date",
	   T2."Employee No_" as "Employee No",
	   T2."Order No_" as "Order No",
	   T2."Client_DB" as "Hauptbetrieb",
	   CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END as "Standort",
	   T2."No_" as "No",
	   T2."Last Name" as "Last Name",
	   T2."First Name" as "First Name",
	   T2."Monteur" as "Monteur",
	   T2."Tage Heute Leaving Date" as "Tage Heute Leaving Date",
	   T2."Monteur_Gruppe" as "Monteur Gruppe",
	   T2."produktiv/unproduktiv" as "Produktiv/unproduktiv",
	   T2."Monatserster" as "Monatserster_ori",
	   T2."Monatsletzter" as "Monatsletzter_ori",
	   T2."Heute" as "Heute",
	   T2."Employment Date" as "Employment Date",
	   T2."Task Type Group" as "Task Type Group",
	   T2."Department No_" as "Department No",
	   T2."Summe produktiv für Auftrag" as "Summe Produktiv Für Auftrag",
	   T2."Order Number u Monteur" as "Order Number U Monteur",
	   T2."Summe produktiv Monteur Auftrag" as "Summe Produktiv Monteur Auftrag",
	   T2."Anteil Monteur Auftrag" as "Anteil Monteur Auftrag",
	   T1."Summe verk.Stunden" * T2."Anteil Monteur Auftrag" as "fakt. Stunden",
	   (convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))) as "Monatserster",
	   (convert(datetime, eomonth((T1."Posting Date")))) as "Monatsletzter",
	   CASE WHEN (T2."Heute" BETWEEN ((convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1)))) AND ((convert(datetime, eomonth((T1."Posting Date")))))) THEN (T1."Posting Date") ELSE null END as "Datum Tagesbericht",
	   T1."Posting Date" as "Datum",
	   T1."Summe Umsatz Lohn" as "Summe Umsatz Lohn",
	   T1."Summe Umsatz Lohn Plan" as "Summe Umsatz Lohn Plan",
	   T1."Summe Umsatz Lohn" * T2."Anteil Monteur Auftrag" as "Umsatz Lohn",
	   T1."Summe Umsatz Lohn Plan" * T2."Anteil Monteur Auftrag" as "Umsatz Lohn Plan",
	   T3."Auftrags_Discount_Lohn" as "Summe Auftrags Discount Lohn",
	   CASE WHEN (T2."Order No_" <> 'WAU17422406') THEN (T3."Auftrags_Discount_Lohn" * T2."Anteil Monteur Auftrag") ELSE (0) END as "Nachlass",
	   CASE WHEN ((T2."Monteur" IN ('Martin Braun','Andreas Stein')) and (T1."Posting Date" < convert(datetime, '2018-10-01 00:00:00.000'))) THEN ('raus') ELSE ('rein') END as "Abgrenzung_Günzburg",
	   T2."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   (CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END) as "Standort_ID",
	   CASE WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name",
	   'fakt. Std.' as "Activity_Codes_Group1",
	   'fakt. Std' as "Activity_Codes_Group2",
	   'fakt. Std.' as "Activity_Desc"
from "ims"."Stempelzeiten_Monteur_Auftrag_Export" T2,
	("ims"."Labor_Ledger_entry_Export" T1 left outer join "ims"."Service_Nachlass_Lohn_Auftrag" T3 on T1."Service Order No_" = T3."Document No_")
where (T1."Service Order No_" = T2."Order No_")
and (((T2."Employee No_" <> '65503') and (T1."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and ((CASE WHEN ((T2."Monteur" IN ('Martin Braun','Andreas Stein')) and (T1."Posting Date" < convert(datetime, '2018-10-01 00:00:00.000'))) THEN ('raus') ELSE ('rein') END) = 'rein'))
-- order by "Service Order No" asc