COGNOS QUERY
STRUCTURE,1,1
DATABASE,werwiso
DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\zeit\Verr_ben_Zeit_Monteur.imr
TITLE,Verr_ben_Zeit_Monteur.imr
BEGIN SQL
select T1."Filial_ID" as c1,
	   T1."MonteurNr" as c2,
	   T1."Datum" as c3,
	   T1."Beginn" as c4,
	   T1."VorgangsNummer" as c5,
	   T1."Ende" as c6,
	   T1."Differenz" as c7,
	   T1."AnzAW" as c8,
	   T1."ZeGruppe" as c9,
	   T1."Kommentar" as c10,
	   T1."Status" as c11,
	   T1."Nachgearbeitetfuer" as c12,
	   T1."Nachgearbeitetvon" as c13,
	   T1."KommentarMonteur" as c14,
	   T1."Auto" as c15,
	   T1."ToDoId" as c16,
	   T1."Ende" - T1."Beginn" as c17,
	   (extract(MINUTE FROM (T1."Ende" - T1."Beginn"))) as c18,
	   (extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60 as c19,
	   (extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60 as c20,
	   ((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60) as c21,
	   (((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60 as c22,
	   '1' as c23,
	   T2."Standort_ID2" as c24,
	   T3."Personalart" as c25,
	   T3."Leistungsgruppe" as c26,
	   T3."Leistungsgrad" as c27,
	   CASE WHEN (T3."Personalart" IN ('Monteur                  ')) THEN ('produktiv') ELSE ('unproduktiv') END as c28,
	   'Monteure' as c29,
	   (rtrim(T3."Name")) || ', ' || T3."Vorname" as c30,
	   T1."VorgangsNummer" as c31,
	   'verk. Std.' as c32,
	   '' as c33,
	   CASE WHEN ('verk. Std.' = 'unproduktiv') THEN (T1."Kommentar") ELSE ('') END as c34,
	   '' as c35,
	   T1."AnzAW" as c36,
	   'Monteur' as c37,
	   ((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60) as c38,
	   T2."Standort_Name2" as c39,
	   'Autohaus Müller GmbH' as c40
from QSS."C:\GlobalCube\System\WERWISO\IQD\Serv_Teile\Filialen_GC.ims" T2,
	("\\172.17.171.208:6262\werwiso\WerWisoTabs\Mueller\Mandant.add"."ZEITERF2" T1 left outer join "\\172.17.171.208:6262\werwiso\WerWisoTabs\Mueller\Mandant.add"."personal" T3 on T3."Personalnummer" = T1."MonteurNr")
where (T3."Filial_ID" = (cinteger(nconvert(T2."Standort_ID2"),0)))
and (((((not T1."VorgangsNummer" IN (-1,0)) and (T3."Personalart" IN ('Monteur                  '))) and (T1."AnzAW" IS NOT NULL)) and (T1."Datum" >= DATE '2019-10-01')) and ((T3."Austrittsdatum" IS NULL) or (T3."Austrittsdatum" >= (@CURRENT_DATE))))
order by c2 asc,c3 asc

END SQL
COLUMN,0,Filial Id
COLUMN,1,Monteurnr
COLUMN,2,Datum
COLUMN,3,Beginn
COLUMN,4,Vorgangsnummer
COLUMN,5,Ende
COLUMN,6,Differenz
COLUMN,7,Anzaw
COLUMN,8,Zegruppe
COLUMN,9,Kommentar
COLUMN,10,Status
COLUMN,11,Nachgearbeitetfuer
COLUMN,12,Nachgearbeitetvon
COLUMN,13,Kommentarmonteur
COLUMN,14,Auto
COLUMN,15,Todoid
COLUMN,16,Ende - Beginn
COLUMN,17,Minute_in_Minuten
COLUMN,18,Stunde_in_Minuten
COLUMN,19,Sekunde_in_Minuten
COLUMN,20,Minuten
COLUMN,21,Stunden
COLUMN,22,Hauptbetrieb_ID
COLUMN,23,Standort_ID
COLUMN,24,Personalart
COLUMN,25,Leistungsgruppe
COLUMN,26,Leistungsgrad
COLUMN,27,Monteur_Gruppe
COLUMN,28,Monteur_Gruppe_2
COLUMN,29,Monteur
COLUMN,30,Order Number
COLUMN,31,Activity_Codes_Group_1_
COLUMN,32,Activity_Codes_Group2_
COLUMN,33,Activity_Desc_
COLUMN,34,Kostenstelle
COLUMN,35,verr. Zeit
COLUMN,36,Monteur_Azubi
COLUMN,37,ben. Zeit
COLUMN,38,Standort_Name
COLUMN,39,Hauptbetrieb_Name