Thema: Laufzeit von Stored Procedure verkürzen

Einzelnen Beitrag anzeigen

Benutzerbild von Andidreas
Andidreas

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

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 14:44
Also die Performance vom Server ist eher weniger das Problem...

Anbei die Stored Procedure



zusammenfalten · markieren
SQL-Code:
USE [inventory]
GO

CREATE PROCEDURE prInventoryManagement_Overview @Brand varchar(255), @Productline varchar(255)
AS

Select
'Warehouse' as Storage,
'SKUs' as Typ,
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_<25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('25')
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('30')
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_30",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('40')
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_40",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('50', '55')
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_50_55",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('60')
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_60",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('70')
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_70",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
AND (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir1, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir2, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_Pir3, ',', '.')) > 0)) as "PLC_Total"


UNION


Select
'Consi' as Storage,
'SKUs' as Typ,
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_<25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('25')
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('30')
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_30",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('40')
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_40",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('50', '55')
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_50_55",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('60')
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_60",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_LowestPLC_SKU IN ('70')
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_70",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
AND (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.')) > 0)) as "PLC_Total"


UNION


Select
'Warehouse' as Storage,
'Pieces' as Typ,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('25')
) as PLC_25,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('30')
) as PLC_30,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('40')
) as PLC_40,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('60')
) as PLC_60,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('70')
) as PLC_70,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total


UNION


Select
'Consi' as Storage,
'Pieces' as Typ,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('25')
) as PLC_25,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('30')
) as PLC_30,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('40')
) as PLC_40,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('60')
) as PLC_60,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('70')
) as PLC_70,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total


UNION


Select
'Warehouse' as Storage,
'DoH' as Typ,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('25')
) as PLC_25,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('30')
) as PLC_30,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('40')
) as PLC_40,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('60')
) as PLC_60,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('70')
) as PLC_70,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total


UNION


Select
'Consi' as Storage,
'DoH' as Typ,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('25')
) as PLC_25,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('30')
) as PLC_30,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('40')
) as PLC_40,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('60')
) as PLC_60,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('70')
) as PLC_70,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat