alert for database drop

Hello All,
SQL2K SP3a
I would like to create an alert when a database is dropped from my sql
servers. is it possible?
Thanks,
sqlgirl
sqlgirl (nice nickname!)
If you can create a trigger on sysobjects - then yes. Any object drop would
result in deleting corresponding entry from sysobjects table. Please let me
know if it helps.
Leon Shargorodsky
"Biva" wrote:

> Hello All,
> SQL2K SP3a
> I would like to create an alert when a database is dropped from my sql
> servers. is it possible?
> Thanks,
> sqlgirl
>
>
|||I don't think you can create a trigger on system tables. However, you could
have a job scheduled that runs every minute or every five minutes that
checks count(*) from sysdatabases and logs it. If this count is ever
*lower* than the last time the job ran, a database has been removed... send
an alert. If you shadow copy the database names from sysdatabases on each
run (maybe in two separate tables), you could even determine which
database(s) got dropped.
http://www.aspfaq.com/
(Reverse address to reply.)
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> sqlgirl (nice nickname!)
> If you can create a trigger on sysobjects - then yes. Any object drop
would
> result in deleting corresponding entry from sysobjects table. Please let
me[vbcol=seagreen]
> know if it helps.
> Leon Shargorodsky
> "Biva" wrote:
|||Hi
Biva, wait for SQL Server 2005 and you will be able to define a trigger on
drop database.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uSQpFPdGFHA.3092@.tk2msftngp13.phx.gbl...
> I don't think you can create a trigger on system tables. However, you
could
> have a job scheduled that runs every minute or every five minutes that
> checks count(*) from sysdatabases and logs it. If this count is ever
> *lower* than the last time the job ran, a database has been removed...
send
> an alert. If you shadow copy the database names from sysdatabases on each
> run (maybe in two separate tables), you could even determine which
> database(s) got dropped.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> would
> me
>
|||> Biva, wait for SQL Server 2005 and you will be able to define a trigger on
> drop database.
Actually, I think you will need to use event notifications, but yes you will
be able to get it done with less work than my proposal (assuming you are
willing to wait for SQL Server 2005 to go RTM :-)).
DDL triggers only cover events that can be in a transaction, and I don't
think CREATE/DROP DATABASE fall under that category.

alert for database drop

Hello All,
SQL2K SP3a
I would like to create an alert when a database is dropped from my sql
servers. is it possible?
Thanks,
sqlgirlsqlgirl (nice nickname!)
If you can create a trigger on sysobjects - then yes. Any object drop would
result in deleting corresponding entry from sysobjects table. Please let me
know if it helps.
Leon Shargorodsky
"Biva" wrote:
> Hello All,
> SQL2K SP3a
> I would like to create an alert when a database is dropped from my sql
> servers. is it possible?
> Thanks,
> sqlgirl
>
>|||I don't think you can create a trigger on system tables. However, you could
have a job scheduled that runs every minute or every five minutes that
checks count(*) from sysdatabases and logs it. If this count is ever
*lower* than the last time the job ran, a database has been removed... send
an alert. If you shadow copy the database names from sysdatabases on each
run (maybe in two separate tables), you could even determine which
database(s) got dropped.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> sqlgirl (nice nickname!)
> If you can create a trigger on sysobjects - then yes. Any object drop
would
> result in deleting corresponding entry from sysobjects table. Please let
me
> know if it helps.
> Leon Shargorodsky
> "Biva" wrote:
> > Hello All,
> > SQL2K SP3a
> >
> > I would like to create an alert when a database is dropped from my sql
> > servers. is it possible?
> >
> > Thanks,
> > sqlgirl
> >
> >
> >|||Hi
Biva, wait for SQL Server 2005 and you will be able to define a trigger on
drop database.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uSQpFPdGFHA.3092@.tk2msftngp13.phx.gbl...
> I don't think you can create a trigger on system tables. However, you
could
> have a job scheduled that runs every minute or every five minutes that
> checks count(*) from sysdatabases and logs it. If this count is ever
> *lower* than the last time the job ran, a database has been removed...
send
> an alert. If you shadow copy the database names from sysdatabases on each
> run (maybe in two separate tables), you could even determine which
> database(s) got dropped.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> > sqlgirl (nice nickname!)
> >
> > If you can create a trigger on sysobjects - then yes. Any object drop
> would
> > result in deleting corresponding entry from sysobjects table. Please let
> me
> > know if it helps.
> >
> > Leon Shargorodsky
> >
> > "Biva" wrote:
> >
> > > Hello All,
> > > SQL2K SP3a
> > >
> > > I would like to create an alert when a database is dropped from my sql
> > > servers. is it possible?
> > >
> > > Thanks,
> > > sqlgirl
> > >
> > >
> > >
>|||> Biva, wait for SQL Server 2005 and you will be able to define a trigger on
> drop database.
Actually, I think you will need to use event notifications, but yes you will
be able to get it done with less work than my proposal (assuming you are
willing to wait for SQL Server 2005 to go RTM :-)).
DDL triggers only cover events that can be in a transaction, and I don't
think CREATE/DROP DATABASE fall under that category.

alert for database drop

Hello All,
SQL2K SP3a
I would like to create an alert when a database is dropped from my sql
servers. is it possible?
Thanks,
sqlgirlsqlgirl (nice nickname!)
If you can create a trigger on sysobjects - then yes. Any object drop would
result in deleting corresponding entry from sysobjects table. Please let me
know if it helps.
Leon Shargorodsky
"Biva" wrote:

> Hello All,
> SQL2K SP3a
> I would like to create an alert when a database is dropped from my sql
> servers. is it possible?
> Thanks,
> sqlgirl
>
>|||I don't think you can create a trigger on system tables. However, you could
have a job scheduled that runs every minute or every five minutes that
checks count(*) from sysdatabases and logs it. If this count is ever
*lower* than the last time the job ran, a database has been removed... send
an alert. If you shadow copy the database names from sysdatabases on each
run (maybe in two separate tables), you could even determine which
database(s) got dropped.
http://www.aspfaq.com/
(Reverse address to reply.)
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> sqlgirl (nice nickname!)
> If you can create a trigger on sysobjects - then yes. Any object drop
would
> result in deleting corresponding entry from sysobjects table. Please let
me[vbcol=seagreen]
> know if it helps.
> Leon Shargorodsky
> "Biva" wrote:
>|||Hi
Biva, wait for SQL Server 2005 and you will be able to define a trigger on
drop database.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uSQpFPdGFHA.3092@.tk2msftngp13.phx.gbl...
> I don't think you can create a trigger on system tables. However, you
could
> have a job scheduled that runs every minute or every five minutes that
> checks count(*) from sysdatabases and logs it. If this count is ever
> *lower* than the last time the job ran, a database has been removed...
send
> an alert. If you shadow copy the database names from sysdatabases on each
> run (maybe in two separate tables), you could even determine which
> database(s) got dropped.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> would
> me
>|||> Biva, wait for SQL Server 2005 and you will be able to define a trigger on
> drop database.
Actually, I think you will need to use event notifications, but yes you will
be able to get it done with less work than my proposal (assuming you are
willing to wait for SQL Server 2005 to go RTM :-)).
DDL triggers only cover events that can be in a transaction, and I don't
think CREATE/DROP DATABASE fall under that category.

aggregate query help

