C11/System/ARI/IQD/Service/Service_offene_Auftraege_neu_mitMont_Anzeige.sql

select c293 as "Document Type",
	   c292 as "No",
	   c291 as "Sell-to Customer No",
	   c290 as "Bill-to Customer No",
	   c289 as "Bill-to Name",
	   c164 as "Order Date",
	   c288 as "Posting Date",
	   c287 as "Posting Description",
	   c286 as "Due Date",
	   c285 as "Location Code",
	   c284 as "Department Code",
	   c283 as "Make Code",
	   c282 as "Salesperson Code",
	   c281 as "On Hold",
	   c280 as "Transaction Type",
	   c279 as "Transport Method",
	   c278 as "Correction",
	   c277 as "Document Date",
	   c276 as "Area",
	   c275 as "Reserve",
	   c274 as "Option Code",
	   c273 as "Service Posting Group",
	   c163 as "Vin",
	   c272 as "Model Code",
	   c271 as "Model No",
	   c270 as "Model",
	   c269 as "Type",
	   c268 as "Initial Registration",
	   c267 as "Order Limit",
	   c266 as "Time Of Order",
	   c265 as "Pickup Date",
	   c264 as "Pickup Time",
	   c263 as "Completion Date",
	   c262 as "Completion Time",
	   c261 as "Labor Type",
	   c260 as "Labor Charging Code",
	   c259 as "Book No",
	   c258 as "Branch Book No",
	   c257 as "Sales Department Code",
	   c256 as "Fixed Date",
	   c255 as "Fixed Instruction",
	   c254 as "Current Instruction Type",
	   c253 as "Work Completed",
	   c252 as "Status Code",
	   c251 as "Service Advisor No",
	   '1' as "Hauptbetrieb",
	   c250 as "Standort_Department",
	   c249 as "Standort",
	   c248 as "No_Employee",
	   c247 as "First Name_Employee",
	   c246 as "Last Name_Employee",
	   c176 as "Serviceberater",
	   c245 as "Umsatzart",
	   c244 as "Kunde",
	   c160 as "Order Number_mit Monteur",
	   c243 as "Entry No",
	   c166 as "Order No",
	   c242 as "Posting Date",
	   c241 as "Document No",
	   c240 as "Type",
	   c239 as "No",
	   c238 as "Description",
	   c237 as "Quantity",
	   c236 as "Direct Unit Cost",
	   c235 as "Unit Cost",
	   c234 as "Total Cost",
	   c233 as "Unit Price",
	   c232 as "Total Price",
	   c231 as "Department Code",
	   c230 as "Work Type Code",
	   c229 as "User Id",
	   c228 as "Amt To Post To G L",
	   c227 as "Amt Posted To G L",
	   c226 as "Amt To Recognize",
	   c225 as "Amt Recognized",
	   c224 as "Entry Type",
	   c223 as "Positive",
	   c222 as "Qty Evaluated",
	   c221 as "Corrected",
	   c220 as "Variant Code",
	   c219 as "Qty Per Unit Of Measure",
	   c218 as "Quantity (base)",
	   c217 as "Time Type",
	   c216 as "Service Job No",
	   c215 as "Assoc Entry",
	   c214 as "Closed",
	   c213 as "Qty Per Hour",
	   c212 as "Qty (hour)",
	   c211 as "Item Group Code",
	   c210 as "Document Type",
	   c209 as "Customer Group Code",
	   c208 as "Charging Group No",
	   c207 as "Standard Time",
	   c206 as "Standard Time Type",
	   c205 as "Source Type",
	   c204 as "Main Customer",
	   c203 as "Order Line No",
	   c202 as "Time From",
	   c201 as "Time Until",
	   c200 as "Internal Charged",
	   c199 as "Order Completed",
	   c198 as "Open",
	   c197 as "Closed By Entry No",
	   c196 as "Closed At Date",
	   c195 as "Closed By Quantity",
	   c194 as "Open Quantity",
	   c193 as "Labor Standard Time Type",
	   c192 as "Menge",
	   c191 as "Einstandsbetrag",
	   c190 as "Verkaufsbetrag",
	   c189 as "Umsatz Lohn",
	   c162 as "Umsatz Teile Service",
	   c188 as "Umsatz Sonstiges",
	   c187 as "Einsatz Teile Service",
	   c186 as "Einsatz Sonstiges",
	   c185 as "Menge pro Stunde",
	   c184 as "verk.Stunden",
	   c161 as "verk. AW",
	   c183 as "benutzte Zeit",
	   c182 as "benutzte AW",
	   c181 as "Name_Employee",
	   c180 as "Function Code",
	   c181 as "Monteur",
	   c180 as "Funktion Monteur",
	   c179 as "ben. AW ohne AZUBI",
	   c164 as "Auftragsdatum",
	   1 as "DG_1",
	   COUNT(c292) OVER (partition by c160) as "DG_2",
	   1 / (COUNT(c292) OVER (partition by c160)) as "Durchgänge",
	   c165 as "Anzahl_Tage",
	   (c165) / (COUNT(c292) OVER (partition by c160)) as "Tage offen",
	   c178 as "gebuchte Teile",
	   c177 as "Monteur_Anzeige",
	   c176 as "Serviceberater_Monteur",
	   c175 as "Order Number",
	   c164 as "Invoice Date",
	   0 as "Minimum Letzte Stempelung",
	   c174 as "Tage letzte Stemp. bereinigt",
	   (c174) / (COUNT(c292) OVER (partition by c160)) as "Anz. Tage letzte Stemp.",
	   c173 as "Order Number_intern",
	   c171 as "Anzahl Tage über Abholtermin",
	   c172 as "Intern/Extern",
	   (c171) / (COUNT(c292) OVER (partition by c160)) as "Tage über Abholtermin",
	   c170 as "Kundenname_verkauft_an",
	   c169 as "Bill-to Contact",
	   c168 as "Sell-to Customer Name",
	   c167 as "Order Number_intern_neu",
	   c166 as "Order Number",
	   c165 as "Anzahl Tage_ori",
	   (1 / (COUNT(c292) OVER (partition by c160))) as "Durchgänge (Auftrag)",
	   c165 as "Anzahl Tage",
	   c164 as "Order Date",
	   '' as "Kostenstelle",
	   c163 as "Fahrzeug",
	   c162 as "Teile",
	   c161 as "Arbeitswerte"
