C11/System/WERWISO/IQD/Zeit/Ben_Zeit_Monteur.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,werwiso
DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\zeit\Ben_Zeit_Monteur.imr
TITLE,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,
	   T1."Filial_ID" as c24,
	   T2."Personalart" as c25,
	   T2."Leistungsgruppe" as c26,
	   T2."Leistungsgrad" as c27,
	   CASE WHEN (T2."Personalart" IN ('Monteur                  ')) THEN ('produktiv') ELSE ('unproduktiv') END as c28,
	   CASE WHEN (T3."FunktionBez" LIKE 'Mont%') THEN ((od_left(T3."FunktionBez",7))) WHEN (T3."FunktionBez" LIKE 'Azub%') THEN ((od_left(T3."FunktionBez",13))) ELSE ('Monteur') END as c29,
	   (rtrim(T2."Name")) || ', ' || T2."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" / 10 as c36,
	   CASE WHEN (T3."FunktionBez" LIKE 'Mont%') THEN ((od_left(T3."FunktionBez",7))) WHEN (T3."FunktionBez" LIKE 'Azub%') THEN ((od_left(T3."FunktionBez",13))) ELSE ('Monteur') END 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,
	   T4."Name_GC" as c39,
	   (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) * (T2."Leistungsgrad" / 100) as c40,
	   T5."Rechnungsdatum" as c41,
	   T6."Rechnungsdatum" as c42,
	   CASE WHEN (T6."Rechnungsdatum" IS NULL) THEN (T5."Rechnungsdatum") ELSE (T6."Rechnungsdatum") END as c43,
	   CASE WHEN ((CASE WHEN (T6."Rechnungsdatum" IS NULL) THEN (T5."Rechnungsdatum") ELSE (T6."Rechnungsdatum") END) IS NULL) THEN (T1."Datum") ELSE ((CASE WHEN (T6."Rechnungsdatum" IS NULL) THEN (T5."Rechnungsdatum") ELSE (T6."Rechnungsdatum") END)) END as c44
from ((((("\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."ZEITERF2" T1 left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."personal" T2 on T2."Personalnummer" = T1."MonteurNr") left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."PersonalFunktionZuord" T3 on T3."Personalnummer" = T2."Personalnummer") left outer join QSS."C:\GlobalCube\System\WERWISO\IQD\Serv_Teile\Filialen_GC.ims" T4 on T1."Filial_ID" = T4."Filial_Id") left outer join QSS."C:\GlobalCube\System\WERWISO\IQD\Zeit\Inrebuch_Rechnungsdatum_ben_Zeit.ims" T5 on (T1."Filial_ID" = T5."Filial_ID") and (T1."VorgangsNummer" = T5."SplitNr")) left outer join QSS."C:\GlobalCube\System\WERWISO\IQD\Zeit\Reaubuch_Rechnungsdatum_ben_Zeit.ims" T6 on (T1."Filial_ID" = T6."Filial_ID") and (T1."VorgangsNummer" = T6."SplitNr"))
where (((not T1."VorgangsNummer" IN (-1,0)) and (T2."Personalart" IN ('Monteur                  '))) and (T1."AnzAW" IS NOT NULL))
order by c2 asc,c3 asc

END SQL
COLUMN,0,Filial Id
COLUMN,1,Monteurnr
COLUMN,2,Datum_ori
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_alt
COLUMN,36,Monteur_Azubi
COLUMN,37,ben. Zeit_ohne_LG
COLUMN,38,Standort_Name
COLUMN,39,ben. Zeit
COLUMN,40,Rechnungsdatum_interne_Rg
COLUMN,41,Rechnungsdatum_externe_Rg
COLUMN,42,Datum_Rechnungsausgangsbücher
COLUMN,43,Datum