Query Help - 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

Transact-SQL (2000)

Query Help

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-19 : 15:28:40

I have a table like the following

ProjectID :: EventID :: CompletedDate
1 :: 5 :: 10/12/2005
1 :: 6 :: 10/14/2005
1 :: 11 :: NULL
2 :: 2 :: 01/12/2005
2 :: 3 :: NULL

How can I in one query, get the Maximum CompletedDate AND Minimum NULL Complete Date Grouped By project. So, for the above data, I need to return

ProjectID :: CompletedDate :: [NULL CompleteDate]
1 :: 10/14/2005 :: NULL
2 :: 01/12/2005 :: NULL

If the data were

ProjectID :: EventID :: CompletedDate
1 :: 5 :: 10/12/2005
1 :: 6 :: 10/14/2005
1 :: 11 :: NULL
2 :: 2 :: 01/12/2005
2 :: 3 :: 01/15/2005

Then I don't want to return the row with no CompletedDate
So, it would only return
1 :: 10/14/2005 :: NULL

Thanks and any help is appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-19 : 16:00:10

SELECT ProjectID, MAX(CompletedDate) AS CompletedDate, 'NULL' AS 'NULL CompleteDate'
FROM YourTable
GROUP BY ProjectID

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-19 : 16:47:30

How about this:

SELECT ProjectID, MAX(CompletedDate) AS CompletedDate, CASE WHEN MIN(ISNULL(CompletedDate, 0)) = 0 THEN NULL ELSE MIN(CompletedDate) END AS NULLCompleteDate
FROM YourTable
GROUP BY ProjectID
HAVING CASE WHEN MIN(ISNULL(CompletedDate, 0)) = 0 THEN NULL ELSE MIN(CompletedDate) END IS NULL

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 09:01:12

I dont think I gave enough info. If anyone is willing to take a look again, I would appreciate it. Thanks

ProjectID :: EventID :: EventOrder :: EstimatedDate :: CompletedDate :: ActionID ::
1 :: 4 :: 1 :: 10/28/2005 :: 10/30/2005 :: 1
1 :: 3 :: 2 :: 11/13/2005 :: 11/11/2005 :: NULL
1 :: 2 :: 3 :: 11/14/2005 :: 11/15/2005 :: 2
1 :: 5 :: 4 :: 12/1/2005 :: NULL :: NULL
1 :: 6 :: 5 :: 12/10/2005 :: NULL :: 2

And what I need back is this
ProjectID :: MaxEventID :: MaxEventOrder:: MaxEstDate :: MaxCompleteDate :: MaxActionID :: MinEstDate :: MinActionID
1 :: 2 :: 3 :: 11/14/2005 :: 11/15/2005 :: 2 :: 12/1/2005 :: NULL

Those columns prefixed with Max are the belong to the record with of the Highest EventOrder that has a completed date. Those prefixed with Min belong to the lowest row that has NO CompletedDate. I hope this makes sense now.

What I need to ultimately do is to put this in a temp table, do a comparison between between the two, and update a field in a temp table to determine the project overall status. I truly appreciate any help in figuring out a method to do this. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 12:32:14

Please see this link that will show you how to post your table structure and sample data:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Once we have it in that format, we'll be able to quickly help you since we'll be able to replicate your problem on our own machines.

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-20 : 12:59:36

How about this, take 2:


SELECT Mx.MaxProjectID, Mx.MaxEventID, Mx.MaxEventOrder, Mx.MaxEstDate, Mx.MaxCompleteDate, Mx.MaxActionID, Mn.MinEstDate, Mn.MinActionID 
FROM	(SELECT Mx1.ProjectID AS MaxProjectID, Mx1.EventID AS MaxEventID, Mx1.EventOrder AS MaxEventOrder, Mx1.EstimatedDate AS MaxEstDate, Mx1.ActionID AS MaxActionID, Mx1.CompletedDate AS MaxCompleteDate 
	FROM YourTable Mx1 
	JOIN	(SELECT ProjectID, MAX(EventOrder) AS EventOrder FROM YourTable 
		WHERE CompletedDate IS NOT NULL 
		GROUP BY ProjectID) AS Mx2 
	ON Mx1.ProjectID = Mx2.ProjectID AND Mx1.EventOrder = Mx2.EventOrder) AS Mx
JOIN	(SELECT Mn1.ProjectID AS MinProjectID, Mn1.ActionID AS MinActionID, Mn1.EstimatedDate AS MinEstDate 
	FROM YourTable Mn1 
	JOIN	(SELECT ProjectID, MIN(EventOrder) AS EventOrder FROM YourTable 
		WHERE CompletedDate IS NULL 
		GROUP BY ProjectID) AS Mn2 
	ON Mn1.ProjectID = Mn2.ProjectID AND Mn1.EventOrder = Mn2.EventOrder) AS Mn 
ON Mx.MaxProjectID = Mn.MinProjectID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 13:02:17

I sure hope that's not the solution. Can you imagin the performance if YourTable is a large table?

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 13:06:22

Here we go, I provided sample data, as well as a sample select that will return the how I would want this data to look. Thanks so much

DROP TABLE #ProjectEvent

CREATE TABLE #ProjectEvent
(
[ProjectID] [int] NOT NULL ,
[EventOrder] [int] NULL ,
[CorrectiveActionTypeID] [int] NULL ,
[EstimatedCompletionDate] [datetime] NULL ,
[CompletedDate] [datetime] NULL
)
GO

INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,2,5,'11/12/2005','11/10/2005')
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,3,1,'11/17/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,5,NULL,'11/20/2005',NULL)

INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,1,1,'09/10/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,2,NULL,'09/17/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,3,NULL,'10/20/2005',NULL)

/*
The below results are what I am looking for
If the project has at least ONE CompletedDate, I want that rows ProjectID, EventOrder,
CorrectiveActionID AND the next rows EstimatedCompletionDate and CorrectiveActionID
*/
SELECT 1 AS ProjectID ,2 AS EventOrder, 5 AS LastCompleteCorrectiveActionID,
'11/10/2005' AS LastCompletedDate,
'11/12/2005' AS LastCompleteEstimatedDate,
'11/17/2005' AS FirstIncompleteEstimatedDate,
1 AS FirstIncompleteCorrectiveActionID
UNION
/* If the project has NO CompletedDates, I want the FIRST(Earliest Event Order for that Project)
ProjectID, EventOrder,
CorrectiveActionID AND EstimatedCompletionDate
*/
SELECT 2 AS ProjectID ,1 AS EventOrder, 1 AS LastCompleteCorrectiveActionID,
NULL AS LastCompletedDate,
'09/10/2005' AS LastCompleteEstimatedDate,
NULL AS FirstIncompleteEstimatedDate,
NULL AS FirstIncompleteCorrectiveActionID

SELECT * FROM #ProjectEvent

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 14:06:48

It's ugly but here's what I came up with. The first result set is yours. The second is mine.



SET NOCOUNT ON

CREATE TABLE #ProjectEvent 
([ProjectID] [int] NOT NULL ,
[EventOrder] [int] NULL ,
[CorrectiveActionTypeID] [int] NULL ,
[EstimatedCompletionDate] [datetime] NULL ,
[CompletedDate] [datetime] NULL)
GO

CREATE TABLE #Temp 
([ProjectID] [int] NOT NULL ,
[EventOrder] [int] NULL ,
[LastCompleteCorrectiveActionID] [int] NULL ,
[LastCompletedDate] [datetime] NULL ,
[LastCompleteEstimatedDate] [datetime] NULL,
[FirstIncompleteEstimatedDate] [datetime] NULL,
[FirstIncompleteCorrectiveActionID] [int] NULL,)
GO

INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,2,5,'11/12/2005','11/10/2005')
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,3,1,'11/17/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,5,NULL,'11/20/2005',NULL)

INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,1,1,'09/10/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,2,NULL,'09/17/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,3,NULL,'10/20/2005',NULL)

SELECT 1 AS ProjectID ,2 AS EventOrder, 5 AS LastCompleteCorrectiveActionID, 
'11/10/2005' AS LastCompletedDate, 
'11/12/2005' AS LastCompleteEstimatedDate, 
'11/17/2005' AS FirstIncompleteEstimatedDate, 
1 AS FirstIncompleteCorrectiveActionID
UNION
SELECT 2 AS ProjectID ,1 AS EventOrder, 1 AS LastCompleteCorrectiveActionID, 
NULL AS LastCompletedDate, 
'09/10/2005' AS LastCompleteEstimatedDate, 
NULL AS FirstIncompleteEstimatedDate, 
NULL AS FirstIncompleteCorrectiveActionID

