C11/System/NAVISION/IQD/Belege/fakt_stunden_aus_ims_fuer_fibu.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",
	   T2."Standort" 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",
	   T1."Posting Date" as "Jahr",
	   T2."Standort" as "Betrieb Nr",
	   'verk. AW Monteur' as "Vstufe 1",
	   CASE WHEN (T2."Monteur_Gruppe" IN ('WMOM','WMOK','WSL','WMOE','WMOL')) THEN ('Monteure') WHEN ((T2."Monteur_Gruppe" IN ('AZG3','AZG2','AZG4'))) THEN (('AZG 2. - 4. LJ')) WHEN (T2."Monteur_Gruppe" IN ('AZG1')) THEN ('AZG 1. LJ') WHEN ((T2."Monteur_Gruppe" IN ('ausgetretene Mitarbeiter')) and (T2."Pay Group No_" IN ('WMOM','WMOE','WSL','WMOK','WMOL'))) THEN ('ausgetr. Monteure') WHEN ((T2."Monteur_Gruppe" IN ('ausgetretene Mitarbeiter')) and (T2."Pay Group No_" IN ('AZG4','AZG3','AZG2','AZG1'))) THEN ('ausgetr. AZUBI') ELSE ('Monteure') END as "Zeile mit Bez",
	   'SC' as "Bereich",
	   T2."Monteur_Gruppe" as "Konto",
	   (T1."Summe verk.Stunden" * T2."Anteil Monteur Auftrag") * 12 as "verk. AW Monteur",
	   T2."Pay Group No_" as "Pay Group No",
	   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",
	   T2."Standort" as "Standort_ID",
	   CASE WHEN (T2."Standort" IN ('10')) THEN ('MM') WHEN (T2."Standort" IN ('30')) THEN ('KRU') WHEN (T2."Standort" IN ('40')) THEN ('ULM') WHEN (T2."Standort" IN ('50')) THEN ('LL') WHEN (T2."Standort" IN ('55')) THEN ('GZ') WHEN (T2."Standort" IN ('60')) THEN ('AAM') WHEN (T2."Standort" IN ('70')) THEN ('LEH') WHEN (T2."Standort" IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name"
from "ims"."Labor_Ledger_entry_Export" T1,
	"ims"."Stempelzeiten_Monteur_Auftrag_Export" T2
where (T1."Service Order No_" = T2."Order No_")
and (T2."produktiv/unproduktiv" = 'prod. Personal')
-- order by "Service Order No" asc