First post: Problem with SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

First post: Problem with SQL query

Author

Topic

cjnewton
Starting Member

3 Posts

Posted - 2007-05-18 : 20:17:40

I'm putting together a .NET web page for my group at work to use for server and infrastructure equipment inventory. I'm having a great deal of trouble with one query. The query needs to pull from three tables; 'Rack', 'Server', and 'Infrastructure.' What I need it to do is SUM the number of rack units (slots) used by servers and infrastructure equipment, grouped by the rack number, and subtract it from the number of units the rack has available. I have a query that works for a single rack...

SELECT [Server].[Rack_No], ([Available_Slots] - (SUM([Rack_Units_Used]) + (SELECT SUM([Rack_Units_Used]) AS [RUs]
FROM [Infrastructure] WHERE [Infrastructure].[Rack_No] = 'Rack1'))) AS [RUs]
FROM [Server], [Rack]
WHERE [Server].[Rack_No] = 'Rack1' AND [Rack].[Rack_No] = 'Rack1'
GROUP BY [Server].[Rack_No], [Available_Slots];

...but, I cannot seem to put one together that will generate a report of all the racks. The closest I've come is this query...

SELECT [Rack].[Rack_No], ([Available_Slots] - (SUM([Infrastructure].[Rack_Units_Used]) + (SUM([Server].[Rack_Units_Used])))) AS [RUs]
FROM [Server], [Rack], [Infrastructure]
WHERE [Server].[Rack_No] = [Rack].[Rack_No] AND [Infrastructure].[Rack_No] = [Rack].[Rack_No]
GROUP BY [Rack].[Rack_No], [Available_Slots]
ORDER BY [Rack].[Rack_No];

But I have to problems. First, the results are completely inaccurate. Second, It cannot deal with null values if there isn't at least one server AND at least one piece of infrastructure equipment installed in a given rack. I need it to return a '0' instead of 'NULL' if that is the case.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-18 : 20:27:21

can you post the table structure here ? Same sample data and the expected result would be nice also

cjnewton
Starting Member

3 Posts

Posted - 2007-05-19 : 12:14:46

Tables used in this query (unused columns omitted)

Rack
Rack_No nchar(20)
Location nchar(30)
Available_Slots int

Server
Server_Name nchar(30)
Rack_No FK nchar(20)
Rack_Units_Used int

Infrastructure
Machine_Name nchar(30)
Rack_No FK nchar(20)
Rack_Units_Used int

Given the following sample data...

Rack
Rack_No...Location....Available_Slots
Rack1........Loc1..........36
Rack2........Loc1..........42
Rack3........Loc1..........42

Server
Server_Name......Rack_No......Rack_Units_Used
Server1................Rack1...........5
Server2................Rack1...........4
Server3................Rack2...........7
Server4................Rack2...........3
Server5................Rack3...........4
Server6................Rack3...........10

Infrastructure
Machine_Name.....Rack_No......Rack_Units_Used
Inf1.......................Rack1...........2
Inf2.......................Rack1...........5
Inf3.......................Rack2...........4
Inf4.......................Rack2...........3

The first query in my original post correctly returns the following data for Rack1.

Rack_No....RUs
Rack1.........20 (number of unused slots)

It only works for a single rack though. I need to write a query that will return a report of all the racks in the DB. Results would be...

Rack_No....RUs
Rack1.........20
Rack2.........25
Rack3.........28

I can't seem to pull this off. First, the math in my report query does not work and it also can't deal with the fact that there are no Infrastructure items in Rack3. This is what my report query returns.

Rack_No....RUs
Rack1.........4
Rack2.........8

Any help you or anyone else can offer would be greatly appreciated.

Thanks

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 12:52:44

[code]
DECLARE @Rack TABLE
(
Rack_No nchar(20),
Location nchar(30),
Available_Slots int
)

DECLARE @Server TABLE
(
Server_Name nchar(30),
Rack_No nchar(20), -- FK
Rack_Units_Used int
)

DECLARE @Infrastructure TABLE
(
Machine_Name nchar(30),
Rack_No nchar(20), -- FK
Rack_Units_Used int
)

