Laufzeit von Stored Procedure verkürzen

[Andidreas]

Also die Performance vom Server ist eher weniger das Problem...

Anbei die Stored Procedure

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