select CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END as "Betrieb Nr",
	   'Forderungen' as "Stufe1",
	   'Kundenforderungen' as "Stufe2",
	   CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END as "Stufe3_ori",
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) <> 'NA Flotte') THEN ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END)) ELSE null END as "Stufe4",
	   SUM(((CASE WHEN ((left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((convert(float, T1."Amount"))))) ELSE (0) END))) as "Summe Umsatz 365",
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA') THEN ((SUM(((CASE WHEN ((left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((convert(float, T1."Amount"))))) ELSE (0) END)))) / 365 * 14) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') THEN ((SUM(((CASE WHEN ((left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((convert(float, T1."Amount"))))) ELSE (0) END)))) / 365 * 21) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) IN ('GA','T&Z')) THEN ((SUM(((CASE WHEN ((left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((convert(float, T1."Amount"))))) ELSE (0) END)))) / 365 * 7) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA Flotte') THEN ((SUM(((CASE WHEN ((left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((convert(float, T1."Amount"))))) ELSE (0) END)))) / 365 * 14) ELSE (0) END as "Max. Wert aus Ist-Umsatz",
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA') THEN (14) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') THEN (21) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) IN ('GA','T&Z')) THEN (7) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA Flotte') THEN (14) ELSE (0) END as "Soll-Fälligkeit in Tagen",
	   CASE WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('GWL-Forderungen') WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (not T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('KD-Forderungen') ELSE null END as "Stufe5",
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA Flotte') THEN ('NA') ELSE ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END)) END as "Stufe3",
	   '1' as "Hauptbetrieb_ID",
	   (CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) as "Standort_ID"
from "DMS1"."dbo"."AH Reisacher$G_L Account" T3,
	"DMS1"."dbo"."AH Reisacher$G_L Entry" T1,
	"ims"."Konten Soll Ist Nav_2013_Umsatz_für_OP" T2
where (T3."No_" = T1."G_L Account No_") and (T3."No_" = T2."Konto Nr")
and (((((((((T3."Income_Balance" = 0) and (((left(T1."G_L Account No_",1)) IN ('8','7','6','5')) or (T3."No_" = '44020'))) and (not T1."Description" IN ('V-ZAHL'))) and (T1."Document No_" <> 'ABSCHLUSS2008_1')) and (not T1."Document No_" IN ('ABSCHLUSS2009_1','ABSCHLUSS2009_2','ABSCHLUSS2009_3','ABSCHLUSS_1','ABSCHLUSS2011','ABSCHLUSS2012','ABSCHLUSS2013','ABSCHLUSS2014','ABSCHLUSS2015','ABSCHLUSS2016','ABSCHLUSS2017','ABSCHLUSS2018','ABSCHLUSS2019'))) and (T1."Source Code" <> 'JAHRABSCH')) and ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 400)) and (CASE WHEN (((ymdint_extract(datetime_to_ymdint((ymdint_to_datetime(daysint_to_ymdint((convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))),(convert(datetime, (getdate()) - cinterval(-1 * datediff(day, (getdate())), 1))) - INTERVAL '001 00:00:00.000' - (convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))))))),5)) = 0) and ((year((convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))))) <> (year((convert(datetime, (getdate()) - cinterval(-1 * datediff(day, (getdate())), 1))) - INTERVAL '001 00:00:00.000')))) THEN (99) WHEN (((ymdint_extract(datetime_to_ymdint((ymdint_to_datetime(daysint_to_ymdint((convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))),(convert(datetime, (getdate()) - cinterval(-1 * datediff(day, (getdate())), 1))) - INTERVAL '001 00:00:00.000' - (convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))))))),5)) = 0) and ((convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))) > (convert(datetime, (getdate()) - cinterval(-1 * datediff(day, (getdate())), 1))) - INTERVAL '001 00:00:00.000')) THEN (99) ELSE ((ymdint_extract(datetime_to_ymdint((ymdint_to_datetime(daysint_to_ymdint((convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))),(convert(datetime, (getdate()) - cinterval(-1 * datediff(day, (getdate())), 1))) - INTERVAL '001 00:00:00.000' - (convert(datetime, T1."Posting Date" - cinterval(-1 * datediff(day, T1."Posting Date"), 1))))))),5))) END BETWEEN 0 AND 11)) and (not (CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) IN ('NA Flotte')))
group by CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END,CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END,CASE WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('GWL-Forderungen') WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (not T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('KD-Forderungen') ELSE null END
-- order by "Betrieb Nr" asc,"Stufe3_ori" asc,"Stufe5" asc