INSERT @Rack
SELECT 'Rack1', 'Loc1', 36 UNION ALL
SELECT 'Rack2', 'Loc1', 42 UNION ALL
SELECT 'Rack3', 'Loc1', 42

INSERT @Server
SELECT 'Server1', 'Rack1', 5 UNION ALL
SELECT 'Server2', 'Rack1', 4 UNION ALL
SELECT 'Server3', 'Rack2', 7 UNION ALL
SELECT 'Server4', 'Rack2', 3 UNION ALL
SELECT 'Server5', 'Rack3', 4 UNION ALL
SELECT 'Server6', 'Rack3', 10

INSERT @Infrastructure
SELECT 'Inf1', 'Rack1', 2 UNION ALL
SELECT 'Inf2', 'Rack1', 5 UNION ALL
SELECT 'Inf3', 'Rack2', 4 UNION ALL
SELECT 'Inf4', 'Rack2', 3

SELECT [Rack].[Rack_No],
[Available_Slots],
[TOT_Infrastructure_Rack_Units_Used] = SUM([Infrastructure].[TOT_Rack_Units_Used]),
[TOT_Server_Rack_Units_Used] = SUM([Server].[TOT_Rack_Units_Used]),
-- ([Available_Slots] - (SUM([Infrastructure].[Rack_Units_Used]) + (SUM([Server].[Rack_Units_Used])))) AS [RUs_OLD],
-- ([Available_Slots] - (SUM(COALESCE([Infrastructure].[Rack_Units_Used], 0)) + (SUM(COALESCE([Server].[Rack_Units_Used], 0))))) AS [RUs]
[Available_Slots] - (SUM([Infrastructure].[TOT_Rack_Units_Used]) + SUM([Server].[TOT_Rack_Units_Used])) AS [RUs]

FROM @Rack AS Rack
LEFT OUTER JOIN
(
SELECT Rack_No,
SUM(COALESCE(Rack_Units_Used, 0)) AS TOT_Rack_Units_Used
FROM @Server
GROUP BY Rack_No
) AS Server
ON [Server].[Rack_No] = [Rack].[Rack_No]
LEFT OUTER JOIN
(
SELECT Rack_No,
SUM(COALESCE(Rack_Units_Used, 0)) AS TOT_Rack_Units_Used
FROM @Infrastructure
GROUP BY Rack_No
) AS Infrastructure
ON [Infrastructure].[Rack_No] = [Rack].[Rack_No]
GROUP BY [Rack].[Rack_No], [Available_Slots]
ORDER BY [Rack].[Rack_No]
[/code]
I think you would be better off using varchar instead of NChar (I don't expect you even need Nvarchar)

Kristen

cjnewton
Starting Member

3 Posts

Posted - 2007-05-19 : 14:16:26

Thank you so much. I got it working in my .NET app like this...

SELECT [Rack].[Rack_No],
[Available_Slots] - (SUM(COALESCE([Infrastructure].[TOT_Rack_Units_Used], 0)) + (SUM(COALESCE([Server].[TOT_Rack_Units_Used], 0)))) AS [RUs]

FROM Rack AS Rack
LEFT OUTER JOIN
(
SELECT Rack_No,
SUM(COALESCE(Rack_Units_Used, 0)) AS TOT_Rack_Units_Used
FROM Server
GROUP BY Rack_No
) AS Server
ON [Server].[Rack_No] = [Rack].[Rack_No]
LEFT OUTER JOIN
(
SELECT Rack_No,
SUM(COALESCE(Rack_Units_Used, 0)) AS TOT_Rack_Units_Used
FROM Infrastructure
GROUP BY Rack_No
) AS Infrastructure
ON [Infrastructure].[Rack_No] = [Rack].[Rack_No]
GROUP BY [Rack].[Rack_No], [Available_Slots]
ORDER BY [Rack].[Rack_No];

Where do I send the "You're my hero" secret decoder ring?

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 15:50:21

"Where do I send the "You're my hero" secret decoder ring?"

Hehehe ... No need, but folk that bother to drop by to say "thank you" are always appreciated, and folk that try to learn from suggestions here are appreciated even more!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources