September 25, 2006 at 1:09 pm
I have the following problem - There is a table with RecordID, ProjectID, Start, End and Status. RecordID is the primary key and the ProjectID is the foreign key to another table. The typical data is as follows:
RecordID | ProjectID | Start | End
1 1000 2/1/2003 2/2/2003
2 1000 2/12/2003 2/13/2003
3 2000 6/1/2004 6/3/2004
4 2000 8/1/2004 8/2/2004
5 2000 7/10/2006 7/11/2006
I would like to write a query to get a list of the highest RecordIDs for each unique ProjectID with the start and end dates 6 months prior to today's date. In the above example, it would be 2.
September 25, 2006 at 1:26 pm
In your example, did you miss something? First you mention there is a field status but there is no status in your example.
You want to write a query of the highest recordID for each unique ProjectID, is it there should be one answer for project ID 1000 and one answer for project ID 2000.
CREATE TABLE Temp (RecordID INT,
ProjectID INT,
StartDate DATETIME,
EndDate DateTime)
INSERT INTO #Temp(RecordID ,
ProjectID ,
StartDate ,
EndDate)
SELECT 1, 1000, '02/01/2003', '02/02/2003'
UNION
SELECT 2, 1000, '02/12/2003', '02/13/2003'
UNION
SELECT 3, 2000 ,'06/01/2004', '06/03/2004'
UNION
SELECT 4, 2000, '08/01/2004', '08/02/2004'
UNION
SELECT 5, 2000, '07/10/2006', '07/11/2006'
SELECT MAX(RecordID), t.ProjectID
FROM Temp t
INNER JOIN (SELECT Distinct ProjectID
FROM Temp
WHERE StartDate < DATEADD(MM, -6, GETDATE()) AND
EndDate < DATEADD(MM, -6, GETDATE())) d
ON t.ProjectID = d.ProjectID
GROUP BY t.ProjectID
Answer:
RecordID ProjectID
2 1000
5 2000
September 25, 2006 at 1:53 pm
Hi,
Thanks for responding. I left the status out of the data since it does not play any role. Now if your query is correct, there should be only one row in the output:
RecordID ProjectID
2 1000
Since the second row (5 2000) has start and end dates inside of 6 months from today.
September 25, 2006 at 2:03 pm
I made a mistake about the 5. But for projectID 2000, you should get 4 as the recordID since the startdate and enddate is 6 month from today.
September 25, 2006 at 2:04 pm
I changed the query.
SELECT MAX(t.RecordID), t.ProjectID
FROM #Temp t
INNER JOIN (SELECT Distinct RecordID, ProjectID
FROM #Temp
WHERE StartDate < DATEADD(MM, -6, GETDATE()) AND
EndDate < DATEADD(MM, -6, GETDATE())) d
ON t.ProjectID = d.ProjectID AND
t.recordID = d.recordID
GROUP BY t.ProjectID
September 25, 2006 at 2:09 pm
Actually I made it too complicated.
SELECT MAX(RecordID), ProjectID
FROM Temp
WHERE StartDate < DATEADD(MM, -6, GETDATE()) AND
EndDate < DATEADD(MM, -6, GETDATE())
GROUP BY ProjectID
September 25, 2006 at 2:46 pm
Excellent! Thanks much!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply