AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Laufzeit von Stored Procedure verkürzen
Thema durchsuchen
Ansicht
Themen-Optionen

Laufzeit von Stored Procedure verkürzen

Ein Thema von Andidreas · begonnen am 4. Okt 2012 · letzter Beitrag vom 18. Okt 2012
Antwort Antwort
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#1

AW: Laufzeit von Stored Procedure verkürzen

  Alt 6. Okt 2012, 09:11
Ich habe den ersten Teil Deiner Unionabfrage umgebaut, so dass schätzungsweise ein Table Scan reicht.
Ist in Oracle gemacht, dürfte aber keine große Rolle spielen. Die Feldtypen habe ich natürlich "optimiert".

Statt der Split Funktion habe ich aus Bequemlichkeit eine normale Tabelle verwendet, geht ja nur um ein Beispiel.
Die Tabelle hat keine Indizes oder Constraints, knapp 1 Mio Datensätze.
Als Test habe ich noch eine Datumsspalte im 2. Select verwendet. Wirst Du wahrscheinlich auch haben / brauchen.


Code:
10:01:02 SQL> select * from fnsplit;
 
BRAND     PRODUCTLINE
---------- -----------
Mercedes  Carrera
Mercedes  B-Klasse
Porsche   Cayenne
Porsche   Boxter
Porsche   Carrera
 
Executed in 0,125 seconds
 
10:01:19 SQL>
10:01:46 SQL>       Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:01:46 SQL> /
 
STORAGE  TYP SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs          231      282634      183282       98890          98846      183942         110         999889
 
Executed in 1,155 seconds
 
10:01:50 SQL> delete from fnsplit s where s.brand ='Mercedes';
 
2 rows deleted
 
Executed in 0,047 seconds
 
10:03:05 SQL>
10:03:22 SQL>       Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:03:22 SQL> /
 
STORAGE  TYP SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs          231      199650      127974       71214          71170      128590         110         709379
 
Executed in 1,466 seconds
 
10:03:26 SQL>
10:04:05 SQL>       Select 'Warehouse' as Storage, 'SKUs' as Typ, to_char(all_date, 'YYMM'),
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2> 0 OR
          28                   ALL_Pir3> 0)
          29                   )x
          30            group by to_char(all_date, 'YYMM')
          31  /
 
STORAGE  TYP TO_CHAR(ALL_DATE,'YYMM') SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------------------ ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs 1103                                63       49242       31428       17682          17672       31590          30         174355
Warehouse SKUs 1102                                63       44234       28096       16022          16010       28270          30         157647
Warehouse SKUs 1101                                63       54450       34902       19422          19410       35070          30         193467
Warehouse SKUs 1104                                42       51724       33548       18088          18078       33660          20         183910
 
Executed in 0,889 seconds
 
10:04:08 SQL>
Gruß, Jo
  Mit Zitat antworten Zitat
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#2

AW: Laufzeit von Stored Procedure verkürzen

  Alt 8. Okt 2012, 09:15
@jobo

Vielen Dank für deine mühe!
Gehst Du in deiner Lösung davon aus das es Felder wie z.B. "PLC_25", "PLC_30" usw. in der Datenbank gibt?
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#3

AW: Laufzeit von Stored Procedure verkürzen

  Alt 8. Okt 2012, 09:25
Nein, ich erzeuge sie über die Case Anweisungen. Eine statische Kreuztabelle sozusagen.
Die Transformation und auch Aggregation (aus Listenform in Spaltenform und Summierung) könnte man allerdings auch in einem Excelmakro durchführen, wenn Excel sowieso als Reportausgabe dient.
Dann muss das SQL jenachdem etwas mehr Daten ausspucken.
Gruß, Jo
  Mit Zitat antworten Zitat
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#4

AW: Laufzeit von Stored Procedure verkürzen

  Alt 8. Okt 2012, 10:06
Ok habs verstanden...
Die Abfrage ist bei mir auch rasend schnell... Aber leider stimmt das Ergebnis nicht... da muss ich nochmal zeit investieren um das richtig hinzubekommen...

Aber die erste Abfrage war auch nicht mein zeitliches Problem...
Ich denke mal deine Lösung hilft mir bei den späteren Abfragen die Mengen sumiert werden z.B. bei WHs Stock
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#5

AW: Laufzeit von Stored Procedure verkürzen

  Alt 8. Okt 2012, 10:32
