sql server mseq: Hi everyone,

I'm working on a report that gives a summary of data in a sql server
database. The structure of the database is the following:

A company has one to many investments. These investments are for a certain
sector, are of a certain nature and are in a certain state. This gives us a
data model that ressembles this:

tblInvestments has a foreign key for the company, the sector and the state
tables.

Now, the report I have to do is a detail of the investments by sector. For
example, say we have "Primary", "Secondary", and "Manufacturing" as sectors,
the report must look like this:

Total investments for company x : 15

Sector Investments Active Total
invested
Primary 4 3 130
000$
Secondary 3 3 250
000$
Manufacturing 8 6 140
000$

The Investments column is simply a count of the investments for a particular
company. The Active column lists the count of all the investments that are
in the state "Active" and the Total invested is the sum of a field in the
Investments table for only the investments that are active. I've been
struggling for this problem for a while now and I'd like some input. Is
there a way to:

1) Do this in 1 view
2) List all the sectors even if there is no investments (to list a zero for
the other columns)

I tried to do two separate views for the simple total of the investments and
for the count of the investments and the total amount, but I can't get them
back together in one query (one line for each investment).

Any help would be appreciated.

Éric.

hi eric,

It would have been more easier to give you solution, if you would have
posted sample table structure and data alongwith expected result set.

however on the basis of some information provided by you , the query given
in following example might be what you are looking for.

--sample data
create table tblInvestments (companyid int, sectorid int,
stateid int,
status varchar(10),
amount int)
go
create table company (companyid int primary key, companyname varchar(500))
go
create table sector(sectorid int primary key, sectorname varchar(500))
go
create table state(stateid int primary key, statename varchar(500))
go
insert into company values(1,'company1')
insert into company values(2,'company2')
insert into company values(3,'company3')
go
insert into sector values (1,'primary')
insert into sector values (2,'secondary')
insert into sector values (3,'manufacturing')
go
insert into state values (1,'CA')
insert into state values (2,'NJ')
insert into state values (3,'MA')
go
insert into tblinvestments values(1,1,1,'active',1000)
insert into tblinvestments values(1,1,1,'inactive',1000)
insert into tblinvestments values(2,1,1,'active',1000)
insert into tblinvestments values(1,2,1,'active',2000)
insert into tblinvestments values(2,2,1,'active',2000)
go
--required query
select b.companyname ,a.sectorname,
sum (case when c.companyid is null then 0 else 1 end ) 'investment' ,
sum(case c.status when 'active' then 1 else 0 end) 'active',
sum(case c.status when 'active' then c.amount else 0 end) 'total invested'
from sector a cross join company b
left outer join tblInvestments c
on a.sectorid = c.sectorid and b.companyid = c.companyid
group by a.sectorname, b.companyname
order by 1,2
compute sum (sum (case when c.companyid is null then 0 else 1 end )) by
b.companyname




--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com