C11/System/CARLO/IQD/Serv_Teile/offene_auftraege_eds_c11.sql

select "Belegart" as "Belegart",
	   "Nr" as "Nr",
	   "Verk An Deb -nr" as "Verk An Deb -nr",
	   "Rech An Deb -nr" as "Rech An Deb -nr",
	   "Rech An Name" as "Rech An Name",
	   "Auftragsdatum" as "Auftragsdatum",
	   "Buchungsdatum" as "Buchungsdatum",
	   "Buchungsbeschreibung" as "Buchungsbeschreibung",
	   "Lagerortcode" as "Lagerortcode",
	   "Kostenstellencode" as "Kostenstellencode",
	   "Verkäufercode" as "Verkäufercode",
	   "Buchungsnr" as "Buchungsnr",
	   "Letzte Lieferscheinnr" as "Letzte Lieferscheinnr",
	   "Letzte Buchungsnr" as "Letzte Buchungsnr",
	   "Storno" as "Storno",
	   "Belegdatum" as "Belegdatum",
	   "Externe Belegnummer" as "Externe Belegnummer",
	   "Nummernserie" as "Nummernserie",
	   "Buchungsnr Serie" as "Buchungsnr Serie",
	   "Lieferungsnr Serie" as "Lieferungsnr Serie",
	   "Benutzer Id" as "Benutzer Id",
	   "Ordertype" as "Ordertype",
	   "Werkstattbuchungsgruppe" as "Werkstattbuchungsgruppe",
	   "Deaktivierungsdatum" as "Deaktivierungsdatum",
	   "Aktivierungsdatum" as "Aktivierungsdatum",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Amtliches Kennzeichen" as "Amtliches Kennzeichen",
	   "Km-stand" as "Km-stand",
	   "Modellcode" as "Modellcode",
	   "Fahrzeugmodellnr" as "Fahrzeugmodellnr",
	   "Baujahr" as "Baujahr",
	   "Modell" as "Modell",
	   "Typ" as "Typ",
	   "Erstzulassung" as "Erstzulassung",
	   "Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   "Auftragsuhrzeit" as "Auftragsuhrzeit",
	   "Abholdatum" as "Abholdatum",
	   "Abholuhrzeit" as "Abholuhrzeit",
	   "Serviceberaternr" as "Serviceberaternr",
	   "Fertigstellungsdatum" as "Fertigstellungsdatum",
	   "Datum Kundenzulassung" as "Datum Kundenzulassung",
	   "Fahrzeugklassencode" as "Fahrzeugklassencode",
	   "Fahrzeugartencode" as "Fahrzeugartencode",
	   "Aw Typ" as "Aw Typ",
	   "Aw Verrechnungscode" as "Aw Verrechnungscode",
	   "Aw Preisgruppe" as "Aw Preisgruppe",
	   "Aktivierung" as "Aktivierung",
	   "Buchnummer" as "Buchnummer",
	   "Filialbuchnummer" as "Filialbuchnummer",
	   "Kostenstellencode Verkauf" as "Kostenstellencode Verkauf",
	   "Aw Preisgruppe Int Verr" as "Aw Preisgruppe Int Verr",
	   "Serviceberaternr Planung" as "Serviceberaternr Planung",
	   "Filialcode" as "Filialcode",
	   "Arbeiten Erledigt" as "Arbeiten Erledigt",
	   "Statuscode" as "Statuscode",
	   "Serviceberaternr Abholung" as "Serviceberaternr Abholung",
	   "Filialcode Verkauf" as "Filialcode Verkauf",
	   "Erstes Lieferdatum" as "Erstes Lieferdatum",
	   "Letztes Lieferdatum" as "Letztes Lieferdatum",
	   "Fahrer" as "Fahrer",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Nr" as "Nr",
	   "Vorname" as "Vorname",
	   "Nachname" as "Nachname",
	   "Serviceberater" as "Serviceberater",
	   "Order Number" as "Order Number",
	   "Markencode" as "Markencode",
	   "Fabrikat" as "Fabrikat",
	   "Model" as "Model",
	   "Fahrzeug" as "Fahrzeug",
	   "KST_aus_Code" as "KST_aus_Code",
	   "Kostenstelle" as "Kostenstelle",
	   "Marke" as "Marke",
	   "Kunde" as "Kunde",
	   "Anzahl Tage_ori" as "Anzahl Tage_ori",
	   "Durchgänge (Auftrag)_ori" as "Durchgänge (Auftrag)_ori",
	   "erledigt_ja_nein" as "erledigt_ja_nein",
	   "DG_2" as "DG_2",
	   "Durchgänge (Auftrag)" as "Durchgänge (Auftrag)",
	   "Belegart" as "Belegart",
	   "Belegnr" as "Belegnr",
	   "Zeilennr" as "Zeilennr",
	   "Art" as "Art",
	   "Nr" as "Nr",
	   "Auftragsdatum" as "Auftragsdatum",
	   "Beschreibung" as "Beschreibung",
	   "Einheit" as "Einheit",
	   "Menge" as "Menge",
	   "Vk-preis" as "Vk-preis",
	   "Mengenrabatt %" as "Mengenrabatt %",
	   "Zeilenrabatt %" as "Zeilenrabatt %",
	   "Zeilenrabattbetrag" as "Zeilenrabattbetrag",
	   "Betrag" as "Betrag",
	   RSUM("Betrag" for "Nr") as "Summe  (Betrag) Nr.2",
	   RSUM("Betrag") as "Summe  (Betrag) Nr.1",
	   "Geschäftsbuchungsgruppe" as "Geschäftsbuchungsgruppe",
	   "Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   "Menge (basis)" as "Menge (basis)",
	   "Vorgabezeit" as "Vorgabezeit",
	   "Menge Pro Stunde" as "Menge Pro Stunde",
	   "Menge (stunde)" as "Menge (stunde)",
	   "Kostenlos" as "Kostenlos",
	   "Arbeitswerte" as "Arbeitswerte",
	   "Teile" as "Teile",
	   "Fremdl." as "Fremdl.",
	   "Anzahl Tage" as "Anzahl Tage",
	   "Date" as "Date",
	   "Company_ID" as "Company_ID",
	   "Department_ID" as "Department_ID",
	   "Employee_Function" as "Employee_Function",
	   "Service_Advisor_Name" as "Service_Advisor_Name",
	   "Turnover_Type_Desc" as "Turnover_Type_Desc",
	   "Order_Desc" as "Order_Desc",
	   "Kostenstellencode_Werkstattzeile" as "Kostenstellencode_Werkstattzeile",
	   "Cost_Centre_Name" as "Cost_Centre_Name",
	   "Make_Desc" as "Make_Desc",
	   "Job_Amount" as "Job_Amount",
	   "Parts_Amount" as "Parts_Amount",
	   "Parts_Purch_Amount" as "Parts_Purch_Amount",
	   "Misc_Amount" as "Misc_Amount",
	   "verk. Std." as "verk. Std.",
	   "Order_Count" as "Order_Count",
	   "Days_Since_Date" as "Days_Since_Date",
	   "Geschäftsbuchungsgruppe_Kopf" as "Geschäftsbuchungsgruppe_Kopf"