@jobo
Die Idee mit der Kreuztabelle ist genial
Ich hab die Stock Berechnungen darauf angepasst... Die dauern jetzt nur noch 8 Sekunden, davor waren es 31 Sekunden!
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#6

AW: Laufzeit von Stored Procedure verkürzen

  Alt 8. Okt 2012, 11:34
@jobo
Die Idee mit der Kreuztabelle ist geniall... Aber leider stimmt das Ergebnis nicht...
Ist leider nicht meine Idee
Da ich keine Tabellenvorlage hatte und es eher ums Prinzip ging, ist das korrekte Ergebnis eher Nebensache gewesen. Außerdem wolltest Du ja Laufzeit verkürzen, von richtigem Ergebnis war keine Rede

Ich drück Dir die Daumen
Gruß, Jo
  Mit Zitat antworten Zitat
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#7

AW: Laufzeit von Stored Procedure verkürzen

  Alt 8. Okt 2012, 14:40
Ich hab in meiner großen Stored Procedure die Unions eliminiert und führe jeden Select in einer separaten Stored Procedure durch.
Des Weiteren habe ich die Statements an das Bsp. von jobo (?) angepasst...
Die Laufzeit liegt nun bei ca. 40 Sek.

Anbei die SQL Statements... Evtl. entdeckt jemand noch Verbesserungs möglichkeiten:



SQL-Code:

/* Stored Procedure 1 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ('00', '10', '20') OR Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 1) IN ('#') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('25') THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('30') THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '40THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ( '50', '55') THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '60THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '70THEN 1 ELSE 0 END as PLC_70,
       1 as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
 AND (ALL_AvailableStock > 0
 OR ALL_Pir > 0
 OR ALL_Pir1 > 0
 OR ALL_Pir2 > 0
 OR ALL_Pir3 > 0)
Group By (ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU)
) TempTable


/* Stored Procedure 2 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ('00', '10', '20') OR Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 1) IN ('#') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('25') THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('30') THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '40THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ( '50', '55') THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '60THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '70THEN 1 ELSE 0 END as PLC_70,
       1 as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
 AND (ALL_ClosingStockQTY > 0
 OR ALL_IssueQTY > 0)
Group By (ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU)
) TempTable


/* Stored Procedure 3 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_AvailableStock ELSE 0 END as PLC_Lower25,
       CASE WHEN ALL_PLC = ('25') THEN ALL_AvailableStock ELSE 0 END as PLC_25,
       CASE WHEN ALL_PLC = ('30') THEN ALL_AvailableStock ELSE 0 END as PLC_30,
       CASE WHEN ALL_PLC = '40THEN ALL_AvailableStock ELSE 0 END as PLC_40,
       CASE WHEN ALL_PLC IN ( '50', '55') THEN ALL_AvailableStock ELSE 0 END as PLC_50_55,
       CASE WHEN ALL_PLC = '60THEN ALL_AvailableStock ELSE 0 END as PLC_60,
       CASE WHEN ALL_PLC = '70THEN ALL_AvailableStock ELSE 0 END as PLC_70,
       ALL_AvailableStock as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 4 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_Lower25,
       CASE WHEN ALL_PLC = ('25') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_25,
       CASE WHEN ALL_PLC = ('30') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_30,
       CASE WHEN ALL_PLC = '40THEN ALL_ClosingStockQTY ELSE 0 END as PLC_40,
       CASE WHEN ALL_PLC IN ( '50', '55') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_50_55,
       CASE WHEN ALL_PLC = '60THEN ALL_ClosingStockQTY ELSE 0 END as PLC_60,
       CASE WHEN ALL_PLC = '70THEN ALL_ClosingStockQTY ELSE 0 END as PLC_70,
       ALL_ClosingStockQTY as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 5 */
