August 13, 2003 at 9:57 am
I have a need to count all laptops and all desktops group by a location!
How do I accomplish this? Here is what I have but the same number is returned for all location!
Select Distinct
SMSSite.SiteName As [Site Name],
(Select
Count(SYSEncl.ChassisTypes0)
From
v_GS_System_Enclosure SYSEncl
Inner Join v_RA_System_SMSInstalledSites Site On SYSEncl.ResourceID = Site.ResourceID
Inner Join v_Site SMSSite On Site.SMS_Installed_Sites0 = SMSSite.SiteCode
Where
Cast (SYSEncl.ChassisTypes0 as int) = 3
Or Cast (SYSEncl.ChassisTypes0 as int) = 6
Or Cast (SYSEncl.ChassisTypes0 as int) = 7
Or Cast (SYSEncl.ChassisTypes0 as int) = 15) As [Desktop],
(Select
Count(SYSEncl.ChassisTypes0)
From
v_GS_System_Enclosure SYSEncl
Inner Join v_RA_System_SMSInstalledSites Site On SYSEncl.ResourceID = Site.ResourceID
Inner Join v_Site SMSSite On Site.SMS_Installed_Sites0 = SMSSite.SiteCode
Where
Cast (SYSEncl.ChassisTypes0 as int) = 8
Or Cast (SYSEncl.ChassisTypes0 as int) = 9
Or Cast (SYSEncl.ChassisTypes0 as int) = 10
Or Cast (SYSEncl.ChassisTypes0 as int) = 12
Or Cast (SYSEncl.ChassisTypes0 as int) = 14
Or Cast (SYSEncl.ChassisTypes0 as int) = 18) As [Laptops]
From
v_Site SMSSite
Group By
SMSSite.SiteName
Thanks in advance
August 13, 2003 at 10:39 am
The problem is that nothing in your subqueries matches your counts to a specific location. One solution might be simply changing the alias on v_Site for the subqueries to, say subSite, and adding:
AND subSite.SiteName = SMSSite.SiteName
However, I'd be interested in knowing if the following works. the approach I took was to join the system enclosure table to SMSSite and Site twice, once only considering the desktop records, once only considering the laptop records. Using a Left Outer Join (I want all the SMSSite and Site records, but only desk and lap records if they exist, and nulls for those fields if they don't), and knowing that count() adds zero for fields with null values, this should work:
Select Distinct
SMSSite.SiteName As [Site Name],
count(desk.ChassisTypes0) as [Desktop],
count(lap.ChassisTypes0) as [Laptop]
from v_Site SMSSite
INNER JOIN v_RA_System_SMSInstalledSites Site
ON Site.SMS_Installed_Sites0 = SMSSite.SiteCode
LEFT OUTER JOIN v_GS_System_Enclosure desk
ON ( desk.ResourceID = Site.ResourceID
AND ( Cast (desk.ChassisTypes0 as int) = 3
Or Cast (desk.ChassisTypes0 as int) = 6
Or Cast (desk.ChassisTypes0 as int) = 7
Or Cast (desk.ChassisTypes0 as int) = 15
)
)
LEFT OUTER JOIN v_GS_System_Enclosure lap
ON ( lap.ResourceID = Site.ResourceID
AND ( Cast (lap.ChassisTypes0 as int) = 8
Or Cast (lap.ChassisTypes0 as int) = 9
Or Cast (lap.ChassisTypes0 as int) = 10
Or Cast (lap.ChassisTypes0 as int) = 12
Or Cast (lap.ChassisTypes0 as int) = 14
Or Cast (lap.ChassisTypes0 as int) = 18
)
)
GROUP BY SMSSite.SiteName
Obviously, not having your data, it's a bit hard for me to test....
R David Francis
August 13, 2003 at 10:42 am
If I am reading this query correctly, you need to remove the reference to the v_site table in the subqueries, instead using the value from the v_site table in the outer query, like this:
Select Distinct
SMSSite.SiteName As [Site Name],
(Select
Count(SYSEncl.ChassisTypes0)
From
v_GS_System_Enclosure SYSEncl
Inner Join v_RA_System_SMSInstalledSites Site On SYSEncl.ResourceID = Site.ResourceID
Where
Site.SMS_Installed_Sites0 = SMSSite.SiteCode AND (
Cast (SYSEncl.ChassisTypes0 as int) = 3
Or Cast (SYSEncl.ChassisTypes0 as int) = 6
Or Cast (SYSEncl.ChassisTypes0 as int) = 7
Or Cast (SYSEncl.ChassisTypes0 as int) = 15)
) As [Desktop],
(Select
Count(SYSEncl.ChassisTypes0)
From
v_GS_System_Enclosure SYSEncl
Inner Join v_RA_System_SMSInstalledSites Site On SYSEncl.ResourceID = Site.ResourceID
Where
Site.SMS_Installed_Sites0 = SMSSite.SiteCode AND (
Cast (SYSEncl.ChassisTypes0 as int) = 8
Or Cast (SYSEncl.ChassisTypes0 as int) = 9
Or Cast (SYSEncl.ChassisTypes0 as int) = 10
Or Cast (SYSEncl.ChassisTypes0 as int) = 12
Or Cast (SYSEncl.ChassisTypes0 as int) = 14
Or Cast (SYSEncl.ChassisTypes0 as int) = 18)
) As [Laptops]
From
v_Site SMSSite
Group By
SMSSite.SiteName
August 13, 2003 at 5:43 pm
Thank you very much...both! I will try both solutions tomorrow and will post back the results!
Again, thanks
Rene
August 13, 2003 at 8:42 pm
The results.....
Brent, by removing the join in the sub query, I only had to add SMSSite.SiteCode in the Group By but apart from that it works great!
rd, like Brent! thanks. I also tried your second approach and it also works perfectly!
Thanks again to both of you! I just couldn't figure it out!
Regards,
Rene
August 13, 2003 at 9:53 pm
Armed with this, this is the query built!
I was not comfortable enough with your query (rd) so I stuck with the original with the changes Brent mentioned.
Create Procedure usp_SMSR6120 -- Count Machines Types by Sites
-- no variables
As
Select Distinct
SmsSite.SiteName As [Site Name],
(Select
Count(SysEncl.ChassisTypes0)
From
v_GS_System_Enclosure SysEncl
Inner Join v_RA_System_SmsInstalledSites Site On SysEncl.ResourceID = Site.ResourceID
Inner Join v_R_System Sys On SysEncl.ResourceID = Sys.ResourceID
Where
Site.SMS_Installed_Sites0 = SMSSite.SiteCode
And (Cast (SysEncl.ChassisTypes0 As Int) = 6
Or Cast (SysEncl.ChassisTypes0 As Int) = 7
Or Cast (SysEncl.ChassisTypes0 As Int) = 15
Or Cast (SysEncl.ChassisTypes0 As Int) = 23)
And Sys.Operating_System_Name_and0 Like '%Server%') As [Servers],
(Select
Count(SysEncl.ChassisTypes0)
From
v_GS_System_Enclosure SysEncl
Inner Join v_RA_System_SmsInstalledSites Site On SysEncl.ResourceID = Site.ResourceID
Inner Join v_R_System Sys On SysEncl.ResourceID = Sys.ResourceID
Where
Site.SMS_Installed_Sites0 = SMSSite.SiteCode
And (Cast (SysEncl.ChassisTypes0 As Int) = 3
Or Cast (SysEncl.ChassisTypes0 As Int) = 4
Or Cast (SysEncl.ChassisTypes0 As Int) = 5
Or Cast (SysEncl.ChassisTypes0 As Int) = 6
Or Cast (SysEncl.ChassisTypes0 As Int) = 7
Or Cast (SysEncl.ChassisTypes0 As Int) = 15)
And Sys.Operating_System_Name_and0 Like '%Workstation%') As [Desktops],
(Select
Count(SysEncl.ChassisTypes0)
From
v_GS_System_Enclosure SysEncl
Inner Join v_RA_System_SMSInstalledSites Site On SysEncl.ResourceID = Site.ResourceID
Inner Join v_R_System Sys On SysEncl.ResourceID = Sys.ResourceID
Where
Site.SMS_Installed_Sites0 = SMSSite.SiteCode
And (Cast (SYSEncl.ChassisTypes0 As Int) = 8
Or Cast (SYSEncl.ChassisTypes0 As Int) = 9
Or Cast (SYSEncl.ChassisTypes0 As Int) = 10
Or Cast (SYSEncl.ChassisTypes0 As Int) = 11
Or Cast (SYSEncl.ChassisTypes0 As Int) = 12
Or Cast (SYSEncl.ChassisTypes0 As Int) = 14
Or Cast (SYSEncl.ChassisTypes0 As Int) = 18)
And Sys.Operating_System_Name_and0 Like '%Workstation%') As [Laptops]
From
v_Site SmsSite
Group By
SmsSite.SiteName, SmsSite.SiteCode
GO
Thanks a million!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply