Extremely 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

SQL Server 2000 Forums

SQL Server Development (2000)

Extremely Complex Query

Author

Topic

StephanieJones
Starting Member

12 Posts

Posted - 2004-04-30 : 14:23:19

I need help with with this extremely complex query (to me it is). I have part of the query working - which is what is shown in red below. But my problem is I want the status name to show based on the max statusID. (Boy I know that this is confusing!)

I have the statuses in a table called ProjectStatus and it just lists the available statuses and contains the following columns: StatusID, Status. The statusID(PK) and is the linked to the DStatusID in the Deployment Table, the TStatusID in the Testing Table and RStatusID in the Requirements Table. I want to show the name of the Max(*StatusID) not just the number.

SELECT (SELECT MAX(DEPLOYMENT.DStatusID)
FROM DEPLOYMENT INNER JOIN
ProjectStatus ON DStatusID = StatusID
WHERE fPROJECTid = PROJECTID) AS MaxDepStat,
(SELECT MAX(TStatusID)
FROM TESTING
WHERE fPROJECTID = PROJECTID) AS MAXTestStat,
(SELECT MAX(RStatusID)
FROM Requirements
WHERE fPROJECTID = PROJECTID) AS MAXReqStat, ProjectCode, ProjectID
FROM PROJECTS
ORDER BY ProjectID

Can someone help me please?

Thanks,
Stephanie

Stephanie Jones

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-30 : 15:40:25

I can't test this, but:

SELECT	x.ProjectID
	, x.ProjectCode
	, y.Status as 'MaxDepStatus'
	, z.Status as 'MaxTestStatus'
	, xx.Status as 'MaxReqStatus'
FROM	(
	SELECT	a.ProjectID
		, a.ProjectCode
		, MAX(b.DStatusID) 'MaxDepStat'
		, MAX(c.TStatusID) 'MaxTestStat'
		, MAX(d.RStatusID) 'MaxReqStat'
	FROM	PROJECTS a LEFT JOIN DEPLOYMENT b
		  ON a.ProjectID = b.fProjectID
		LEFT JOIN TESTING c
		  ON a.ProjectID = c.fProjectID
		LEFT JOIN Requirements d
		  ON a.ProjectID = d.fProjectID
	GROUP BY a.ProjectID, a.ProjectCode
	) x
	LEFT JOIN ProjectStatus y
	  ON x.MaxDepStat = y.StatusID
	LEFT JOIN ProjectStatus z
	  ON x.MaxTestStat = z.StatusID
	LEFT JOIN ProjectStatus xx
	  ON x.MaxReqStat = xx.StatusID
ORDER BY x.ProjectID

StephanieJones
Starting Member

12 Posts

Posted - 2004-04-30 : 15:56:44

OH MY GOODNESS THIS WORKED!!!!!! I HAVE STRUGGLED OVER THIS ALL DAY!

THANKS SO MUCH FOR YOUR ASSISTANCE!

Stephanie Jones

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-30 : 16:20:36

Cool, I'm glad. Thanks for letting me know it worked.

StephanieJones
Starting Member

12 Posts

Posted - 2004-05-10 : 23:56:15

Hi,

You helped me a few days ago work out this extremely difficult (for me) query. Now I need to add one filter to this thing. I only want records returned that have the string "yes" in the IssueStatus field.

I tried hard to dissect what you did for me and I could not make it happen. Here is the original:

SELECT x.ProjectID, x.ProjectCode, x.ProjectName, y.Status AS MaxDepStatus, z.Status AS MaxTestStatus, xx.Status AS MaxReqStatus,
yy.Status AS MaxSuppSiteStatus, zz.Status AS MaxSECStatus
FROM (SELECT a.ProjectID, a.ProjectCode, a.ProjectName, MAX(b.DStatusID) 'MaxDepStat', MAX(c.TStatusID) 'MaxTestStat', MAX(d .RStatusID)
'MaxReqStat', MAX(e.SStatusID) 'MaxSuppSiteStatus', MAX(f.SECStatusID) 'MaxSECStatus'
FROM PROJECTS a LEFT JOIN
DEPLOYMENT b ON a.ProjectID = b.fProjectID LEFT JOIN
TESTING c ON a.ProjectID = c.fProjectID LEFT JOIN
Requirements d ON a.ProjectID = d .fProjectID LEFT JOIN
SitePrep e ON a.ProjectID = e.fProjectID LEFT JOIN
SECURITY f ON a.ProjectID = f.fProjectID

GROUP BY a.ProjectID, a.ProjectCode, a.ProjectName) x LEFT OUTER JOIN
PROJECTSTATUS y ON x.MaxDepStat = y.StatusID LEFT OUTER JOIN
PROJECTSTATUS z ON x.MaxTestStat = z.StatusID LEFT OUTER JOIN
PROJECTSTATUS xx ON x.MaxReqStat = xx.StatusID LEFT OUTER JOIN
PROJECTSTATUS yy ON x.MaxSuppSiteStatus = yy.StatusID LEFT OUTER JOIN
PROJECTSTATUS zz ON x.MaxSECStatus = zz.StatusID
ORDER BY x.ProjectCode

If I was writing a simple query like this, I would write:

Select fProjectID, Max(DStatusID) FROM DEPLOYMENT WHERE IssueStatus
= 'Yes'
I need this for each of these subqueries

Would you mind helping me one more time?

Thanks,

Stephanie Jones

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-11 : 07:51:23

SELECT
x.ProjectID,
x.ProjectCode,
y.Status as 'MaxDepStatus',
z.Status as 'MaxTestStatus',
xx.Status as 'MaxReqStatus'
FROM (
SELECT
a.ProjectID,
a.ProjectCode,
MAX(b.DStatusID) 'MaxDepStat',
MAX(c.TStatusID) 'MaxTestStat',
MAX(d.RStatusID) 'MaxReqStat'
FROM
PROJECTS a
INNER JOIN DEPLOYMENT b ON a.ProjectID = b.fProjectID
AND b.IssueStatus = 'Yes'
LEFT JOIN TESTING c ON a.ProjectID = c.fProjectID
LEFT JOIN Requirements d ON a.ProjectID = d.fProjectID
GROUP BY
a.ProjectID, a.ProjectCode
) x
LEFT JOIN ProjectStatus y ON x.MaxDepStat = y.StatusID
LEFT JOIN ProjectStatus z ON x.MaxTestStat = z.StatusID
LEFT JOIN ProjectStatus xx ON x.MaxReqStat = xx.StatusID
ORDER BY
x.ProjectID

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

StephanieJones
Starting Member

12 Posts

Posted - 2004-05-11 : 09:52:14

This worked just fine! I can't tell you enough how much I appreciate your help. This site has a bunch of knowledgeable people that are so willing to help and to help me get things correct the first time!

When I popped this query in my Query Analyzer, it worked brilliantly. I was very intimidated by the first query and did not want to jack with it, but it is working great.

Thanks so much!

Stephanie Jones

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources