complex query... - 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

complex query...

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-10-13 : 12:23:27

How do I join one more take, but I need to select just the most recent status for each project, from multiple status reports..

SELECT a.project_ID,b.Constraint_ID

FROM Project a
INNER JOIN Users c ON a.ProjectManager=c.UserName
INNER JOIN Constraints b on a.project_id =b.project_id
ORDER BY a.project_id, b.constraint_id

The above code will select a project (project_ID), then each project has one projectmanager, then each project has multiple constraints.

This results in the following list:

Project_ID constraint_ID
2 419
4 161
4 172
4 405

Now each project also has several status reports. I would like to join the most recent status from the most recent status report.

the results should be:

Project_ID constraint_ID Status
2 419 Green
4 161 Yellow
4 172 Yellow
4 405 Yellow

Status reports have a project_ID, HealthIndicator, and StatusDate.

So I want to sort the status report by project ID, then by status date, then select the healthindicator from the top status report for each project.

I have gotten this far, but this only selects the healthindicator for the most recent status report.

SELECT a.project_ID,b.Constraint_ID
,PMHealth = (SELECT TOP 1 HealthIndicator FROM StatusReports ORDER BY StatusDate DESC )

FROM Project a
INNER JOIN Constraints b on a.project_id =b.project_id
INNER JOIN Users c ON a.ProjectManager=c.UserName

ORDER BY a.project_id, b.constraint_id

This produces:
Project_ID constraint_ID Status
2 419 Green
4 161 Green
4 172 Green
4 405 Green

thank you for the help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 14:33:58

You have not given information about the keys to match from project to StatusReports, but maybe something like this:

SELECT a.project_ID,b.Constraint_ID, dt.Status

FROM Project a 
INNER JOIN Users c ON a.ProjectManager=c.UserName
INNER JOIN Constraints b on a.project_id =b.project_id
inner join (select project_id, Status, row_number() over (partition by project_id order by Status_Date desc) as rnum from StatusReports)dt
on dt.project_id = a.project_id and dt.rnum=1
ORDER BY a.project_id, b.constraint_id

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-13 : 14:43:20

Is Oktoberfest over?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 14:44:30

Yes, I think so ...

But YOUR personal Fest is coming closer...

No, you're never too old to Yak'n'Roll if you're too young to die.

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-10-13 : 17:31:50

webfred..you guessed right...project is joined to status report by project_id...

but I get an error...

a select @@version gives me... I thought I was on 2005..(blah)

'row_number' is not a recognized funciton name...

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-14 : 08:26:18

[code]
SELECT a.project_ID,b.Constraint_ID, dt.Status

FROM Project a
INNER JOIN Users c ON a.ProjectManager=c.UserName
INNER JOIN Constraints b on a.project_id =b.project_id
inner join StatusReports dt
on dt.project_id = a.project_id and
dt.Status_Date = (select max(Status_Date)
from StatusReports sr
where sr.project_id = dt.project_id)

ORDER BY a.project_id, b.constraint_id

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-10-14 : 10:12:21

WebFred,

Worked like a champ!..thank you very much...I see my initial problem...both in syntax..and putting the subquery in the select, rather than in the join....

^5

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-14 : 10:19:36

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources