C11/System/NAVISION/IQD/Belege/Belege_Bilanz_Grosskunden_STK_BWA.sql

select "No" as "No",
	   "Name_2" as "Name_2",
	   "Search Description" as "Search Description",
	   "Account Type" as "Account Type",
	   "Department Code_2" as "Department Code_2",
	   "Make Code_Konto" as "Make Code_Konto",
	   "Income Balance" as "Income Balance",
	   "Last Date Modified" as "Last Date Modified",
	   "Entry No" as "Entry No",
	   "G L Account No" as "G L Account No",
	   "Posting Date" as "Posting Date",
	   "Document Type" as "Document Type",
	   "Document No" as "Document No",
	   "Description" as "Description",
	   "Bal Account No" as "Bal Account No",
	   "Department Code" as "Department Code",
	   "Make Code" as "Make Code",
	   "User Id" as "User Id",
	   "Source Code" as "Source Code",
	   "Quantity" as "Quantity",
	   "Reason Code" as "Reason Code",
	   "Gen Posting Type" as "Gen Posting Type",
	   "Gen Bus Posting Group" as "Gen Bus Posting Group",
	   "Gen Prod Posting Group" as "Gen Prod Posting Group",
	   "Bal Account Type" as "Bal Account Type",
	   "Transaction No" as "Transaction No",
	   "Document Date" as "Document Date",
	   "External Document No" as "External Document No",
	   "Source Type" as "Source Type",
	   "Source No" as "Source No",
	   "No Series" as "No Series",
	   "Branch Code" as "Branch Code",
	   "Main Area" as "Main Area",
	   "Vin" as "Vin",
	   "Book No" as "Book No",
	   "Veh Source Code" as "Veh Source Code",
	   "Reposted" as "Reposted",
	   "Corrected" as "Corrected",
	   "Correction To Curr No" as "Correction To Curr No",
	   "Reposting To Curr No" as "Reposting To Curr No",
	   "Code" as "Code",
	   "Name" as "Name",
	   "Jahr_ori" as "Jahr_ori",
	   "Betrieb Nr_2" as "Betrieb Nr_2",
	   "Konto Nr_ori" as "Konto Nr_ori",
	   "Betrag_ori" as "Betrag_ori",
	   "Amount_1" as "Amount_1",
	   "Betrieb Nr_neu_1" as "Betrieb Nr_neu_1",
	   SUM("Betrag_ori") OVER (partition by "Vin") as "Summe_Betrag_VIN",
	   "Rechnungsdatum" as "Rechnungsdatum",
	   "Minmum_Rechnungsdatum" as "Minmum_Rechnungsdatum",
	   "Menge_1" as "Menge_1",
	   COUNT("Document No") OVER (partition by "Vin") as "Menge_2",
	   ("Menge_1" / (COUNT("Document No") OVER (partition by "Vin"))) as "Betrag",
	   "Betrieb Nr" as "Betrieb Nr",
	   "Jahr" as "Jahr",
	   "Marke_GK" as "Marke_GK",
	   MAX("Marke_GK") OVER (partition by "Vin") as "Maximum_Marke",
	   CASE WHEN ((MAX("Marke_GK") OVER (partition by "Vin")) = 'BMW') THEN ('88200_Stk') WHEN ((MAX("Marke_GK") OVER (partition by "Vin")) = 'BMW-MINI') THEN ('88203_Stk') WHEN ((MAX("Marke_GK") OVER (partition by "Vin")) = 'BMWI') THEN ('88270_Stk') ELSE null END as "Konto Nr",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name"
from 
(select "No",
	   "Name_2",
	   "Search Description",
	   "Account Type",
	   "Department Code_2",
	   "Make Code_Konto",
	   "Income Balance",
	   "Last Date Modified",
	   "Entry No",
	   "G L Account No",
	   "Posting Date",
	   "Document Type",
	   "Document No",
	   "Description",
	   "Bal Account No",
	   "Department Code",
	   "Make Code",
	   "User Id",
	   "Source Code",
	   "Quantity",
	   "Reason Code",
	   "Gen Posting Type",
	   "Gen Bus Posting Group",
	   "Gen Prod Posting Group",
	   "Bal Account Type",
	   "Transaction No",
	   "Document Date",
	   "External Document No",
	   "Source Type",
	   "Source No",
	   "No Series",
	   "Branch Code",
	   "Main Area",
	   "Vin",
	   "Book No",
	   "Veh Source Code",
	   "Reposted",
	   "Corrected",
	   "Correction To Curr No",
	   "Reposting To Curr No",
	   "Code",
	   "Name",
	   "Posting Date" as "Jahr_ori",
	   "Betrieb Nr_2",
	   "G L Account No" as "Konto Nr_ori",
	   "Betrag_ori",
	   "Amount_1",
	   "Betrieb Nr_2" as "Betrieb Nr_neu_1",
	   "Rechnungsdatum",
	   MIN("Rechnungsdatum") OVER (partition by "Vin") as "Minmum_Rechnungsdatum",
	   1 as "Menge_1",
	   "Betrieb Nr",
	   CASE WHEN ((("Vin" LIKE '%FK68973%') or ("Vin" LIKE '%FK70609%')) or ("Vin" LIKE '%FK72533%')) THEN (convert(datetime, '2021-01-31 00:00:00.000')) ELSE ((MIN("Rechnungsdatum") OVER (partition by "Vin"))) END as "Jahr",
	   "Marke_GK",
	   "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Betrieb Nr_2" as "Standort_ID",
	   "Standort_Name",
	   SUM("Betrag_ori") OVER (partition by "Vin") as c64
from 
(select (T2."VIN") as "Vin",
	   CASE WHEN (T2."Branch Code" IN ('GÖG','AAM')) THEN ('AAM') ELSE (T2."Branch Code") END as "Standort_Name",
	   (CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('10') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ('10') END) as "Betrieb Nr_2",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   T2."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T2."Document No_" LIKE 'VRGF%') THEN (T2."Make Code") ELSE null END as "Marke_GK",
	   CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END as "Betrieb Nr",
	   CASE WHEN (T2."Document No_" LIKE 'VRGF%') THEN (T2."Posting Date") ELSE null END as "Rechnungsdatum",
	   (convert(float, T2."Amount")) as "Amount_1",
	   ((convert(float, T2."Amount"))) as "Betrag_ori",
	   T2."G_L Account No_" as "G L Account No",
	   T2."Posting Date" as "Posting Date",
	   T3."Name" as "Name",
	   T3."Code" as "Code",
	   T2."Reposting to curr_ No_" as "Reposting To Curr No",
	   T2."Correction to curr_ No_" as "Correction To Curr No",
	   T2."Corrected" as "Corrected",
	   T2."Reposted" as "Reposted",
	   T2."Veh_ Source Code" as "Veh Source Code",
	   T2."Book No_" as "Book No",
	   T2."Main Area" as "Main Area",
	   T2."Branch Code" as "Branch Code",
	   T2."No_ Series" as "No Series",
	   T2."Source No_" as "Source No",
	   T2."Source Type" as "Source Type",
	   T2."External Document No_" as "External Document No",
	   T2."Document Date" as "Document Date",
	   T2."Transaction No_" as "Transaction No",
	   T2."Bal_ Account Type" as "Bal Account Type",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Gen_ Posting Type" as "Gen Posting Type",
	   T2."Reason Code" as "Reason Code",
	   T2."Quantity" as "Quantity",
	   T2."Source Code" as "Source Code",
	   T2."User ID" as "User Id",
	   T2."Make Code" as "Make Code",
	   T2."Department Code" as "Department Code",
	   T2."Bal_ Account No_" as "Bal Account No",
	   T2."Description" as "Description",
	   T2."Document No_" as "Document No",
	   T2."Document Type" as "Document Type",
	   T2."Entry No_" as "Entry No",
	   T1."Last Date Modified" as "Last Date Modified",
	   T1."Income_Balance" as "Income Balance",
	   T1."Make Code" as "Make Code_Konto",
	   T1."Department Code" as "Department Code_2",
	   T1."Account Type" as "Account Type",
	   T1."Search Description" as "Search Description",
	   T1."Name" as "Name_2",
	   T1."No_" as "No"
from "NAVISION"."import"."G_L_Account" T1,
	("NAVISION"."import"."G_L_Entry" T2 left outer join "NAVISION"."import"."Department" T3 on (T2."Department Code" = T3."Code") and (T2."Client_DB" = T3."Client_DB"))
where ((T1."No_" = T2."G_L Account No_") and (T1."Client_DB" = T2."Client_DB"))
and ((T1."Income_Balance" = 1) and (T1."No_" = '15210'))
) D2
) D1
where ((((c64 <= "Menge_1") and (c64 >= -9999)) and ("Minmum_Rechnungsdatum" IS NOT NULL)) and ("Minmum_Rechnungsdatum" >= convert(datetime, '2019-01-01 00:00:00.000')))
-- order by "Vin" asc