GAPS/Portal/System/IQD/Zeiten/Stempelzeiten_Monteur_2.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Stempelzeiten_Monteur_2.imr
TITLE,Stempelzeiten_Monteur_2.imr
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   RSUM(c39) as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   c61 as c61,
	   c62 as c62,
	   c63 as c63,
	   c64 as c64,
	   c65 as c65,
	   c66 as c66,
	   c67 as c67,
	   c68 as c68,
	   c69 as c69,
	   c70 as c70,
	   c71 as c71,
	   c72 as c72,
	   c73 as c73,
	   c74 as c74,
	   c75 as c75,
	   c76 as c76,
	   c77 as c77,
	   c78 as c78,
	   c79 as c79,
	   c80 as c80,
	   c81 as c81,
	   c82 as c82,
	   c83 as c83,
	   c84 as c84,
	   c85 as c85,
	   c86 as c86,
	   c87 as c87,
	   c88 as c88,
	   c89 as c89,
	   c90 as c90,
	   c91 as c91,
	   c92 as c92,
	   c93 as c93,
	   c94 as c94,
	   c95 as c95,
	   c96 as c96,
	   c97 as c97,
	   c98 as c98,
	   c99 as c99,
	   c100 as c100,
	   c101 as c101,
	   c102 as c102
from 
(select T1."Entry No_" as c1,
	   T1."Employee No_" as c2,
	   T1."Date" as c3,
	   T1."Time Value" as c4,
	   T1."Begin_End" as c5,
	   T1."Cause of Absence Code" as c6,
	   T1."Terminal Code" as c7,
	   T1."Department Code" as c8,
	   T1."Make Code" as c9,
	   T1."User ID" as c10,
	   T1."Reason Code" as c11,
	   T1."Sorting" as c12,
	   T1."Posting Date" as c13,
	   T1."Order No_" as c14,
	   T1."Service Job No_" as c15,
	   T1."Closed" as c16,
	   T1."Task Type Code" as c17,
	   T1."Link No_" as c18,
	   T1."Task Ledger Entry No_" as c19,
	   T1."Corrected" as c20,
	   T1."Starting Time" as c21,
	   T1."Ending Time" as c22,
	   T1."Duration" as c23,
	   T1."Time Acquisition Posted" as c24,
	   T1."Branch Code" as c25,
	   T1."Approved by User ID" as c26,
	   T1."Approved" as c27,
	   T1."Automatic Posting" as c28,
	   T1."Subject to Approval" as c29,
	   T1."Applied-to Entry No_" as c30,
	   T1."Ledger Entry Origin" as c31,
	   T1."Correction Mode" as c32,
	   T1."Posting Time" as c33,
	   T1."Service Advisor No_" as c34,
	   T1."Resource Group No_" as c35,
	   T1."Tested" as c36,
	   T1."Location Code" as c37,
	   T1."Leave" as c38,
	   (cast_float(T1."Duration")) as c39,
	   T2."Code" as c41,
	   T2."Description" as c42,
	   T2."Task Statistic Group" as c43,
	   '1' as c44,
	   T3."Filialcode" as c45,
	   T4."No_" as c46,
	   T4."Last Name" as c47,
	   T4."First Name" as c48,
	   T4."First Name" || ' ' || T4."Last Name" as c49,
	   0 + (CASE WHEN (T2."Code" IN ('211','212')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('242','244')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('112','113','114')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('216')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('331')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('336')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('245')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('243')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + 0 as c50,
	   T1."Date" as c51,
	   T1."Order No_" as c52,
	   T4."Leaving Date" as c53,
	   T4."Group No_ 1" as c54,
	   T4."Group No_ 2" as c55,
	   T4."Group No_ 3" as c56,
	   T4."Group No_ 1" as c57,
	   (extract(DAY FROM (now()) - T4."Leaving Date")) as c58,
	   CASE WHEN ((((extract(DAY FROM (now()) - T4."Leaving Date"))) > 0) and (T4."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T4."Group No_ 1") END as c59,
	   CASE WHEN (T4."Group No_ 1" IN ('AZUBI 1','AZUBI 2','AZUBI 3','AZUBI 4','HILFSLOHN','MONTEUR')) THEN ('prod. Personal') ELSE ('unprod. Personal') END as c60,
	   (cdatetime(T1."Date" - cinterval(extract(DAY FROM T1."Date") - 1))) as c61,
	   (cdatetime(lastday(cdate(T1."Date")))) as c62,
	   (now()) as c63,
	   CASE WHEN (((now())) BETWEEN ((cdatetime(T1."Date" - cinterval(extract(DAY FROM T1."Date") - 1)))) AND ((cdatetime(lastday(cdate(T1."Date")))))) THEN (T1."Date") ELSE null END as c64,
	   (CASE WHEN (T2."Code" IN ('332')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c65,
	   T4."Employment Date" as c66,
	   CASE WHEN (not T2."Code" IN ('ARZT','KRANKHEIT','PAUSE','URL-SONDER','URLAUB','1/2-URLN','1/2-URLV')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c67,
	   CASE WHEN (T2."Code" IN ('NACHARBEIT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c68,
	   CASE WHEN (T2."Code" IN ('LEERLAUF','WARTEZEIT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c69,
	   0 as c70,
	   0 as c71,
	   CASE WHEN (T2."Code" IN ('W6','SCHUL-INT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c72,
	   CASE WHEN (T2."Code" IN ('SCHUL-EXT','SCHULUNG')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c73,
	   CASE WHEN (T2."Code" IN ('B')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c74,
	   CASE WHEN (T2."Code" IN ('KRANKHEIT','K-MIT LFZ')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c75,
	   CASE WHEN (T2."Code" IN ('ARZT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c76,
	   CASE WHEN (T2."Code" IN ('1/2-URLN','1/2-URLV','URLAUB')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c77,
	   CASE WHEN (T2."Code" IN ('URL-SONDER')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c78,
	   0 as c79,
	   CASE WHEN (T2."Code" IN ('MEISTERV')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c80,
	   CASE WHEN (T2."Code" IN ('SCHULE')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c81,
	   CASE WHEN (T2."Code" IN ('ZA')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c82,
	   CASE WHEN (T2."Code" IN ('ABSCHLEPP')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c83,
	   CASE WHEN (T2."Code" IN ('A','AKARO','ALACK','AMECH','AUFTRAG','CHRYSLER','CITROEN','DIAGNOSE','AELEK')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c84,
	   CASE WHEN (T2."Code" IN ('BGW','BNW','BTUZ','VERWALTUNG')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c85,
	   CASE WHEN (T2."Code" IN ('D')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c86,
	   CASE WHEN (T2."Code" IN ('F')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c87,
	   CASE WHEN (T2."Code" IN ('WERKSTATT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c88,
	   CASE WHEN (T2."Code" IN ('W3')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c89,
	   T3."Global Dimension 1 Code" || ' - ' || T5."Name" as c90,
	   (CASE WHEN (T2."Code" IN ('LEERLAUF','WARTEZEIT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + 0 + 0 + (CASE WHEN (T2."Code" IN ('W6','SCHUL-INT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('B')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('MEISTERV')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('ABSCHLEPP')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('D')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('F')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('WERKSTATT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('W3')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('NACHARBEIT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c91,
	   (CASE WHEN (T2."Code" IN ('SCHUL-EXT','SCHULUNG')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('KRANKHEIT','K-MIT LFZ')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('ARZT')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('1/2-URLN','1/2-URLV','URLAUB')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + (CASE WHEN (T2."Code" IN ('URL-SONDER')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) + 0 + (CASE WHEN (T2."Code" IN ('ZA')) THEN (((cast_float(T1."Duration")))) ELSE (0) END) as c92,
	   0 as c93,
	   0 as c94,
	   0 as c95,
	   T3."Filialcode" as c96,
	   T3."Global Dimension 1 Code" as c97,
	   T5."Name" as c98,
	   CASE WHEN (T3."Filialcode" IN ('00','05')) THEN ('Königsberger Str.') WHEN (T3."Filialcode" IN ('10','15')) THEN ('Kevelaer') WHEN (T3."Filialcode" IN ('20','25')) THEN ('Brunnenstr.') WHEN (T3."Filialcode" IN ('30','35')) THEN ('Weilerswist') WHEN (T3."Filialcode" IN ('50','55')) THEN ('Benrath') WHEN (T3."Filialcode" IN ('40','45')) THEN ('Ratingen') ELSE null END as c99,
	   CASE WHEN (T4."Group No_ 1" IN ('AZUBI 1','AZUBI 2','AZUBI 3','AZUBI 4')) THEN ('AZUBI') WHEN (T4."Group No_ 1" IN ('MONTEUR')) THEN ('Monteur') ELSE ('andere Mitarbeiter') END as c100,
	   T3."Arbeitsvertragscode" as c101,
	   CASE WHEN (T2."Code" IN ('FAHR')) THEN (((cast_float(T1."Duration")))) ELSE (0) END as c102
from (("DE0682"."dbo"."Tretter Bad Bergzabern$Task Acquisition Ledger Entry" T1 left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Task Type" T2 on T1."Task Type Code" = T2."Code") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Employee_T" T4 on T4."No_" = T1."Employee No_"),
	("DE0682"."dbo"."Tretter Bad Bergzabern$Employee" T3 left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Kostenstelle" T5 on T3."Global Dimension 1 Code" = T5."Code")
where (T3."Nr_" = T4."No_")
and (((((((T1."Sorting" = 1) and (T1."Corrected" = 0)) and (T1."Date" >= T4."Employment Date")) and (T1."Date" >= TIMESTAMP '2013-01-01 00:00:00.000')) and (T4."Group No_ 1" IN ('AZUBI 1','AZUBI 2','AZUBI 3','AZUBI 4','HILFSLOHN','MONTEUR'))) and (T3."Global Dimension 1 Code" IN ('40','41','42','62'))))
order by c2 asc,c3 asc
) D1

END SQL
COLUMN,0,Entry No
COLUMN,1,Employee No
COLUMN,2,Date
COLUMN,3,Time Value
COLUMN,4,Begin End
COLUMN,5,Cause Of Absence Code
COLUMN,6,Terminal Code
COLUMN,7,Department Code
COLUMN,8,Make Code
COLUMN,9,User Id
COLUMN,10,Reason Code
COLUMN,11,Sorting
COLUMN,12,Posting Date
COLUMN,13,Order No
COLUMN,14,Service Job No
COLUMN,15,Closed
COLUMN,16,Task Type Code
COLUMN,17,Link No
COLUMN,18,Task Ledger Entry No
COLUMN,19,Corrected
COLUMN,20,Starting Time
COLUMN,21,Ending Time
COLUMN,22,Duration
COLUMN,23,Time Acquisition Posted
COLUMN,24,Branch Code
COLUMN,25,Approved By User Id
COLUMN,26,Approved
COLUMN,27,Automatic Posting
COLUMN,28,Subject To Approval
COLUMN,29,Applied-to Entry No
COLUMN,30,Ledger Entry Origin
COLUMN,31,Correction Mode
COLUMN,32,Posting Time
COLUMN,33,Service Advisor No
COLUMN,34,Resource Group No
COLUMN,35,Tested
COLUMN,36,Location Code
COLUMN,37,Leave
COLUMN,38,Zeitdauer
COLUMN,39,Summe  (Zeitdauer) Nr.1
COLUMN,40,Code
COLUMN,41,Description_Task_Type
COLUMN,42,Task Statistic Group
COLUMN,43,Hauptbetrieb
COLUMN,44,Standort
COLUMN,45,No
COLUMN,46,Last Name
COLUMN,47,First Name
COLUMN,48,Monteur
COLUMN,49,W-variabel Stunden
COLUMN,50,Datum
COLUMN,51,Order Number
COLUMN,52,Leaving Date
COLUMN,53,Group No 1
COLUMN,54,Group No 2
COLUMN,55,Group No 3
COLUMN,56,Monteur_Gruppe_ori
COLUMN,57,Tage Heute Leaving Date
COLUMN,58,Monteur_Gruppe
COLUMN,59,produktiv/unproduktiv
COLUMN,60,Monatserster
COLUMN,61,Monatsletzter
COLUMN,62,Heute
COLUMN,63,Datum Tagesbericht
COLUMN,64,W-fix Stunden
COLUMN,65,Employment Date
COLUMN,66,Anwesenheit
COLUMN,67,W4 - Nacharbeit
COLUMN,68,W2 - Leerlauf
COLUMN,69,UNP - Unproduktiv
COLUMN,70,Hilfslohn
COLUMN,71,W6 - Schulung intern
COLUMN,72,Schulung extern
COLUMN,73,Betriebsrat
COLUMN,74,Krank
COLUMN,75,Arzt
COLUMN,76,Urlaub
COLUMN,77,Sonderurlaub
COLUMN,78,Fehlstunden
COLUMN,79,W8 - Meistervertretung
COLUMN,80,Berufsschule
COLUMN,81,Zeitausgleich
COLUMN,82,Abschleppen
COLUMN,83,produktiv
COLUMN,84,Betrieb NW/GW/TZ
COLUMN,85,Dienstgang
COLUMN,86,Filiale
COLUMN,87,W1 - Eigene Werkstatt
COLUMN,88,W3 - Ausbild. m. Mechaniker
COLUMN,89,Kostenstelle
COLUMN,90,unproduktiv
COLUMN,91,abwesend_ori
COLUMN,92,Extern
COLUMN,93,GWL
COLUMN,94,Intern
COLUMN,95,Filialcode_Employee
COLUMN,96,Kostenstellencode_Employee
COLUMN,97,Name_Kostenstelle
COLUMN,98,Betrieb
COLUMN,99,Monteur_Gruppe_2
COLUMN,100,Arbeitsvertragscode
COLUMN,101,Hol-/Bringdienst