Registriert seit: 26. Aug 2004
Ort: Nebel auf Amrum
3.154 Beiträge
Delphi 7 Enterprise
|
AW: Subselect mit MSSQL
31. Mai 2012, 22:12
ja, aber nur weil etwas geht, hört man doch nicht auf das noch weiter zu optimieren (zumindest nicht, wenn das so krauselig aussieht).
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
Geändert von omata (31. Mai 2012 um 22:53 Uhr)
|
|
Zitat
|