Subselect mit MSSQL

[omata]

Zitat von Andidreas:

So gehts auf alle Fälle:

ja, aber nur weil etwas geht, hört man doch nicht auf das noch weiter zu optimieren (zumindest nicht, wenn das so krauselig aussieht).

Zitat von Andidreas:

Sieht doch Hübsch aus

Das ist ein Alptraum-Code.



Kann man das nicht schöner realisieren?

SQL-Code:

SELECT DoH, 
       'Total' AS Brand, 
       SUM(PLC25) PLC25, SUM(PLC30) PLC30, SUM(PLC40) PLC40
FROM (SELECT CASE WHEN plc = '25' THEN stock ELSE 0 END PLC25,
             CASE WHEN plc = '30' THEN stock ELSE 0 END PLC30,
             CASE WHEN plc = '40' THEN stock ELSE 0 END PLC40,
             CASE 
               WHEN free >  77.4 AND free <= 154.8 THEN '> 3 Months' 
               WHEN free > 154.8 AND free <= 309.6 THEN '> 6 Months' 
               WHEN free > 309.6                   THEN '> 12 Months' 
             END DoH
      FROM (SELECT CONVERT(FLOAT, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.') AS free, 
                   ALL_AvailableStock AS stock,
                   ALL_PLC AS plc           
            FROM [inventory].[dbo].[inventory.inv_all]
            WHERE ALL_PLC IN ('25', '30', '40')) x) x
GROUP BY DoH

UNION

SELECT 'HighStock' AS DoH, 
       'Total' AS Brand, 
       SUM(CASE WHEN plc = '25' THEN stock ELSE 0 END) PLC25,
       SUM(CASE WHEN plc = '30' THEN stock ELSE 0 END) PLC30,
       SUM(CASE WHEN plc = '40' THEN stock ELSE 0 END) PLC40
FROM (SELECT CONVERT(FLOAT, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.') AS free, 
             ALL_AvailableStock AS stock,
             ALL_PLC AS plc           
      FROM [inventory].[dbo].[inventory.inv_all]
      WHERE ALL_PLC IN ('25', '30', '40')) x
WHERE free >= 154.8 
  AND stock > 200