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.
| Author |
Topic |
|
markandrews80
Starting Member
2 Posts |
 Posted - 2011-09-05 : 12:22:17
|
| Hi all,I need to create some reports which analyses the amount of IT rack U space remaining in a few hundred IT cabinets but im stumped as to how to transform the data I have into something I can carry out analysis on. The analysis I require is to be able to count up the number of U slots we have available but for a number of different U slot heights. So for example if I had a cabinet which had the bottom U's 1 through 4 available, I would be able to count this space as:4 x 1U slotsor2 x 2U slotsor1 x 3U slot and 1 x 1U slotor1 x 4U slot etcSo with this type of analysis I would be able to apply this to entire rooms to say we have 38 x 2U slots available OR 10 x 4U slots available etc.At the moment, I am able to extract the data I need into a table which contains a record for every "U" space we have. so a rack with 47U will contain 47 records in the table below with the following columns:Rack_ID: Key of the Rack/CabinetU_Height: The U Height number which starts from 1 to the highest U height within the rack/cabinet.Asset_ID: The key of the device which is racked at that U. For devices higher than 1U, this Asset_ID appears on numerous lines. This is only used to mark a row/U height as being in use or available.So I am able to find the amount of Used/remaining U space easily but the trouble I am having is working out how many U's of space I have for differnet U space sizes from 2U to 10U.Hopefully the above makes sense. :)If anyone knows of a fairly straight forward way of pulling this data out into a new table via sql statments then please let me know since im stumped at the moment :(Thanks in advance for any replies to assist. Apologies for length of post!!ThanksMark |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-09-06 : 07:05:11
|
| Hard to give a full answer without table definition /sample data, but this sounds like you need to do COUNT(U) combined with GROUP BY RackID this will give the total U's available in each rack. To get the number of 2U's or 4U's etc you could use some maths inside a CASE statement, e.g CASE COUNT(U) WHEN 4 then '1' else '0' as NoOf4Us, CASE COUNT(U) WHEN 4 then '2' WHEN 3 then '1' WHEN 2 then '1' else '0' as NoOf2Us, etc. You could then pivot the columns NoOf4Us, NoOf2Us into a single column if required. |
 |
|
|
markandrews80
Starting Member
2 Posts |
Posted - 2011-09-06 : 14:34:00
|
Hi lappin, thanks for your reply. To help explain my problem better, the following columns pretty much make up the table I need to pull data from:Rack_ID: intU: intAsset_ID: intThe below would be a sample of the data stored in the above table.Rack_ID U Asset:ID1 1 1234 1 2 12341 3 NULL1 4 NULL1 5 NULL1 6 NULL1 7 11112 1 NULLetcNow looking at the above as an example, for Rack_ID "1" I have a 4U gap from U3 to U6. As mentioned above I can calculate that I have 4 x 1U gaps via a count(U) having Rack_ID = 1 etc but I would like to be able to count the number of 2U, 3U and 4U gaps to get the following answers:2U Gaps = 2 (One between 3U and 4U and one between 5U to 6U).3U Gaps = 1 (There are two possibilities of where the 3U gap is available either from 3U to 5U or 4U to 6U however as these overlap, only a maximum of 1 x 3U gap is usable).4U Gaps = 1 (The entire space between 3U and 6U is available).From reading your suggestion below am I right in thinking that the case statements will only calculate the individual number of different U spaces based on the total amount of U available within the cabinet? If so then thats not going to provide the correct answers since for example i may have a cabinet which has a 3U gap at the bottom and a 3U gap at the top. Although this totals 6U of available space the number of 6U slots available is 0 since the space is split within the top and bottom of the cabinet. It this that makes this a bit more complicated to work out :)My progress thus far was to extract the free 1U slots to a new table with the following attributes:Rack_ID: Copy from table aboveU: Copy from table aboveU_Size: = 1 for 1U gapEnd_U: = U from table aboveNext_U: = U from table above + 1I then self joined Next_U to U and filtered where Next_U = U which effectivly gave me a list of all U's which are available and are the next U up in the cabinet from the existing 1U gaps. I then appended this data to the new tabl with a U_Size of 2. I planned to do this 10 times with each additional query appending 3U slots, 4U, slots etc.The problem here is that for a 4U space as in the initial sample table above it would tell me that I had 2U slots at 3U-4U, 4U-5U and 5U-6U and so total 3 x 2U slots which is incorrect since it should be 2 x 2U slots. I am now stumped as to how i can run a query to delete the overlapping U space....Apologies for the long winded reply, I am unable to access my PC from which I can send the scripts I have thus far so im finding it difficult to demonstrate :)Hope what I wrote makes sense. If not then let me know as I should have access to my PC soon to get the scripts.CheersMark  |
|
|
|
|
|
|
|
|
SQL help needed for data manipulation