CODEWITHSUNDEEP
sqlsql-servert-sql
Bohn
Bohn

Reputation: 26919

Filtering out parent records based on conditions on child records

I have two table with a psudo-schema like this:

CREATE TABLE Task(TaskPK INT)

CREATE TABLE TaskDetails(TaskDetailsPK INT, TaskPK INT, CompleteDate DATETIME NULL, TaskDetailName CHAR(30) , Status CHAR(10))

Notice that TaskDetails table has a CompleteDate column that can be NULL too and also its Status column has a few hard-coded values such as "Completed", "Cancelled", "In Process", "Waiting" ....

I want to write a query to find TaskPK ( parent table ) in two cases: 1- Parent has at least one child record that is not in the Completed Status 2- Parent has at least one child record that is in Completed Status AND its completion date has not been older than 30 days from today.

I tried to write it with GroupBy but it is not exactly correct:

select td.TaskPK from TaskDetail td
where ( td.CurrentStatus = 'Complete')
Group By td.TaskPK
Having Min(CompleteDate ) >= DATEADD(day, -30, GETDATE())

Upvotes: 2

Views: 1612

Answers (3)

Sandya
Sandya

Reputation: 21

We can achieve the required results using INNER JOIN too... please check below code...

  SELECT T.TaskPK
  FROM TASK T
  INNER JOIN TaskDetails TD
  ON T.TaskPK = TD.TaskPK
  WHERE (TD.[Status]!= 'Completed') OR (TD.[Status] = 'Completed' AND TD.CompleteDate > 
  DATEADD(DD,-30,GETDATE()))

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

I'd use EXISTS for that.

SELECT t.taskpk
       FROM task t
       WHERE EXISTS (SELECT *
                            FROM taskdetails td
                            WHERE td.taskpk = t.taskpk
                                  AND td.status <> 'Completed')
              OR EXISTS (SELECT *
                                FROM taskdetails td
                                WHERE td.taskpk = t.taskpk
                                      AND td.status = 'Completed'
                                      AND td.completedate >= dateadd(day, -30, convert(date, getdate())));

I'm not sure if the two conditions must both be true or only at least one of them. In the former case, you'd have to change the OR between the two EXISTS to AND.

I also down casted getdate() to a date, so that it loses its time component. Unless you really mean 30 day = 30 * 24 * 60 * 60 * 1000 ms the results may otherwise be surprising.

Upvotes: 1

Marcelo Myara
Marcelo Myara

Reputation: 3011

Well, using your description directly:

SELECT 
    TaskPK 
  FROM 
    Task T
  WHERE
    EXISTS (SELECT 1 FROM TaskDetails TD WHERE TD.TaskPK = T.TaskPK AND Status <> 'Completed')
    OR
    EXISTS (SELECT 1 FROM TaskDetails TD WHERE TD.TaskPK = T.TaskPK AND Status = 'Completed' AND CompleteDate >= DATEADD(DAY, -30, GetDate()));

Upvotes: 5

Related Questions