from 
(select (CASE WHEN ((((T2."First Name" + ' ' + T2."Last Name"))) IS NOT NULL) THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (((T2."First Name" + ' ' + T2."Last Name"))) + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date")))) WHEN ((((T2."First Name" + ' ' + T2."Last Name"))) IS NULL) THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date")))) ELSE null END) as c160,
	   ((CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END) * ((convert(float, T3."Qty_ per Hour")))) as c161,
	   (CASE WHEN (T3."Type" = 1) THEN (((convert(float, T3."Total Price")))) ELSE (0) END) as c162,
	   T1."VIN" as c163,
	   T1."Order Date" as c164,
	   ((day((now()) - T1."Order Date"))) as c165,
	   T3."Order No_" as c166,
	   CASE WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NOT NULL) THEN (T1."No_" + ' - ' + T1."VIN" + ' / ' + T1."Model" + ' - ' + T1."Sell-to Customer Name" + ' / ' + T1."Bill-to Customer No_" + ' - ' + (T2."First Name" + ' ' + T2."Last Name") + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date"))) + ' / ' + 'Abh. Termin:' + ' ' + (convert(varchar(50), year(T1."Pickup Date")) + '-' + convert(varchar(50), month(T1."Pickup Date")) + '-' + convert(varchar(50), day(T1."Pickup Date")))) WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NULL) THEN (T1."No_" + ' - ' + T1."VIN" + ' / ' + T1."Model" + ' - ' + T1."Sell-to Customer Name" + ' / ' + T1."Bill-to Customer No_" + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date"))) + ' / ' + 'Abh. Termin:' + ' ' + (convert(varchar(50), year(T1."Pickup Date")) + '-' + convert(varchar(50), month(T1."Pickup Date")) + '-' + convert(varchar(50), day(T1."Pickup Date")))) ELSE null END as c167,
	   T1."Sell-to Customer Name" as c168,
	   T1."Bill-to Contact" as c169,
	   T6."Name" as c170,
	   (day((now()) - T1."Pickup Date")) as c171,
	   CASE WHEN (T1."Sell-to Customer No_" IN ('INTERN1','INTERN2')) THEN ('Intern') ELSE ('Extern') END as c172,
	   CASE WHEN (T1."Sell-to Customer No_" IN ('INTERN1','INTERN2')) THEN ((CASE WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NOT NULL) THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (T2."First Name" + ' ' + T2."Last Name") + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date")))) WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NULL) THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date")))) ELSE null END) + ' / ' + T1."VIN" + ' / ' + T1."Model" + ' - ' + 'Abh.Termin:' + ' ' + (convert(varchar(50), year(T1."Pickup Date")) + '-' + convert(varchar(50), month(T1."Pickup Date")) + '-' + convert(varchar(50), day(T1."Pickup Date"))) + ' - ' + T1."Sell-to Customer Name") ELSE null END as c173,
	   CASE WHEN (0 IS NULL) THEN (((day((now()) - T1."Order Date")))) ELSE (0) END as c174,
	   CASE WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NOT NULL) THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (T2."First Name" + ' ' + T2."Last Name") + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date")))) WHEN ((T2."First Name" + ' ' + T2."Last Name") IS NULL) THEN (T1."No_" + ' - ' + (T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name") + ' - ' + (convert(varchar(50), year(T1."Order Date")) + '-' + convert(varchar(50), month(T1."Order Date")) + '-' + convert(varchar(50), day(T1."Order Date")))) ELSE null END as c175,
	   ((T2."First Name" + ' ' + T2."Last Name")) as c176,
	   T5."Last Name" as c177,
	   CASE WHEN ((T3."Type" = 1) and (T3."Document No_" IS NOT NULL)) THEN (((convert(float, T3."Total Price")))) ELSE (0) END as c178,
	   CASE WHEN (not T4."Function Code" IN ('AZUBI','AZUBIG')) THEN (((CASE WHEN (T3."Type" = 2) THEN ((convert(float, T3."Qty_ Evaluated"))) ELSE (0) END) * 12)) ELSE (0) END as c179,
	   T4."Function Code" as c180,
	   T4."Name" as c181,
	   (CASE WHEN (T3."Type" = 2) THEN ((convert(float, T3."Qty_ Evaluated"))) ELSE (0) END) * 12 as c182,
	   CASE WHEN (T3."Type" = 2) THEN ((convert(float, T3."Qty_ Evaluated"))) ELSE (0) END as c183,
	   CASE WHEN (T3."Type" = 4) THEN ((convert(float, T3."Qty_ (Hour)"))) ELSE (0) END as c184,
	   (convert(float, T3."Qty_ per Hour")) as c185,
	   CASE WHEN (T3."Type" IN (0,3)) THEN (((convert(float, T3."Total Cost")))) ELSE (0) END as c186,
	   CASE WHEN (T3."Type" = 1) THEN (((convert(float, T3."Total Cost")))) ELSE (0) END as c187,
	   CASE WHEN (T3."Type" IN (0,3)) THEN (((convert(float, T3."Total Price")))) ELSE (0) END as c188,
	   CASE WHEN (T3."Type" = 4) THEN (((convert(float, T3."Total Price")))) ELSE (0) END as c189,
	   (convert(float, T3."Total Price")) as c190,
	   (convert(float, T3."Total Cost")) as c191,
	   (convert(float, T3."Quantity")) as c192,
	   T3."Labor Standard Time Type" as c193,
	   T3."Open Quantity" as c194,
	   T3."Closed by Quantity" as c195,
	   T3."Closed at Date" as c196,
	   T3."Closed by Entry No_" as c197,
	   T3."Open" as c198,
	   T3."Order Completed" as c199,
	   T3."Internal Charged" as c200,
	   T3."Time Until" as c201,
	   T3."Time From" as c202,
	   T3."Order Line No_" as c203,
	   T3."Main Customer" as c204,
	   T3."Source Type" as c205,
	   T3."Standard Time Type" as c206,
	   T3."Standard Time" as c207,
	   T3."Charging Group No_" as c208,
	   T3."Customer Group Code" as c209,
	   T3."Document Type" as c210,
	   T3."Item Group Code" as c211,
	   T3."Qty_ (Hour)" as c212,
	   T3."Qty_ per Hour" as c213,
	   T3."Closed" as c214,
	   T3."Assoc_ Entry" as c215,
	   T3."Service Job No_" as c216,
	   T3."Time Type" as c217,
	   T3."Quantity (Base)" as c218,
	   T3."Qty_ per Unit of Measure" as c219,
	   T3."Variant Code" as c220,
	   T3."Corrected" as c221,
	   T3."Qty_ Evaluated" as c222,
	   T3."Positive" as c223,
	   T3."Entry Type" as c224,
	   T3."Amt_ Recognized" as c225,
	   T3."Amt_ to Recognize" as c226,
	   T3."Amt_ Posted to G_L" as c227,
	   T3."Amt_ to Post to G_L" as c228,
	   T3."User ID" as c229,
	   T3."Work Type Code" as c230,
	   T3."Department Code" as c231,
	   T3."Total Price" as c232,
	   T3."Unit Price" as c233,
	   T3."Total Cost" as c234,
	   T3."Unit Cost" as c235,
	   T3."Direct Unit Cost" as c236,
	   T3."Quantity" as c237,
	   T3."Description" as c238,
	   T3."No_" as c239,
	   T3."Type" as c240,
	   T3."Document No_" as c241,
	   T3."Posting Date" as c242,
	   T3."Entry No_" as c243,
	   T1."Bill-to Customer No_" + ' - ' + T1."Bill-to Name" as c244,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE ('Extern') END as c245,
	   T2."Last Name" as c246,
	   T2."First Name" as c247,
	   T2."No_" as c248,
	   CASE WHEN ((T1."Location Code" = 'BUR') or (T1."Location Code" = '')) THEN ('10') WHEN (T1."Location Code" = 'MUE') THEN ('20') ELSE ((left(T1."Department Code",2))) END as c249,
	   (left(T1."Department Code",2)) as c250,
	   T1."Service Advisor No_" as c251,
	   T1."Status Code" as c252,
	   T1."Work Completed" as c253,
	   T1."Current Instruction Type" as c254,
	   T1."Fixed Instruction" as c255,
	   T1."Fixed Date" as c256,
	   T1."Sales Department Code" as c257,
	   T1."Branch Book No_" as c258,
	   T1."Book No_" as c259,
	   T1."Labor Charging Code" as c260,
	   T1."Labor Type" as c261,
	   T1."Completion Time" as c262,
	   T1."Completion Date" as c263,
	   T1."Pickup Time" as c264,
	   T1."Pickup Date" as c265,
	   T1."Time of Order" as c266,
	   T1."Order Limit" as c267,
	   T1."Initial Registration" as c268,
	   T1."Type" as c269,
	   T1."Model" as c270,
	   T1."Model No_" as c271,
	   T1."Model Code" as c272,
	   T1."Service Posting Group" as c273,
	   T1."Option Code" as c274,
	   T1."Reserve" as c275,
	   T1."Area" as c276,
	   T1."Document Date" as c277,
	   T1."Correction" as c278,
	   T1."Transport Method" as c279,
	   T1."Transaction Type" as c280,
	   T1."On Hold" as c281,
	   T1."Salesperson Code" as c282,
	   T1."Make Code" as c283,
	   T1."Department Code" as c284,
	   T1."Location Code" as c285,
	   T1."Due Date" as c286,
	   T1."Posting Description" as c287,
	   T1."Posting Date" as c288,
	   T1."Bill-to Name" as c289,
	   T1."Bill-to Customer No_" as c290,
	   T1."Sell-to Customer No_" as c291,
	   T1."No_" as c292,
	   T1."Document Type" as c293
from ((((("Vogl7x"."dbo"."BMW AH Vogl$Service Header" T1 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T2 on T2."No_" = T1."Service Advisor No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Service Ledger Entry" T3 on T1."No_" = T3."Order No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T4 on T4."No_" = T3."No_") left outer join "ims"."Monteure_fuer_Anzeige_offene_Auftraege" T5 on T5."Order No_" = T1."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T6 on T1."Sell-to Customer No_" = T6."No_")
where ((T1."No_" LIKE 'W%') and (T1."Document Type" = 1))
) D1
-- order by "No" asc