Acasă Explorează Ajutor
Înregistrare Autentificare
GlobalCube
/
C11
3
0
Bifurcare 0
Fisiere Probleme 0 Trageți solicitările 0 Wiki
Arbore: 12cb950568
Ramuri Etichete
C11
/
System
/
NAVISION
/
IQD
/
OP
/
Belege_KER_Umsatz_OP_Berechnung_gruppiert.sql

Belege_KER_Umsatz_OP_Berechnung_gruppiert.sql 13 KB
Istoric Crud

12345678910111213141516171819 
  1. 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",
    2. 

  2. 	   'Forderungen' as "Stufe1",
    3. 

  3. 	   'Kundenforderungen' as "Stufe2",
    4. 

  4. 	   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",
    5. 

  5. 	   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",
    6. 

  6. 	   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",
    7. 

  7. 	   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",
    8. 

  8. 	   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",
    9. 

  9. 	   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",
    10. 

  10. 	   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",
    11. 

  11. 	   '1' as "Hauptbetrieb_ID",
    12. 

  12. 	   (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"
    13. 

  13. from "DMS1"."dbo"."AH Reisacher$G_L Account" T3,
    14. 

  14. 	"DMS1"."dbo"."AH Reisacher$G_L Entry" T1,
    15. 

  15. 	"ims"."Konten Soll Ist Nav_2013_Umsatz_für_OP" T2
    16. 

  16. where (T3."No_" = T1."G_L Account No_") and (T3."No_" = T2."Konto Nr")
    17. 

  17. 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')))
    18. 

  18. 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
    19. 

  19. -- order by "Betrieb Nr" asc,"Stufe3_ori" asc,"Stufe5" asc
    20.