--drop table bldg
--drop table room
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
bldg org Area
-- -- ---
B1 Org1 100
B1 Org2 300
B2 Org1 400
B2 Org2 600
that works fine. however, i need two additional columns in the output.
one column is the total area of the building.
the other is the percentage of usage by the org of each building.
the output i need would look like this.
bldg org
Area Bldg Area Org Pct
-- -- ---
B1 Org1
100 400 25.00
B1 Org2
300 400 75.00
B2 Org1
400 1000 40.00
B2 Org2
600 1000 60.00
any way to do this in a select statement, i.e. no stored procedures or
cursors?Try something like this:
set nocount on
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm
go
select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
100.00 PCT
from
(select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm, t1.bldg_id) t3
join
(select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org, t1.bldg_id) t4
on t3.bldg_nm = t4.bldg_nm
order by t3.bldg_nm, t4.org
drop table bldg
drop table room
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ch" <ch@.dontemailme.com> wrote in message
news:4152E589.90B4DB41@.dontemailme.com...
> --drop table bldg
> --drop table room
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
>
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> bldg org Area
> -- -- ---
> B1 Org1 100
> B1 Org2 300
> B2 Org1 400
> B2 Org2 600
>
> that works fine. however, i need two additional columns in the output.
> one column is the total area of the building.
> the other is the percentage of usage by the org of each building.
> the output i need would look like this.
> bldg org
> Area Bldg Area Org Pct
> -- -- ---
> B1 Org1
> 100 400 25.00
> B1 Org2
> 300 400 75.00
> B2 Org1
> 400 1000 40.00
> B2 Org2
> 600 1000 60.00
> any way to do this in a select statement, i.e. no stored procedures or
> cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||> because i don't care for the inner join syntax ;)
That is a mistake IMO. The whole database world is moving away from the old join syntax. Of course
it is up to you, but perhaps time will come when you work in a project/organization that don't like
the old join syntax... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41530D1C.953CF4F1@.dontemailme.com...
> thanks, although i had to change you inner join syntax to where clauses
> because i don't care for the inner join syntax ;)
>
> "Gregory A. Larsen" wrote:
> >
> > Try something like this:
> >
> > set nocount on
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm
> > go
> >
> > select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> > 100.00 PCT
> > from
> >
> > (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm, t1.bldg_id) t3
> >
> > join
> >
> > (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> >
> > on t3.bldg_nm = t4.bldg_nm
> >
> > order by t3.bldg_nm, t4.org
> >
> > drop table bldg
> > drop table room
> >
> > --
> >
> > ----
> > ----
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:4152E589.90B4DB41@.dontemailme.com...
> > >
> > > --drop table bldg
> > > --drop table room
> > >
> > > create table bldg
> > > (
> > > bldg_id int not null primary key,
> > > bldg_nm varchar(20)
> > > )
> > > go
> > >
> > > create table room
> > > (
> > > bldg_id int not null,
> > > room_num varchar(10) not null,
> > > area numeric,
> > > org varchar(10)
> > > )
> > > go
> > > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > > go
> > >
> > > insert into bldg values (1, 'B1')
> > > insert into bldg values (2, 'B2')
> > > insert into room values (1, '1RM1', 100.00, 'Org1')
> > > insert into room values (1, '1RM2', 50.00, 'Org2')
> > > insert into room values (1, '1RM3', 250.00, 'Org2')
> > > insert into room values (2, '2RM1', 400.00, 'Org1')
> > > insert into room values (2, '2RM2', 600.00, 'Org2')
> > > go
> > >
> > >
> > > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > > group by t1.bldg_nm, t2.org
> > > order by t1.bldg_nm, t2.org
> > > go
> > >
> > > bldg org Area
> > > -- -- ---
> > > B1 Org1 100
> > > B1 Org2 300
> > > B2 Org1 400
> > > B2 Org2 600
> > >
> > >
> > > that works fine. however, i need two additional columns in the output.
> > > one column is the total area of the building.
> > > the other is the percentage of usage by the org of each building.
> > > the output i need would look like this.
> > >
> > > bldg org
> > > Area Bldg Area Org Pct
> > > -- -- ---
> > > B1 Org1
> > > 100 400 25.00
> > > B1 Org2
> > > 300 400 75.00
> > > B2 Org1
> > > 400 1000 40.00
> > > B2 Org2
> > > 600 1000 60.00
> > >
> > > any way to do this in a select statement, i.e. no stored procedures or
> > > cursors?