Here is a stab at what I think you are asking for. Note I removed one of your status columns (for demonstration). This makes the
assumption that the maximum entry for status_add_date also has the value '5' for status_state. You want to filter the
status_state=5 entries after you determine what the final state is for your client/project combinations. Here is a sql server example.
declare @Clients Table (client_id int identity primary key, client_name
varchar(10))
declare @Projects Table (project_id int identity primary key,
project_client_idfk int, project_name varchar(10))
declare @Status table (status_id int identity primary key, status_state int,
status_project_idfk int, status_add_date datetime)
set nocount on
insert into @Clients values ('Client 1')
insert into @Clients values ('Client 2')
insert into @Clients values ('Client 3')
insert into @Projects values (1, 'Proj 1a')
insert into @Projects values (1, 'Proj 1b')
insert into @Projects values (2, 'Proj 2a')
insert into @Projects values (2, 'Proj 2b')
insert into @Projects values (3, 'Proj 3a')
insert into @Status values (1, 1, '2006-09-01')
insert into @Status values (3, 1, '2006-09-02')
insert into @Status values (1, 2, '2006-09-03')
insert into @Status values (3, 2, '2006-09-04')
insert into @Status values (1, 3, '2006-09-01')
insert into @Status values (2, 3, '2006-09-05')
insert into @Status values (1, 4, '2006-09-01')
insert into @Status values (4, 4, '2006-09-06')
insert into @Status values (1, 5, '2006-09-01')
insert into @Status values (5, 5, '2006-09-07')
select
c.client_name, p.project_name, s.status_state
from
@Clients c
inner join
@Projects p on c.client_id = p.project_client_idfk
inner join
@Status s on p.project_id = s.status_project_idfk
where
exists (
select 1 from @Status sx
where
p.project_id = sx.status_project_idfk
group by
sx.status_project_idfk
having
s.status_add_date = max(sx.status_add_date)
)
and s.status_state <> 5
order by
c.client_name, p.project_name, s.status_state
Jake McKee wrote:
OK, so where's what I have:
Clients Table (client_id, client_name)
Projects Table (project_id, project_client_idfk, project_name)
Status table (status_id, status_content, status_state,
status_project_idfk, status_add_date)
One client can have multiple projects. A single project can have
multiple status updates.
I'm trying to create a query that will output the following:
For each client, spit out all projects where the latest status update is
not of status type 5 (closed)
So:
Client 1
- project 1
- project 3
Client 2
- project 4
- project 6
...and so on. Projects 2 and 5 have a status entry that's set to a value
of 5, therefore are closed and don't need to be displayed.
How can I make this happen?
Thanks!
Jake
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