INSERT INTO #Temp(ProjectID, EventOrder, LastCompleteCorrectiveActionID, LastCompletedDate, LastCompleteEstimatedDate)
SELECT t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, t.CompletedDate, p.EstimatedCompletionDate
FROM #ProjectEvent p
INNER JOIN 
(
	SELECT ProjectID, MAX(CompletedDate) AS CompletedDate
	FROM #ProjectEvent
	WHERE CompletedDate IS NOT NULL
	GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.CompletedDate = t.CompletedDate

INSERT INTO #Temp(ProjectID, EventOrder, LastCompleteCorrectiveActionID, LastCompleteEstimatedDate)
SELECT t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, t.EstimatedCompletionDate
FROM #ProjectEvent p
INNER JOIN
(
	SELECT ProjectID, MIN(EstimatedCompletionDate) AS EstimatedCompletionDate
	FROM #ProjectEvent
	WHERE ProjectID NOT IN (SELECT ProjectID AS CompletedDate FROM #ProjectEvent WHERE CompletedDate IS NOT NULL GROUP BY ProjectID)
	GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.EstimatedCompletionDate = t.EstimatedCompletionDate

UPDATE t
SET FirstIncompleteEstimatedDate = x.EstimatedCompletionDate,
	FirstIncompleteCorrectiveActionID = x.CorrectiveActionTypeID
FROM #Temp t
INNER JOIN
(
	SELECT t.ProjectID, CorrectiveActionTypeID, EstimatedCompletionDate
	FROM #ProjectEvent p
	INNER JOIN
	(
		SELECT ProjectID, MIN(EventOrder) AS EventOrder
		FROM #ProjectEvent
		WHERE 
			EventOrder NOT IN (SELECT EventOrder FROM #Temp WHERE LastCompletedDate IS NOT NULL) AND
			ProjectID IN (SELECT ProjectID FROM #Temp WHERE LastCompletedDate IS NOT NULL)
		GROUP BY ProjectID
	) t
	ON p.ProjectID = t.ProjectID AND p.EventOrder = t.EventOrder
) x
ON t.ProjectID = x.ProjectID

SELECT * FROM #Temp

DROP TABLE #ProjectEvent, #Temp

Hopefully this is for a report or won't have to be run often as I can't imagine it will perform very well. Perhaps you can get nosepicker's solution to work for you as his does it all in one query whereas mine requires working with a temp table.

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 14:56:53

Two problems...obviously, resource intensive...probably won't matter. It is for high level execs to see some total values. Probably be run 5 ro 10 times a day.

But, this query returns the rows for event Event that has a completedDate...I only want the last one completed. So, we are close, but not there yet...

I don't even know where to start debugging this, but I will try anyway! Thanks, and anymore thoughts are appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 14:59:20

My result set matches your expected result set. So if there is something wrong with the queries I provided, we need the sample data to reflect your problem.

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 15:19:51

Well, the problem is this..Notice I now have two events in the same project with the same completion date. That is what causes the problem. I only need the one with the latest EventOrder...Thanks so much helping me...I really appreciate it

SET NOCOUNT ON

CREATE TABLE #ProjectEvent
([ProjectID] [int] NOT NULL ,
[EventOrder] [int] NULL ,
[CorrectiveActionTypeID] [int] NULL ,
[EstimatedCompletionDate] [datetime] NULL ,
[CompletedDate] [datetime] NULL)
GO

CREATE TABLE #Temp
([ProjectID] [int] NOT NULL ,
[EventOrder] [int] NULL ,
[LastCompleteCorrectiveActionID] [int] NULL ,
[LastCompletedDate] [datetime] NULL ,
[LastCompleteEstimatedDate] [datetime] NULL,
[FirstIncompleteEstimatedDate] [datetime] NULL,
[FirstIncompleteCorrectiveActionID] [int] NULL,)
GO

INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,2,5,'11/12/2005','11/10/2005')
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,3,1,'11/17/2005','11/10/2005')
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(1,5,NULL,'11/20/2005',NULL)

INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,1,1,'09/10/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,2,NULL,'09/17/2005',NULL)
INSERT INTO #ProjectEvent(ProjectID, EventOrder, CorrectiveActionTypeID, EstimatedCompletionDate, CompletedDate)
VALUES(2,3,NULL,'10/20/2005',NULL)

SELECT 1 AS ProjectID ,2 AS EventOrder, 5 AS LastCompleteCorrectiveActionID,
'11/10/2005' AS LastCompletedDate,
'11/12/2005' AS LastCompleteEstimatedDate,
'11/17/2005' AS FirstIncompleteEstimatedDate,
1 AS FirstIncompleteCorrectiveActionID
UNION
SELECT 2 AS ProjectID ,1 AS EventOrder, 1 AS LastCompleteCorrectiveActionID,
NULL AS LastCompletedDate,
'09/10/2005' AS LastCompleteEstimatedDate,
NULL AS FirstIncompleteEstimatedDate,
NULL AS FirstIncompleteCorrectiveActionID

INSERT INTO #Temp(ProjectID, EventOrder, LastCompleteCorrectiveActionID, LastCompletedDate, LastCompleteEstimatedDate)
SELECT t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, t.CompletedDate, p.EstimatedCompletionDate
FROM #ProjectEvent p
INNER JOIN
(
SELECT ProjectID, MAX(CompletedDate) AS CompletedDate
FROM #ProjectEvent
WHERE CompletedDate IS NOT NULL
GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.CompletedDate = t.CompletedDate

INSERT INTO #Temp(ProjectID, EventOrder, LastCompleteCorrectiveActionID, LastCompleteEstimatedDate)
SELECT t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, t.EstimatedCompletionDate
FROM #ProjectEvent p
INNER JOIN
(
SELECT ProjectID, MIN(EstimatedCompletionDate) AS EstimatedCompletionDate
FROM #ProjectEvent
WHERE ProjectID NOT IN (SELECT ProjectID AS CompletedDate FROM #ProjectEvent WHERE CompletedDate IS NOT NULL GROUP BY ProjectID)
GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.EstimatedCompletionDate = t.EstimatedCompletionDate

UPDATE t
SET FirstIncompleteEstimatedDate = x.EstimatedCompletionDate,
FirstIncompleteCorrectiveActionID = x.CorrectiveActionTypeID
FROM #Temp t
INNER JOIN
(
SELECT t.ProjectID, CorrectiveActionTypeID, EstimatedCompletionDate
FROM #ProjectEvent p
INNER JOIN
(
SELECT ProjectID, MIN(EventOrder) AS EventOrder
FROM #ProjectEvent
WHERE
EventOrder NOT IN (SELECT EventOrder FROM #Temp WHERE LastCompletedDate IS NOT NULL) AND
ProjectID IN (SELECT ProjectID FROM #Temp WHERE LastCompletedDate IS NOT NULL)
GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.EventOrder = t.EventOrder
) x
ON t.ProjectID = x.ProjectID

SELECT * FROM #Temp

DROP TABLE #ProjectEvent, #Temp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 15:22:58

The first INSERT statement needs TOP 1:



INSERT INTO #Temp(ProjectID, EventOrder, LastCompleteCorrectiveActionID, LastCompletedDate, LastCompleteEstimatedDate)
SELECT TOP 1 t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, t.CompletedDate, p.EstimatedCompletionDate
FROM #ProjectEvent p
INNER JOIN 
(
	SELECT ProjectID, MAX(CompletedDate) AS CompletedDate
	FROM #ProjectEvent
	WHERE CompletedDate IS NOT NULL
	GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.CompletedDate = t.CompletedDate

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 15:30:31

No, that pulls the first row (EventOrder 2). I need the LATEST EventOrder(3) for that project(1)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 15:35:22

You should have changed your expected result set then to match what you wanted as that's what I'm coding by. Here's the new version:



INSERT INTO #Temp(ProjectID, EventOrder, LastCompleteCorrectiveActionID, LastCompletedDate, LastCompleteEstimatedDate)
SELECT t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, t.CompletedDate, p.EstimatedCompletionDate
FROM #ProjectEvent p
INNER JOIN 
(
	SELECT ProjectID, MAX(CompletedDate) AS CompletedDate, MAX(EventOrder) AS EventOrder
	FROM #ProjectEvent
	WHERE CompletedDate IS NOT NULL
	GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.CompletedDate = t.CompletedDate AND p.EventOrder = t.EventOrder

Tara

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 15:59:40

Thanks Tara...that works, a question though...WHy not slightly simplified... UNION for the INSERT...And what was your UPDATE doing? My results are the same without it? Thanks for taking the time to help out...Thanks

SET NOCOUNT ON

CREATE TABLE #Temp
([ProjectID] [int] NOT NULL ,
[EventOrder] [int] NULL ,
[LastCompleteCorrectiveActionID] [int] NULL ,
[LastCompletedDate] [datetime] NULL ,
[LastCompleteEstimatedDate] [datetime] NULL,
[FirstIncompleteEstimatedDate] [datetime] NULL,
[FirstIncompleteCorrectiveActionID] [int] NULL,)
GO

INSERT INTO #Temp(ProjectID, EventOrder, LastCompleteCorrectiveActionID, LastCompletedDate, LastCompleteEstimatedDate)
SELECT t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, t.CompletedDate, p.EstimatedCompletionDate
FROM ProjectEvent p
INNER JOIN
(
SELECT ProjectID, MAX(CompletedDate) AS CompletedDate, MAX(EventOrder) AS EventOrder
FROM ProjectEvent
WHERE CompletedDate IS NOT NULL
GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.CompletedDate = t.CompletedDate AND p.EventOrder = t.EventOrder
UNION
SELECT t.ProjectID, p.EventOrder, p.CorrectiveActionTypeID, NULL, t.EstimatedCompletionDate
FROM ProjectEvent p
INNER JOIN
(
SELECT ProjectID, MIN(EstimatedCompletionDate) AS EstimatedCompletionDate
FROM ProjectEvent
WHERE ProjectID NOT IN (SELECT ProjectID AS CompletedDate FROM ProjectEvent WHERE CompletedDate IS NOT NULL GROUP BY ProjectID)
GROUP BY ProjectID
) t
ON p.ProjectID = t.ProjectID AND p.EstimatedCompletionDate = t.EstimatedCompletionDate

SELECT * FROM #Temp
DROP TABLE #Temp

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-20 : 16:01:30

Harry, did you even bother to run my SQL statement? Based on what you wrote, I believe it should give you what you want.

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 16:04:45

quote:
Originally posted by nosepicker

Harry, did you even bother to run my SQL statement? Based on what you wrote, I believe it should give you what you want.

In absolutely did run it, but could not get it to run...I apologixe for not responding...I was just getting alot of column name errors. I am sure it was my fault. But, sincerely, thank you.

If I could get your to run, I would likely use it, as it is one statement, and probably less resource intensive...Thoughts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 16:04:49

The UPDATE statement is due to the fact that we don't yet have information for FirstIncompleteEstimatedDate or FirstIncompleteCorrectiveActionID when we do the first INSERT. So you've got NULLs there until we UPDATE the temp table.

If you want to UNION, then that's fine. I don't have it in there as I was building up the solution little by little and didn't go back to combine them.

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-20 : 16:19:38

[code]
SELECT Mx.MaxProjectID, Mx.MaxEventOrder, Mx.MaxEstDate, Mx.MaxCompleteDate, Mx.MaxActionID, Mn.MinEstDate, Mn.MinActionID
FROM (SELECT Mx1.ProjectID AS MaxProjectID, Mx1.EventOrder AS MaxEventOrder, Mx1.EstimatedCompletionDate AS MaxEstDate, Mx1.CorrectiveActionTypeID AS MaxActionID, Mx1.CompletedDate AS MaxCompleteDate
FROM #ProjectEvent Mx1
JOIN (SELECT ProjectID, MAX(EventOrder) AS EventOrder FROM #ProjectEvent
WHERE CompletedDate IS NOT NULL
GROUP BY ProjectID) AS Mx2
ON Mx1.ProjectID = Mx2.ProjectID AND Mx1.EventOrder = Mx2.EventOrder) AS Mx
JOIN (SELECT Mn1.ProjectID AS MinProjectID, Mn1.CorrectiveActionTypeID AS MinActionID, Mn1.EstimatedCompletionDate AS MinEstDate
FROM #ProjectEvent Mn1
JOIN (SELECT ProjectID, MIN(EventOrder) AS EventOrder FROM #ProjectEvent
WHERE CompletedDate IS NULL
GROUP BY ProjectID) AS Mn2
ON Mn1.ProjectID = Mn2.ProjectID AND Mn1.EventOrder = Mn2.EventOrder) AS Mn
ON Mx.MaxProjectID = Mn.MinProjectID
[/code]

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-20 : 16:47:08

nosepicker, works like a charm. Many thanks to both you and Tara. I appreciate it...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources