COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\belege\fakt_stunden_aus_ims_fuer_fibu.imr
TITLE,fakt_stunden_aus_ims_fuer_fibu.imr
BEGIN SQL
select T1."Service Order No_" as c1,
	   T1."Summe verk.Stunden" as c2,
	   T1."Posting Date" as c3,
	   T2."Employee No_" as c4,
	   T2."Order No_" as c5,
	   T2."Client_DB" as c6,
	   T2."Standort" as c7,
	   T2."No_" as c8,
	   T2."Last Name" as c9,
	   T2."First Name" as c10,
	   T2."Monteur" as c11,
	   T2."Tage Heute Leaving Date" as c12,
	   T2."Monteur_Gruppe" as c13,
	   T2."produktiv/unproduktiv" as c14,
	   T2."Monatserster" as c15,
	   T2."Monatsletzter" as c16,
	   T2."Heute" as c17,
	   T2."Employment Date" as c18,
	   T2."Task Type Group" as c19,
	   T2."Department No_" as c20,
	   T2."Summe produktiv für Auftrag" as c21,
	   T2."Order Number u Monteur" as c22,
	   T2."Summe produktiv Monteur Auftrag" as c23,
	   T2."Anteil Monteur Auftrag" as c24,
	   T1."Summe verk.Stunden" * T2."Anteil Monteur Auftrag" as c25,
	   (cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))) as c26,
	   (cdatetime(lastday(cdate(T1."Posting Date")))) as c27,
	   CASE WHEN (T2."Heute" BETWEEN ((cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1)))) AND ((cdatetime(lastday(cdate(T1."Posting Date")))))) THEN (T1."Posting Date") ELSE null END as c28,
	   T1."Posting Date" as c29,
	   T1."Summe Umsatz Lohn" as c30,
	   T1."Summe Umsatz Lohn Plan" as c31,
	   T1."Summe Umsatz Lohn" * T2."Anteil Monteur Auftrag" as c32,
	   T1."Summe Umsatz Lohn Plan" * T2."Anteil Monteur Auftrag" as c33,
	   T1."Posting Date" as c34,
	   T2."Standort" as c35,
	   'verk. AW Monteur' as c36,
	   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 c37,
	   'SC' as c38,
	   T2."Monteur_Gruppe" as c39,
	   (T1."Summe verk.Stunden" * T2."Anteil Monteur Auftrag") * 12 as c40,
	   T2."Pay Group No_" as c41,
	   T2."Client_DB" as c42,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c43,
	   T2."Standort" as c44,
	   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 c45
from QSS."C:\GlobalCube\System\NAVISION\IQD\Zeiten\Labor_Ledger_entry_Export.ims" T1,
	QSS."C:\GlobalCube\System\NAVISION\IQD\Zeiten\Stempelzeiten_Monteur_Auftrag_Export.ims" T2
where (T1."Service Order No_" = T2."Order No_")
and (T2."produktiv/unproduktiv" = 'prod. Personal')
order by c1 asc

END SQL
COLUMN,0,Service Order No
COLUMN,1,Summe Verk.stunden
COLUMN,2,Posting Date
COLUMN,3,Employee No
COLUMN,4,Order No
COLUMN,5,Hauptbetrieb
COLUMN,6,Standort
COLUMN,7,No
COLUMN,8,Last Name
COLUMN,9,First Name
COLUMN,10,Monteur
COLUMN,11,Tage Heute Leaving Date
COLUMN,12,Monteur Gruppe
COLUMN,13,Produktiv/unproduktiv
COLUMN,14,Monatserster_ori
COLUMN,15,Monatsletzter_ori
COLUMN,16,Heute
COLUMN,17,Employment Date
COLUMN,18,Task Type Group
COLUMN,19,Department No
COLUMN,20,Summe Produktiv Für Auftrag
COLUMN,21,Order Number U Monteur
COLUMN,22,Summe Produktiv Monteur Auftrag
COLUMN,23,Anteil Monteur Auftrag
COLUMN,24,fakt. Stunden
COLUMN,25,Monatserster
COLUMN,26,Monatsletzter
COLUMN,27,Datum Tagesbericht
COLUMN,28,Datum
COLUMN,29,Summe Umsatz Lohn
COLUMN,30,Summe Umsatz Lohn Plan
COLUMN,31,Umsatz Lohn
COLUMN,32,Umsatz Lohn Plan
COLUMN,33,Jahr
COLUMN,34,Betrieb Nr
COLUMN,35,Vstufe 1
COLUMN,36,Zeile mit Bez
COLUMN,37,Bereich
COLUMN,38,Konto
COLUMN,39,verk. AW Monteur
COLUMN,40,Pay Group No
COLUMN,41,Hauptbetrieb_ID
COLUMN,42,Hauptbetrieb_Name
COLUMN,43,Standort_ID
COLUMN,44,Standort_Name