Select
CASE WHEN SUM(PIR_Lower25) = 0 THEN 0 ELSE (SUM(Stock_Lower25) / (SUM(PIR_Lower25)/77.4)) END as PLC_Lower25,
CASE WHEN SUM(PIR_25) = 0 THEN 0 ELSE (SUM(Stock_25) / (SUM(PIR_25)/77.4)) END as PLC_25,
CASE WHEN SUM(PIR_30) = 0 THEN 0 ELSE (SUM(Stock_30) / (SUM(PIR_30)/77.4)) END as PLC_30,
CASE WHEN SUM(PIR_40) = 0 THEN 0 ELSE (SUM(Stock_40) / (SUM(PIR_40)/77.4)) END as PLC_40,
CASE WHEN SUM(PIR_50_55) = 0 THEN 0 ELSE (SUM(Stock_50_55) / (SUM(PIR_50_55)/77.4)) END as PLC_50_55,
CASE WHEN SUM(PIR_60) = 0 THEN 0 ELSE (SUM(Stock_60) / (SUM(PIR_60)/77.4)) END as PLC_60,
CASE WHEN SUM(PIR_70) = 0 THEN 0 ELSE (SUM(Stock_70) / (SUM(PIR_70)/77.4)) END as PLC_70,
CASE WHEN SUM(PIR_Total) = 0 THEN 0 ELSE (SUM(Stock_Total) / (SUM(PIR_Total)/77.4)) END as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_Lower25,
      CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_AvailableStock ELSE 0 END as Stock_Lower25,      
      CASE WHEN ALL_PLC IN ('25') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_25,
      CASE WHEN ALL_PLC IN ('25') THEN ALL_AvailableStock ELSE 0 END as Stock_25,      
      CASE WHEN ALL_PLC IN ('30') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_30,
      CASE WHEN ALL_PLC IN ('30') THEN ALL_AvailableStock ELSE 0 END as Stock_30,      
      CASE WHEN ALL_PLC IN ('40') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_40,
      CASE WHEN ALL_PLC IN ('40') THEN ALL_AvailableStock ELSE 0 END as Stock_40,      
      CASE WHEN ALL_PLC IN ('50', '55') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_50_55,
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_AvailableStock ELSE 0 END as Stock_50_55,      
      CASE WHEN ALL_PLC IN ('60') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_60,
      CASE WHEN ALL_PLC IN ('60') THEN ALL_AvailableStock ELSE 0 END as Stock_60,      
      CASE WHEN ALL_PLC IN ('70') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_70,
      CASE WHEN ALL_PLC IN ('70') THEN ALL_AvailableStock ELSE 0 END as Stock_70,      
       (ALL_PIR+ALL_PIR1+ALL_PIR2) as PIR_Total,
       ALL_AvailableStock as Stock_Total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 6 */
Select
(SUM(ClosingStock_Lower25) / (SUM(IssueQTY_Lower25)/77.4)) as PLC_Lower25,
(SUM(ClosingStock_25) / (SUM(IssueQTY_25)/77.4)) as PLC_25,
(SUM(ClosingStock_30) / (SUM(IssueQTY_30)/77.4)) as PLC_30,
(SUM(ClosingStock_40) / (SUM(IssueQTY_40)/77.4)) as PLC_40,
(SUM(ClosingStock_50_55) / (SUM(IssueQTY_50_55)/77.4)) as PLC_50_55,
(SUM(ClosingStock_60) / (SUM(IssueQTY_60)/77.4)) as PLC_60,
(SUM(ClosingStock_70) / (SUM(IssueQTY_70)/77.4)) as PLC_70,
(SUM(ClosingStock_Total) / (SUM(IssueQTY_Total)/77.4)) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_Lower25,
      CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_Lower25,      
      CASE WHEN ALL_PLC IN ('25') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_25,
      CASE WHEN ALL_PLC IN ('25') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_25,      
      CASE WHEN ALL_PLC IN ('30') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_30,
      CASE WHEN ALL_PLC IN ('30') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_30,      
      CASE WHEN ALL_PLC IN ('40') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_40,
      CASE WHEN ALL_PLC IN ('40') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_40,      
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_50_55,
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_50_55,      
      CASE WHEN ALL_PLC IN ('60') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_60,
      CASE WHEN ALL_PLC IN ('60') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_60,      
      CASE WHEN ALL_PLC IN ('70') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_70,
      CASE WHEN ALL_PLC IN ('70') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_70,      
       ALL_ClosingStockQTY as ClosingStock_Total,
       ALL_IssueQTY as IssueQTY_Total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable

Off Topic
Das man hier als Cretin oder Vollpfosten bezeichnet wird hat mich jetzt nicht so gestört, es ist ja niemand Allwissend (naja manche meines es vielleicht)... Aber das der Werte Herr (oder Dame) arrogant sowas behauptet und dann keine Hilfestellung geben kann finde ich sehr schwach...
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat
Antwort Antwort


Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 18:25 Uhr.
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024-2025 by Thomas Breitkreuz