Home Explore Help
Register Sign In
GlobalCube
/
C11
3
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Tree: 12cb950568
Branches Tags
C11
/
System
/
NAVISION
/
IQD
/
Zeiten
/
Labor_Ledger_entry_Export.sql

Labor_Ledger_entry_Export.sql 13 KB
History Raw

123456789101112131415161718192021222324252627282930313233343536373839404142 
  1. select distinct "Service Order No" as "Service Order No",
    2. 

  2. 	   SUM(c6 at "Service Order No") OVER (partition by "Service Order No") as "Summe verk.Stunden",
    3. 

  3. 	   "Datum" as "Datum",
    4. 

  4. 	   "Summe Umsatz Lohn" as "Summe Umsatz Lohn",
    5. 

  5. 	   "Summe Umsatz Lohn Plan" as "Summe Umsatz Lohn Plan"
    6. 

  6. from 
    7. 

  7. (select "Service Order No" as "Service Order No",
    8. 

  8. 	   "Datum" as "Datum",
    9. 

  9. 	   "Summe Umsatz Lohn" as "Summe Umsatz Lohn",
    10. 

  10. 	   "Summe Umsatz Lohn Plan" as "Summe Umsatz Lohn Plan",
    11. 

  11. 	   SUM(c7) OVER (partition by "Service Order No") as c6
    12. 

  12. from 
    13. 

  13. (select "Service Order No" as "Service Order No",
    14. 

  14. 	   "Datum" as "Datum",
    15. 

  15. 	   "Summe Umsatz Lohn" as "Summe Umsatz Lohn",
    16. 

  16. 	   "Summe Umsatz Lohn Plan" as "Summe Umsatz Lohn Plan",
    17. 

  17. 	   ((c20) / COUNT(c21) OVER (partition by c22)) as c7
    18. 

  18. from 
    19. 

  19. (select "Service Order No",
    20. 

  20. 	   "Datum",
    21. 

  21. 	   SUM(c18) OVER (partition by "Service Order No") as "Summe Umsatz Lohn",
    22. 

  22. 	   SUM(c17) OVER (partition by "Service Order No") as "Summe Umsatz Lohn Plan",
    23. 

  23. 	   c15 as c20,
    24. 

  24. 	   c19 as c21,
    25. 

  25. 	   c13 as c22
    26. 

  26. from 
    27. 

  27. (select (T1."Service Order No_" + T1."Document No_" + (((T1."Service Order Line No_")))) as c13,
    28. 

  28. 	   T1."Service Order No_" as "Service Order No",
    29. 

  29. 	   convert(float, T1."Qty_ (Hour)") as c15,
    30. 

  30. 	   T2."Posting Date" as "Datum",
    31. 

  31. 	   (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) * CASE WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.15) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (7.67) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (9.41) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (9.07) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (7.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('Extern','Intern'))) THEN (8.99) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'MECH')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.00) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (8.20) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (8.20) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'KARO')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (8.20) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('10','40'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('30'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.70) WHEN (((((T3."Type" = 4) and (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END * (convert(float, T3."Qty_ per Hour")) <> 0)) and (T3."Charging Group No_" = 'ELEK')) and (CASE WHEN (T2."Location Code" = 'MM') THEN ('10') WHEN (T2."Location Code" = 'VÖH') THEN ('20') WHEN (T2."Location Code" = 'KRU') THEN ('30') WHEN (T2."Location Code" = 'ULM') THEN ('40') WHEN (T2."Location Code" = 'LL') THEN ('50') ELSE null END IN ('50'))) and (CASE WHEN (T3."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T3."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END IN ('GWL'))) THEN (7.70) ELSE (0) END) as c17,
    32. 

  32. 	   (CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Total Price"))) ELSE (0) END) as c18,
    33. 

  33. 	   T1."Entry No_" as c19
    34. 

  34. from "NAVISION"."import"."Archived_Service_Header" T2,
    35. 

  35. 	("NAVISION"."import"."Service_Ledger_Entry" T3 left outer join "NAVISION"."import"."Labor_Ledger_Entry" T1 on (((T3."Order No_" = T1."Service Order No_") and (T3."Order Line No_" = T1."Service Order Line No_")) and (T3."Assoc_ Entry" = T1."Entry No_")) and (T3."Client_DB" = T1."Client_DB"))
    36. 

  36. where ((T2."No_" = T1."Service Order No_") and (T2."Client_DB" = T1."Client_DB"))
    37. 

  37. and (((T1."Entry Type" = 0) and (T3."Entry Type" = 0)) and (T2."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000')))
    38. 

  38. ) D3
    39. 

  39. ) D6
    40. 

  40. ) D2
    41. 

  41. ) D1
    42. 

  42. -- order by "Service Order No" asc
    43.