from 
(select c229 as "Belegart",
	   c129 as "Nr",
	   c228 as "Verk An Deb -nr",
	   c227 as "Rech An Deb -nr",
	   c226 as "Rech An Name",
	   c142 as "Auftragsdatum",
	   c225 as "Buchungsdatum",
	   c224 as "Buchungsbeschreibung",
	   c223 as "Lagerortcode",
	   c222 as "Kostenstellencode",
	   c221 as "Verkäufercode",
	   c220 as "Buchungsnr",
	   c219 as "Letzte Lieferscheinnr",
	   c218 as "Letzte Buchungsnr",
	   c217 as "Storno",
	   c216 as "Belegdatum",
	   c215 as "Externe Belegnummer",
	   c214 as "Nummernserie",
	   c213 as "Buchungsnr Serie",
	   c212 as "Lieferungsnr Serie",
	   c211 as "Benutzer Id",
	   c210 as "Ordertype",
	   c209 as "Werkstattbuchungsgruppe",
	   c208 as "Deaktivierungsdatum",
	   c207 as "Aktivierungsdatum",
	   c206 as "Fahrgestellnummer",
	   c205 as "Amtliches Kennzeichen",
	   c204 as "Km-stand",
	   c203 as "Modellcode",
	   c202 as "Fahrzeugmodellnr",
	   c201 as "Baujahr",
	   c169 as "Modell",
	   c200 as "Typ",
	   c199 as "Erstzulassung",
	   c198 as "Produktbuchungsgruppe",
	   c197 as "Auftragsuhrzeit",
	   c196 as "Abholdatum",
	   c195 as "Abholuhrzeit",
	   c194 as "Serviceberaternr",
	   c193 as "Fertigstellungsdatum",
	   c192 as "Datum Kundenzulassung",
	   c191 as "Fahrzeugklassencode",
	   c190 as "Fahrzeugartencode",
	   c189 as "Aw Typ",
	   c188 as "Aw Verrechnungscode",
	   c187 as "Aw Preisgruppe",
	   c186 as "Aktivierung",
	   c185 as "Buchnummer",
	   c184 as "Filialbuchnummer",
	   c183 as "Kostenstellencode Verkauf",
	   c182 as "Aw Preisgruppe Int Verr",
	   c181 as "Serviceberaternr Planung",
	   c180 as "Filialcode",
	   c179 as "Arbeiten Erledigt",
	   c178 as "Statuscode",
	   c177 as "Serviceberaternr Abholung",
	   c176 as "Filialcode Verkauf",
	   c175 as "Erstes Lieferdatum",
	   c174 as "Letztes Lieferdatum",
	   c173 as "Fahrer",
	   '1' as "Hauptbetrieb",
	   c141 as "Standort",
	   c172 as "Nr",
	   c171 as "Vorname",
	   c170 as "Nachname",
	   c140 as "Serviceberater",
	   c138 as "Order Number",
	   c136 as "Markencode",
	   c136 as "Fabrikat",
	   c169 as "Model",
	   c168 as "Fahrzeug",
	   c167 as "KST_aus_Code",
	   c166 as "Kostenstelle",
	   c136 as "Marke",
	   c165 as "Kunde",
	   c131 as "Anzahl Tage_ori",
	   1 as "Durchgänge (Auftrag)_ori",
	   c164 as "erledigt_ja_nein",
	   SUM(1) OVER (partition by c129) as "DG_2",
	   1 / (SUM(1) OVER (partition by c129)) as "Durchgänge (Auftrag)",
	   c163 as "Belegart",
	   c162 as "Belegnr",
	   c161 as "Zeilennr",
	   c160 as "Art",
	   c159 as "Nr",
	   c158 as "Auftragsdatum",
	   c157 as "Beschreibung",
	   c156 as "Einheit",
	   c155 as "Menge",
	   c154 as "Vk-preis",
	   c153 as "Mengenrabatt %",
	   c152 as "Zeilenrabatt %",
	   c151 as "Zeilenrabattbetrag",
	   c150 as "Betrag",
	   c149 as "Geschäftsbuchungsgruppe",
	   c148 as "Produktbuchungsgruppe",
	   c147 as "Menge (basis)",
	   c146 as "Vorgabezeit",
	   c145 as "Menge Pro Stunde",
	   c144 as "Menge (stunde)",
	   c143 as "Kostenlos",
	   c135 as "Arbeitswerte",
	   c134 as "Teile",
	   c133 as "Fremdl.",
	   (c131) / (SUM(1) OVER (partition by c129)) as "Anzahl Tage",
	   c142 as "Date",
	   '1' as "Company_ID",
	   c141 as "Department_ID",
	   'Serviceberater' as "Employee_Function",
	   c140 as "Service_Advisor_Name",
	   c139 as "Turnover_Type_Desc",
	   c138 as "Order_Desc",
	   c137 as "Kostenstellencode_Werkstattzeile",
	   c137 as "Cost_Centre_Name",
	   c136 as "Make_Desc",
	   c135 as "Job_Amount",
	   c134 as "Parts_Amount",
	   0 as "Parts_Purch_Amount",
	   c133 as "Misc_Amount",
	   c132 as "verk. Std.",
	   (1 / (SUM(1) OVER (partition by c129))) as "Order_Count",
	   ((c131) / (SUM(1) OVER (partition by c129))) as "Days_Since_Date",
	   c130 as "Geschäftsbuchungsgruppe_Kopf"
from 
(select T1."Nr_" as c129,
	   T1."Geschäftsbuchungsgruppe" as c130,
	   (day((now()) - T1."Auftragsdatum")) as c131,
	   CASE WHEN ((CASE WHEN (T3."Art" = 3) THEN (((T3."Betrag"))) ELSE (0) END) <> 0) THEN (((T3."Menge (Stunde)"))) ELSE (0) END as c132,
	   (CASE WHEN (T3."Art" = 4) THEN (((T3."Betrag"))) ELSE (0) END) as c133,
	   (CASE WHEN (T3."Art" = 2) THEN (((T3."Betrag"))) ELSE (0) END) as c134,
	   (CASE WHEN (T3."Art" = 3) THEN (((T3."Betrag"))) ELSE (0) END) as c135,
	   T1."Markencode" as c136,
	   T3."Kostenstellencode" as c137,
	   ((substring(T1."Nr_" + ' - ' + T1."Rech_ an Name" + ' - ' + T1."Statuscode" + ' - ' + (convert(varchar(50), year(T1."Auftragsdatum")) + '-' + convert(varchar(50), month(T1."Auftragsdatum")) + '-' + convert(varchar(50), day(T1."Auftragsdatum"))) + ' - ' + 'erledigt: ' + (CASE WHEN (T1."Arbeiten erledigt" = 1) THEN ('ja') WHEN (T1."Arbeiten erledigt" = 0) THEN ('nein') ELSE null END) + ' - Abh.Dat.: ' + (CASE WHEN (T1."Abholdatum" = convert(datetime, '1753-01-01 00:00:00.000')) THEN ('n.v.') ELSE ((convert(varchar(50), year(T1."Abholdatum")) + '-' + convert(varchar(50), month(T1."Abholdatum")) + '-' + convert(varchar(50), day(T1."Abholdatum")))) END), 1, 100))) as c138,
	   CASE WHEN (T1."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T1."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END as c139,
	   (T1."Serviceberaternr_" + ' - ' + T2."Vorname" + ' ' + T2."Nachname") as c140,
	   (CASE WHEN ('1' = '2') THEN ('20') ELSE (T1."Filialcode") END) as c141,
	   T1."Auftragsdatum" as c142,
	   T3."Kostenlos" as c143,
	   (T3."Menge (Stunde)") as c144,
	   T3."Menge pro Stunde" as c145,
	   T3."Vorgabezeit" as c146,
	   T3."Menge (Basis)" as c147,
	   T3."Produktbuchungsgruppe" as c148,
	   T3."Geschäftsbuchungsgruppe" as c149,
	   (T3."Betrag") as c150,
	   T3."Zeilenrabattbetrag" as c151,
	   T3."Zeilenrabatt %" as c152,
	   T3."Mengenrabatt %" as c153,
	   (T3."VK-Preis") as c154,
	   (T3."Menge") as c155,
	   T3."Einheit" as c156,
	   T3."Beschreibung" as c157,
	   T3."Auftragsdatum" as c158,
	   T3."Nr_" as c159,
	   T3."Art" as c160,
	   T3."Zeilennr_" as c161,
	   T3."Belegnr_" as c162,
	   T3."Belegart" as c163,
	   CASE WHEN (T1."Arbeiten erledigt" = 1) THEN ('ja') WHEN (T1."Arbeiten erledigt" = 0) THEN ('nein') ELSE null END as c164,
	   T1."Rech_ an Deb_-Nr_" + ' - ' + T1."Rech_ an Name" as c165,
	   CASE WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) LIKE '1%') THEN ('1') WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) LIKE '2%') THEN ('2') WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) LIKE '3%') THEN ('6') WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) IN ('41','40')) THEN ('3') WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) = '44') THEN ('4') WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) = '45') THEN ('5') WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) LIKE '7%') THEN ('7') WHEN ((CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END) LIKE '9%') THEN ('0') ELSE null END as c166,
	   CASE WHEN (T1."Kostenstellencode Verkauf" = '') THEN ('99') ELSE ((substring(T1."Kostenstellencode Verkauf", 3, 2))) END as c167,
	   T1."Fahrgestellnummer" + ' - ' + T1."Modell" as c168,
	   T1."Modell" as c169,
	   T2."Nachname" as c170,
	   T2."Vorname" as c171,
	   T2."Nr_" as c172,
	   T1."Fahrer" as c173,
	   T1."Letztes Lieferdatum" as c174,
	   T1."Erstes Lieferdatum" as c175,
	   T1."Filialcode Verkauf" as c176,
	   T1."Serviceberaternr_ Abholung" as c177,
	   T1."Statuscode" as c178,
	   T1."Arbeiten erledigt" as c179,
	   T1."Filialcode" as c180,
	   T1."Serviceberaternr_ Planung" as c181,
	   T1."AW Preisgruppe int_ Verr_" as c182,
	   T1."Kostenstellencode Verkauf" as c183,
	   T1."Filialbuchnummer" as c184,
	   T1."Buchnummer" as c185,
	   T1."Aktivierung" as c186,
	   T1."AW Preisgruppe" as c187,
	   T1."AW Verrechnungscode" as c188,
	   T1."AW Typ" as c189,
	   T1."Fahrzeugartencode" as c190,
	   T1."Fahrzeugklassencode" as c191,
	   T1."Datum Kundenzulassung" as c192,
	   T1."Fertigstellungsdatum" as c193,
	   T1."Serviceberaternr_" as c194,
	   T1."Abholuhrzeit" as c195,
	   T1."Abholdatum" as c196,
	   T1."Auftragsuhrzeit" as c197,
	   T1."Produktbuchungsgruppe" as c198,
	   T1."Erstzulassung" as c199,
	   T1."Typ" as c200,
	   T1."Baujahr" as c201,
	   T1."Fahrzeugmodellnr_" as c202,
	   T1."Modellcode" as c203,
	   T1."Km-Stand" as c204,
	   T1."Amtliches Kennzeichen" as c205,
	   T1."Fahrgestellnummer" as c206,
	   T1."Aktivierungsdatum" as c207,
	   T1."Deaktivierungsdatum" as c208,
	   T1."Werkstattbuchungsgruppe" as c209,
	   T1."OrderType" as c210,
	   T1."Benutzer ID" as c211,
	   CASE WHEN (T1."Abholdatum" = convert(datetime, '1753-01-01 00:00:00.000')) THEN ('n.v.') ELSE ((convert(varchar(50), year(T1."Abholdatum")) + '-' + convert(varchar(50), month(T1."Abholdatum")) + '-' + convert(varchar(50), day(T1."Abholdatum")))) END as c212,
	   T1."Buchungsnr_ Serie" as c213,
	   T1."Nummernserie" as c214,
	   T1."Externe Belegnummer" as c215,
	   T1."Belegdatum" as c216,
	   T1."Storno" as c217,
	   T1."Letzte Buchungsnr_" as c218,
	   T1."Letzte Lieferscheinnr_" as c219,
	   T1."Buchungsnr_" as c220,
	   T1."Verkäufercode" as c221,
	   T1."Kostenstellencode" as c222,
	   T1."Lagerortcode" as c223,
	   T1."Buchungsbeschreibung" as c224,
	   T1."Buchungsdatum" as c225,
	   T1."Rech_ an Name" as c226,
	   T1."Rech_ an Deb_-Nr_" as c227,
	   T1."Verk_ an Deb_-Nr_" as c228,
	   T1."Belegart" as c229
from (("CARLO"."import"."Werkstattkopf" T1 left outer join "CARLO"."import"."Employee" T2 on (T2."Nr_" = T1."Serviceberaternr_") and (T2."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Werkstattzeile" T3 on (T1."Nr_" = T3."Belegnr_") and (T1."Client_DB" = T3."Client_DB"))
where (((T1."Nr_" LIKE 'WVAN%') and (T1."Auftragsdatum" >= convert(datetime, '2017-01-01 00:00:00.000'))) and (T1."Filialcode" <> ' '))
-- order by c129 asc
) D1
) D3