SQL query with JOIN and multiple conditions

Question

I'm having trouble formulating a SQL query. I run the following:

SELECT *
FROM tasks
LEFT JOIN plans
ON plans.task_id = tasks.id

and get this result set:

task.id task.description  plan.id plan.task_id plan.date
-------|-----------------|-------|------------|------------
   1    Foo                  1         1        1998-01-01
   2    Foobar               2         2        2012-02-25
   2    Foobar               3         2        2012-12-12
   3    Foobass              4         3        2012-12-24
   4    Bassbar
                       ... and lots of more records

Today is 2012-08-03. I want all tasks with the following condition: the task have never been planed or the task have been planed in the past but have no future plans.

In the example above the following tasks meet this condition:

• 1 Foo
• 4 Bassbar

Any suggestions? Thanks in advance!

Answer 1

SELECT *
FROM tasks
LEFT JOIN plans
ON plans.task_id = tasks.id
WHERE plan.week IS NULL OR (plan.week < DATEPART(week, GetDate()) AND plan.Year = YEAR(GETDATE())

You should not use SELECT *, explicitly write out each column you need. You didn't mention if you had a year column as well, you'll have to handle the week number and the year. If you just handle the week # you'll get results across multiple years.

It sounds like you can just use task description??? Why are you basing it on the week column if you can use the task description?

SELECT *
FROM tasks
LEFT JOIN plans
ON plans.task_id = tasks.id
WHERE tasks.description = 'Past' OR tasks.description = 'Not planned'

Aha I understand your data now, you could have multiple tasks but on different week numbers. This is simple, just use a query to find the MAX(Week#) GROUP BY the task and then perform the query, Ill write it up give me a minute...

  CREATE TABLE #MyTest
(
 TaskID int,
 TaskYear int,
 TaskWeek int
)

INSERT INTO #MyTest(TaskID,TaskYear,TaskWeek)
VALUES (1, 2012, 4)
INSERT INTO #MyTest(TaskID,TaskYear,TaskWeek)
VALUES (2, 2012, 5)
INSERT INTO #MyTest(TaskID,TaskYear,TaskWeek)
VALUES (2, 2012, 36)
INSERT INTO #MyTest(TaskID,TaskYear,TaskWeek)
VALUES (3, 2012, 36)
INSERT INTO #MyTest(TaskID,TaskYear,TaskWeek)
VALUES (4, 2012, NULL)
INSERT INTO #MyTest(TaskID,TaskYear,TaskWeek)
VALUES (6, 2011, 5)
INSERT INTO #MyTest(TaskID,TaskYear,TaskWeek)
VALUES (6, 2010, 36)

SELECT
    TaskID,
    MAX(TaskWeek) AS WeekNumber,
    TaskYear
FROM
  #MyTest
GROUP BY 
    TaskID,
    TaskWeek,
    TaskYear
HAVING MAX(TaskWeek) < DatePart(week, GetDate()) OR MIN(TaskWeek) IS NULL

DROP TABLE #MyTest

This will now give you unique rows for each task.id with the max week number.

Final Result

Based on your final comments:

Copy and paste this into sql management studio, I have commented it for you:

CREATE TABLE #MyTest
(
 TaskID int,
 TaskDate datetime
)

--test for only in the past NOTHING in the future
INSERT INTO #MyTest(TaskID, TaskDate)
VALUES (1, '1998-01-01')

--test for planned in the future NOTHING in the past
INSERT INTO #MyTest(TaskID,TaskDate)
VALUES (3, '2012-12-24')

--test for no plan as all (IS NULL)
INSERT INTO #MyTest(TaskID,TaskDate)
VALUES (4, null)

--test for planned in the past but has an upcoming event in the future
INSERT INTO #MyTest(TaskID,TaskDate)
VALUES (6, '2011-12-23')
INSERT INTO #MyTest(TaskID,TaskDate)
VALUES (6, '2012-12-23')
INSERT INTO #MyTest(TaskID,TaskDate)

--test for planned in the past, NO upcoming event in the future
VALUES (8, '2012-1-23')
INSERT INTO #MyTest(TaskID,TaskDate)
VALUES (8, '2012-6-23')

--result should show:
-- task id = 1 (because of: performed in past but nothing in the future)
-- task id = 4 (because of: no plan at all) 
-- task id = 8 (because of: only past)

SELECT
    TaskID,
    YEAR(TaskDate) AS TheYear,
    DatePart(week, TaskDate) AS WeekNumber
FROM
  #MyTest
WHERE 
--handle no planning of a task...
((TaskDate IS NULL)
--eliminate any task id that is out in the future
OR TaskID NOT IN (SELECT TaskID FROM #MyTest WHERE TaskDate > GetDate()))
GROUP BY
    TaskID,
    Year(TaskDate),
    DatePart(week, TaskDate)

DROP TABLE #MyTest