Página inicial Explorar Ajuda
Registrar Entrar
GlobalCube
/
C11
3
0
Fork 0
Arquivos Issues 0 Pull Requests 0 Wiki
Tree: 36bc3eba40
Branches Tags
C11
/
System
/
WERWISO
/
IQD
/
Zeit
/
Stempelzeiten_Monteur.iqd

Stempelzeiten_Monteur.iqd 20 KB
Histórico Raw

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128 
  1. COGNOS QUERY
    2. 

  2. STRUCTURE,1,1
    3. 

  3. DATABASE,werwiso
    4. 

  4. DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\zeit\Stempelzeiten_Monteur.imr
    5. 

  5. TITLE,Stempelzeiten_Monteur.imr
    6. 

  6. BEGIN SQL
    7. 

  7. select T1."Filial_ID" as c1,
    8. 

  8. 	   T1."MonteurNr" as c2,
    9. 

  9. 	   T1."Datum" as c3,
    10. 

  10. 	   T1."Beginn" as c4,
    11. 

  11. 	   T1."VorgangsNummer" as c5,
    12. 

  12. 	   T1."Ende" as c6,
    13. 

  13. 	   T1."Differenz" as c7,
    14. 

  14. 	   T1."AnzAW" as c8,
    15. 

  15. 	   T1."ZeGruppe" as c9,
    16. 

  16. 	   T1."Kommentar" as c10,
    17. 

  17. 	   T1."Status" as c11,
    18. 

  18. 	   T1."Nachgearbeitetfuer" as c12,
    19. 

  19. 	   T1."Nachgearbeitetvon" as c13,
    20. 

  20. 	   T1."KommentarMonteur" as c14,
    21. 

  21. 	   T1."Auto" as c15,
    22. 

  22. 	   T1."ToDoId" as c16,
    23. 

  23. 	   T2."ModellID" as c17,
    24. 

  24. 	   T2."Modellbezeichnung" as c18,
    25. 

  25. 	   T2."ModellKuerzel" as c19,
    26. 

  26. 	   T1."Ende" - T1."Beginn" as c20,
    27. 

  27. 	   (extract(MINUTE FROM (T1."Ende" - T1."Beginn"))) as c21,
    28. 

  28. 	   (extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60 as c22,
    29. 

  29. 	   (extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60 as c23,
    30. 

  30. 	   ((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60) as c24,
    31. 

  31. 	   (((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 c25,
    32. 

  32. 	   '1' as c26,
    33. 

  33. 	   T3."Standort_ID2" as c27,
    34. 

  34. 	   T4."Personalart" as c28,
    35. 

  35. 	   T4."Leistungsgruppe" as c29,
    36. 

  36. 	   T4."Leistungsgrad" as c30,
    37. 

  37. 	   CASE WHEN (T4."Personalart" IN ('Monteur                  ')) THEN ('produktiv') ELSE ('unproduktiv') END as c31,
    38. 

  38. 	   'Monteure' as c32,
    39. 

  39. 	   (rtrim(T4."Name")) || ', ' || T4."Vorname" as c33,
    40. 

  40. 	   T1."VorgangsNummer" as c34,
    41. 

  41. 	   CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END as c35,
    42. 

  42. 	   CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (((ltrim(T5."Abwesenheitsart")))) WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (T1."Status") WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'abwesend') THEN (T2."ModellKuerzel" || ' - ' || T2."Modellbezeichnung") ELSE null END as c36,
    43. 

  43. 	   (ltrim(T5."Abwesenheitsart")) as c37,
    44. 

  44. 	   CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (T1."Kommentar") ELSE ((CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (((ltrim(T5."Abwesenheitsart")))) WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (T1."Status") WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'abwesend') THEN (T2."ModellKuerzel" || ' - ' || T2."Modellbezeichnung") ELSE null END)) END as c38,
    45. 

  45. 	   '' as c39,
    46. 

  46. 	   CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END as c40,
    47. 

  47. 	   CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END as c41,
    48. 

  48. 	   CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'abwesend') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END as c42,
    49. 

  49. 	   0 as c43,
    50. 

  50. 	   0 as c44,
    51. 

  51. 	   0 as c45,
    52. 

  52. 	   0 as c46,
    53. 

  53. 	   'Monteur' as c47,
    54. 

  54. 	   T3."Standort_Name2" as c48,
    55. 

  55. 	   CASE WHEN ((CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (T1."Kommentar") ELSE ((CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (((ltrim(T5."Abwesenheitsart")))) WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (T1."Status") WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'abwesend') THEN (T2."ModellKuerzel" || ' - ' || T2."Modellbezeichnung") ELSE null END)) END) = 'K  - Krank                                             ') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END as c49,
    56. 

  56. 	   (CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END) + (CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END) + (CASE WHEN ((CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (T1."Kommentar") ELSE ((CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (((ltrim(T5."Abwesenheitsart")))) WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (T1."Status") WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'abwesend') THEN (T2."ModellKuerzel" || ' - ' || T2."Modellbezeichnung") ELSE null END)) END) = 'K  - Krank                                             ') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END) as c50,
    57. 

  57. 	   CASE WHEN (T1."VorgangsNummer" = 0) THEN ((round(((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60),2,0))) ELSE (0) END as c51,
    58. 

  58. 	   CASE WHEN (T1."VorgangsNummer" = -1) THEN ((round(((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60),2,0))) ELSE (0) END as c52,
    59. 

  59. 	   (CASE WHEN (T1."VorgangsNummer" = 0) THEN ((round(((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60),2,0))) ELSE (0) END) - (CASE WHEN (T1."VorgangsNummer" = -1) THEN ((round(((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60),2,0))) ELSE (0) END) as c53,
    60. 

  60. 	   (rtrim(((rtrim(T4."Name")) || ', ' || T4."Vorname"))) || '_' || (asciiz(extract(YEAR FROM T1."Datum"),4) || '-' || asciiz(extract(MONTH FROM T1."Datum"),2) || '-' || asciiz(extract(DAY FROM T1."Datum"),2)) as c54,
    61. 

  61. 	   (CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END) + (CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'unproduktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END) as c55,
    62. 

  62. 	   CASE WHEN (((CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END) <> 0) and (T1."AnzAW" IS NULL)) THEN ((CASE WHEN ((CASE WHEN (T1."Status" IN ('Auftrag             ','AuftragEnde         ')) THEN ('produktiv') WHEN (T1."Status" IN ('Unproduktiv         ','UnproduktivEnde     ')) THEN ('unproduktiv') WHEN (T1."Status" IN ('Krank               ','Überstundenausgleich')) THEN ('abwesend') WHEN (T2."ModellID" IN (-7,-5,-4,-3,-2)) THEN ('abwesend') WHEN (T2."ModellID" IN (8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) THEN ('abwesend') ELSE null END) = 'produktiv') THEN (((((extract(MINUTE FROM (T1."Ende" - T1."Beginn")))) + ((extract(HOUR FROM (T1."Ende" - T1."Beginn"))) * 60) + ((extract(SECOND FROM (T1."Ende" - T1."Beginn"))) / 60)) / 60)) ELSE (0) END)) ELSE (0) END as c56,
    63. 

  63. 	   'Autohaus Müller GmbH' as c57
    64. 

  64. from QSS."C:\GlobalCube\System\WERWISO\IQD\Serv_Teile\Filialen_GC.ims" T3,
    65. 

  65. 	((("\\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"."Zeitmodelle" T2 on (T1."Filial_ID" = T2."FilialID") and (T1."VorgangsNummer" = T2."ModellID")) left outer join "\\172.17.171.208:6262\werwiso\WerWisoTabs\Mueller\Mandant.add"."personal" T4 on T4."Personalnummer" = T1."MonteurNr") left outer join "\\172.17.171.208:6262\werwiso\WerWisoTabs\Mueller\Mandant.add"."ZeiterfUnprod" T5 on (((T1."Filial_ID" = T5."Filial_ID") and (T1."MonteurNr" = T5."MonteurNr")) and (T1."Datum" = T5."Datum")) and (T1."Beginn" = T5."Beginn"))
    66. 

  66. where (T4."Filial_ID" = (cinteger(nconvert(T3."Standort_ID2"),0)))
    67. 

  67. and (((T4."Personalart" IN ('Monteur                  ')) and (T1."Datum" >= DATE '2019-10-01')) and ((T4."Austrittsdatum" IS NULL) or (T4."Austrittsdatum" >= (@CURRENT_DATE))))
    68. 

  68. order by c2 asc,c3 asc
    69. 

  69. 

  70. END SQL
    71. 

  71. COLUMN,0,Filial Id
    72. 

  72. COLUMN,1,Monteurnr
    73. 

  73. COLUMN,2,Datum
    74. 

  74. COLUMN,3,Beginn
    75. 

  75. COLUMN,4,Vorgangsnummer
    76. 

  76. COLUMN,5,Ende
    77. 

  77. COLUMN,6,Differenz
    78. 

  78. COLUMN,7,Anzaw
    79. 

  79. COLUMN,8,Zegruppe
    80. 

  80. COLUMN,9,Kommentar
    81. 

  81. COLUMN,10,Status
    82. 

  82. COLUMN,11,Nachgearbeitetfuer
    83. 

  83. COLUMN,12,Nachgearbeitetvon
    84. 

  84. COLUMN,13,Kommentarmonteur
    85. 

  85. COLUMN,14,Auto
    86. 

  86. COLUMN,15,Todoid
    87. 

  87. COLUMN,16,Modellid_Zeitmodelle
    88. 

  88. COLUMN,17,Modellbezeichnung_Zeitmodelle
    89. 

  89. COLUMN,18,Modellkuerzel_Zeitmodelle
    90. 

  90. COLUMN,19,Ende - Beginn
    91. 

  91. COLUMN,20,Minute_in_Minuten
    92. 

  92. COLUMN,21,Stunde_in_Minuten
    93. 

  93. COLUMN,22,Sekunde_in_Minuten
    94. 

  94. COLUMN,23,Minuten
    95. 

  95. COLUMN,24,Stunden
    96. 

  96. COLUMN,25,Hauptbetrieb_ID
    97. 

  97. COLUMN,26,Standort_ID
    98. 

  98. COLUMN,27,Personalart
    99. 

  99. COLUMN,28,Leistungsgruppe
    100. 

  100. COLUMN,29,Leistungsgrad
    101. 

  101. COLUMN,30,Monteur_Gruppe
    102. 

  102. COLUMN,31,Monteur_Gruppe_2
    103. 

  103. COLUMN,32,Monteur
    104. 

  104. COLUMN,33,Order Number
    105. 

  105. COLUMN,34,Activity_Codes_Group_1
    106. 

  106. COLUMN,35,Activity_Codes_Group2
    107. 

  107. COLUMN,36,Abwesenheitsart_Zeiterfunprod
    108. 

  108. COLUMN,37,Activity_Desc
    109. 

  109. COLUMN,38,Kostenstelle
    110. 

  110. COLUMN,39,prod.
    111. 

  111. COLUMN,40,unprod.
    112. 

  112. COLUMN,41,Abw.
    113. 

  113. COLUMN,42,Extern
    114. 

  114. COLUMN,43,GWL
    115. 

  115. COLUMN,44,Intern
    116. 

  116. COLUMN,45,Sollzeit
    117. 

  117. COLUMN,46,Monteur_Azubi
    118. 

  118. COLUMN,47,Standort_Name
    119. 

  119. COLUMN,48,krank
    120. 

  120. COLUMN,49,Anw. + krank
    121. 

  121. COLUMN,50,Anw. gestempelt
    122. 

  122. COLUMN,51,Pause
    123. 

  123. COLUMN,52,Anw.
    124. 

  124. COLUMN,53,Monteur_Datum
    125. 

  125. COLUMN,54,prod_unprod
    126. 

  126. COLUMN,55,noch nicht verr. Std.
    127. 

  127. COLUMN,56,Hauptbetrieb_Name
    128. 

  128.