Geek asks and answers

SQL query with JOIN and several conditions

I'm having problems with the wording of the 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 to be carried out with the following condition: the task was never planned or the task was planned in the past, but has no plans for the future.

In the above example, the following tasks meet this condition:

  • 1 foo
  • 4 bassbar

Any suggestions? Thanks in advance!

+5
source share
2 answers
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())

SELECT *, , . , , . #, .

, ??? , ?

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

. , , . , , MAX ( #) GROUP BY task, , , ...

  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

task.id .

:

sql, :

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
+1

:

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

More articles:


All Articles