sql statement help - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

sql statement help

Author

Topic

ihatecars
Starting Member

1 Post

Posted - 2010-11-04 : 18:44:37

I know this is a long post, I hope I can describe the results that I am looking for effectively. I need help with writing an sql statement.

This may be a lot more simple than I am making it but for some reason I can't figure it out.

I need five columns of data. The first three are common in each row for the 4th and 5th columns.

Let’s say that column one is the building number, column two is the floor number and column three is the room number. So in the examples I will give, the first three columns are 2, 1, 7. Building 2, floor 1, room 7.

Column four will represent the total number of computers in that room. Column five will represent how many computers in that room have been upgraded with specific software. I was trying to use the COUNT function for both columns 4 and 5.

Here is the dilemma. I am pulling the data from the same two tables but columns 4 and 5 have different criteria. I can run the two SQL statements separately, but I don’t know how to combine them, especially because I want to GROUP them by building, floor and room.

So what I need is for column four (the total number of computers) to be a total count of all computers when the COMP_ID from the first table matches the COMP_ID from the second table. The first table tells me the first three columns (building number, floor number, room number). The second table tells me whether that computer id has been updated (VERSION).

Something like this:

Table name = Location

Comp_id Bldg# floor# room#
P123 2 1 7
P456 2 1 7
P789 2 1 7

Table name = Status

Comp_id version
P123 11
P456 11
P456 12
P789 11

I need the results to show me that in building 2, floor 1, room 7 there are 3 computers and 2 have not updated to version 12.

Notice that VERSION is not part of the primary key and that each COMP_ID can have more than one VERSION showing in the “STATUS” table. So I can’t just type version = 11, because all of the COMP_IDs will be returned. I have the sql working to find out the ones that haven’t, so that isn’t my problem.

Also, there are 131 different building, floor and room combinations so I can’t explicitly look for just 2, 1, 7. The SQL has to be dynamic to show every combination of building, floor and room.

The results should look like this:
Bldg floor room total not updated
2 1 7 3 2

Here is what I have so far, but I don’t know how to combine them.

In this first SQL, I am joining tables because I only want to show those comp_ids that are in both tables. This SQL counts all of the comp_ids, regardless of status.

-- to determine how many total nodes in each room combo regardless of status
select
distinct (loc.bldg_nbr),
loc.floor_nbr,
loc.room_nbr,
count(distinct(loc.comp_id))

from
location loc,
status stat

where loc.comp_id = stat.comp_id

group by loc.bldg_nbr, loc.floor_nbr, loc.room_nbr

In keeping with the above example, the results would be

Bldg # floor # room # total (4th column)
2 1 7 3

The above is just a subset of data, there are 131 rows.

2nd SQL Statement

-- to determine how many nodes are on VERSION 11 for each room combo

select
count(distinct(loc.comp_id))

from
location loc
where loc.comp_id in
(
select distinct(stat.comp_id)
from status stat
where
(vrsn = '11'
and (stat.comp_id not in
(select distinct(comp_id) from status
where vrsn = '12'))
)

group by loc.bldg_nbr, loc.floor_nbr, loc.room_nbr

The result set in this case would be

notUpdated (5th column)
2

The above is just a subset of data, there are 131 rows.

How do I put “notUpdated” side by side with the first sql to make the results like this?

Bldg floor room total not updated
2 1 7 3 2

I would love to add a sixth column which gives me the percentage of computers in each row that have been updated. (column 5 / column 4) But I am not worried about that now.

I know this is a long post but it is a little complicated to explain.

Any help would be greatly appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-05 : 05:09:28

[code]declare @Location table (Comp_id varchar(255), Bldg int, [Floor] int, Room int)
declare @Status table (Comp_id varchar(255), [Version] int)

insert @Location
select 'P123',2,1,7 union all
select 'P456',2,1,7 union all
select 'P789',2,1,7 union all
select 'P666',3,1,5 union all
select 'P667',3,1,5

insert @Status
select 'P123',11 union all
select 'P456',11 union all
select 'P456',12 union all
select 'P789',11 union all
select 'P666',11 union all
select 'P667',12

declare @actVersion int
set @actVersion = 12
select
Bldg,
[Floor],
Room,
count(*) as total,
sum(case when [Version] < @actVersion then 1 else 0 end) as NotUpdated
from
(
select
l.Bldg,
l.[Floor],
l.Room,
l.Comp_id,
s.[Version]
from @Location l
join (select Comp_id, max([Version]) as [Version]
from @Status
group by Comp_id)s
on s.Comp_id = l.Comp_id
)dt
group by Bldg,[Floor],Room
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources