COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\zeiten\fakt_stunden_aus_ims_mit_nachlass.imr
TITLE,fakt_stunden_aus_ims_mit_nachlass.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,
	   CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END 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,
	   T3."Auftrags_Discount_Lohn" as c34,
	   CASE WHEN (T2."Order No_" <> 'WAU17422406') THEN (T3."Auftrags_Discount_Lohn" * T2."Anteil Monteur Auftrag") ELSE (0) END as c35,
	   CASE WHEN ((T2."Monteur" IN ('Martin Braun','Andreas Stein')) and (T1."Posting Date" < TIMESTAMP '2018-10-01 00:00:00.000')) THEN ('raus') ELSE ('rein') END as c36,
	   T2."Client_DB" as c37,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c38,
	   (CASE WHEN (T2."Monteur" = 'Heiko Saage') THEN ('10') ELSE (T2."Standort") END) as c39,
	   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 c40,
	   'fakt. Std.' as c41,
	   'fakt. Std' as c42,
	   'fakt. Std.' as c43
from QSS."C:\GlobalCube\System\NAVISION\IQD\Zeiten\Stempelzeiten_Monteur_Auftrag_Export.ims" T2,
	(QSS."C:\GlobalCube\System\NAVISION\IQD\Zeiten\Labor_Ledger_entry_Export.ims" T1 left outer join QSS."C:\GlobalCube\System\NAVISION\IQD\Serv_Teile\Service_Nachlass_Lohn_Auftrag.ims" 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" >= TIMESTAMP '2021-01-01 00:00:00.000')) and ((CASE WHEN ((T2."Monteur" IN ('Martin Braun','Andreas Stein')) and (T1."Posting Date" < TIMESTAMP '2018-10-01 00:00:00.000')) THEN ('raus') ELSE ('rein') END) = 'rein'))
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,Summe Auftrags Discount Lohn
COLUMN,34,Nachlass
COLUMN,35,Abgrenzung_Günzburg
COLUMN,36,Hauptbetrieb_ID
COLUMN,37,Hauptbetrieb_Name
COLUMN,38,Standort_ID
COLUMN,39,Standort_Name
COLUMN,40,Activity_Codes_Group1
COLUMN,41,Activity_Codes_Group2
COLUMN,42,Activity_Desc