Need help with a SQL query

[rjoubert]

I'm trying to find the best way to write this query, which involves a Parent-Child relationship. Any suggestions would be greatly appreciated.

I want to return the Parent records that have Child records that have not been completed in over 120 days.

Details...
- Parent.ID = Child.ParentID
- Need to query for the last completed Child record (Child.Status = 'Completed') belonging to each Parent record and use the Child.StatusDate to see if it's over 120 days old.

I know this is about as clear as mud, so please let me know if I can provide more details.

TIA...

 

[SgtJarrow]

You're right....not much info to work with. Some sample data and expected results would help a lot...

SELECT
    b.Id
FROM table a
INNER JOIN table b
    ON a.id = b.ParentId
WHERE a.Status = 'Completed'
    AND a.StatusDate < DATEADD(DAY, -120, GETDATE())

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[markros]

Try

;with cte as (SELECT
    a.Id, b.StatusDate, row_number() over (partition by a.ID order by b.StatusDate DESC) as row
FROM Parent a
INNER JOIN Child b
    ON a.id = b.ParentId
WHERE b.Status = 'Completed)

select * from cte where Row = 1 and StatusDate < dateadd(day,
-120 + datediff(day, '19000101',getdate()),'19000101')

PluralSight Learning Library

 

[rjoubert]

ParentID
1
2
3

ChildID, ParentID, Status, StatusDate
1, 1, "Completed", 1/1/10
2, 1, "Completed", 1/2/10
3, 1, "Completed", 1/3/10

4, 2, "Completed", 7/7/10
5, 2, "Completed", 7/5/10

6, 3, "Completed", 2/2/10
7, 3, "Completed", 3/1/10
8, 3, "Completed", 2/22/10

For ParentID 1, the last completed ChildID is 3 with a completed date of 1/3/10. That is more than 120 days old, so ParentID 1 would need to be returned.

For ParentID 2, the last completed ChildID is 4 with a completed date of 7/7/10. That is less than 120 days old, so ParentID 2 would NOT be returned.

For ParentID 3, the last completed ChildID is 7 with a completed date of 3/1/10. That is more than 120 days old, so Parent ID 3 would need to be returned.

Hope that clears it up a bit...

 

[rjoubert]

Ok, I think I have figured this out. It appears I have tried to make this more complicated than it needs to be. Since I only have to return the ID of the Parent, I can just use an aggregate query on the Child table without linking in the Parent table.

Here is the query I am using...

SELECT ParentID, 
       MAX(StatusDate) AS LastCompletedDate
FROM Child
WHERE [Status] = 'Completed'
AND DATEDIFF(Day, StatusDate, GETDATE()) >= 120
GROUP BY ParentID

Thanks for your help though.

 

[Emtucifor]

Don't use datediff on the column, please. Do it like this:

SELECT
   ParentID,
   LastCompletedDate = Max(StatusDate)
FROM Child
WHERE [Status] = 'Completed'
AND StatusDate <= DateAdd(Day, -120, GetDate())
GROUP BY ParentID

Of further note is that the child table should have statuses in a lookup table so the column can be StatusID and just 1, 2, or 4 bytes instead of the at least 9 required to store the character string 'Completed'.