Need to rollup by site - 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

General SQL Server Forums

New to SQL Server Programming

Need to rollup by site

Author

Topic

biogem
Starting Member

13 Posts

Posted - 2009-12-07 : 16:29:41

SELECT Sites.SiteName,
(select COUNT (*) from tenant where tenanttype=2 and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%' GROUP BY SITE) AS [OFFICE],
(select COUNT (*) from tenant where tenanttype IN ( 1, 3) and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%' GROUP BY SITE) AS [TELECOM],
(select COUNT (*) from tenant where tenanttype=4 and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%' GROUP BY SITE) AS [ANY2]

I forgot to add the result set(this is what I need it to look like)

FROM Sites INNER JOIN Tenant ON Sites.SiteID = Tenant.Site
group by TENANT.site, sitename

I can get the results using a temp table, but not via case or ctu.

Here's the temp table query.
Create Table #tmp1
(
site char(50),
telecom int
)
insert into #tmp1 (site, telecom)
select site, COUNT(*) AS telecom
from tenant
where (tenanttype=1 or tenanttype=3) and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%'
group by site

-- Office
Create Table #tmp2
(
site char(50),
office int,
)
insert into #tmp2 (site, office)
select site, COUNT(*) AS office
from tenant
where tenanttype=2 and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%'
group by site

-- Any2
Create Table #tmp3
(
site char(50),
any2 int
)
insert into #tmp3 (site, any2)
select site, COUNT(*) AS any2
from tenant
where tenanttype=4 and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%'
group by site

select s.sitename, t1.telecom, t2.office, t3.any2
from sites s left outer join
#tmp1 t1 on t1.site = s.siteid left outer join
#tmp2 t2 on t2.site = s.siteid left outer join
#tmp3 t3 on t3.site = s.siteid
where s.Internal is null or s.Internal = 0

drop table #tmp1
drop table #tmp2
drop table #tmp3

The result set I need.
sitename telecom office any2
One Wilshire 295 5 40
55 S. Market 113 7 10
1275 K Street 51 0 1
2115 NW 22nd Street 32 0 0
900 N. Alameda 65 4 2
1656 McCarthy 31 0 1
427 S. LaSalle 60 0 0
70 Innerbelt 38 0 0
32 Ave. of the Americas 18 0 1
12100 Sunrise Valley 60 3 0
2901 Coronado Dr 0 8 0
Coronado Stender Business Park 0 1 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-10 : 14:10:23

[code]SELECT Sites.SiteName,
COUNT (case when tenanttype=2 and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%' THEN 1 ELSE NULL END) AS [OFFICE],
COUNT (case when tenanttype IN ( 1, 3) and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%' THEN 1 ELSE NULL END) AS [TELECOM],
COUNT (case when tenanttype=4 and ([non-active] is null or [non-active] = 0) and not tenantname like 'CoreSite%' THEN 1 ELSE NULL END) AS [ANY2]
FROM Sites INNER JOIN Tenant ON Sites.SiteID = Tenant.Site
group by sitename
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources