Einzelnen Beitrag anzeigen

Benutzerbild von Andidreas
Andidreas

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

AW: Subselect mit MSSQL

  Alt 31. Mai 2012, 10:46
Weder noch... So wies aussieht kann man in der MSSQL Temporär keine neuen Tabellen Namen vergeben...

So gehts auf alle Fälle:

SQL-Code:
select
'> 3 Monthsas DoH,
'Totalas Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 77.4
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 154.8) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 77.4
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 154.8) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 77.4
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 154.8) PLC40

UNION

select
'> 6 Monthsas DoH,
'Totalas Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 154.8
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 309.6) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 154.8
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 309.6) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 154.8
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 309.6) PLC40

UNION

select
'> 12 Monthsas DoH,
'Totalas Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 309.6) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 309.6) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 309.6) PLC40

UNION

select
'HighStockas DoH,
'Totalas Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 154.8
And CONVERT(Float, ALL_AvailableStock) > 200) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 154.8
And CONVERT(Float, ALL_AvailableStock) > 200) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 154.8
And CONVERT(Float, ALL_AvailableStock) > 200) PLC40
Sieht doch Hübsch aus
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald

Geändert von mkinzler (31. Mai 2012 um 10:48 Uhr) Grund: Code-Tag durch SQL-Tag ersetzt: So sieht es noch hübscher aus
  Mit Zitat antworten Zitat