How to structure SQL Statment

Question

Hello, I have a table that contains a list of tasks;

TableName: Tasks. Fields: (ID Int, Description nvarchar)

The tasks are completed daily and are logged in a table like follows;

TableName TasksDone. Fields: (TaskID Int, TaskDate DateTime)

I need to have a query that runs for a date range and shows the tasks that were NOT done (do not exist in the TasksDone table) for every date in the range.

I hope that makes sense... Thanks for any help.

Answer 1

This is fairly straight forward, if I'm understanding the problem correctly:

SELECT *
FROM Tasks
WHERE ID NOT IN (SELECT TaskID FROM TasksDone WHERE TaskDate BETWEEN x AND y)

Replace x and y with the date you're after.

Answer 2

I've not tested this out but see if this helps:

select ID, TaskDate as A from Tasks,TasksDone
where TaskID not in (select TaskID from TasksDone where TaskDate = A)
GROUP BY TaskDate

Answer 3

You will need a numbers or calendar table to make things easy, or we can simulate one if the range is small. Is the TaskDate a plain date, or does it have a time component also?

Basic plan of attack is:

declare @StartDate datetime
declare @EndDate datetime

/* Set @StartDate and @EndDate to represent the range */

with Digits as (
    select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all
    select 5 union all select 6 union all select 7 union all select 8 union all select 9
), Numbers as (
    select (D1.d * 100) + (D2.d * 10) + D3.d as n
    from Digits D1,Digits D2,Digits D3
), TaskDates as (
    select
        t.TaskID,
        DATEADD(day,n.n,@StartDate) as TaskDate
    from
        Tasks t
            inner join
        Numbers n
            on
                DATEADD(day,n.n,@StartDate) <= @EndDate
)
select
    *
from
    TaskDates td1
        left join
    TasksDone td2
        on
            td1.TaskID = td2.TaskID and
            DATEDIFF(day,td1.TaskDate,td2.TaskDate) = 0
where
    td2.TaskID is null

The first two CTEs build a small numbers table, the 3rd CTE constructs a set of TaskIDs and Dates within the required range. The final select matches theses against the TasksDone table, and then discards those rows where a match is found. If TasksDone.TaskDate is a plain date (no time component) and @StartDate is also with no time component, then you can ditch the DATEDIFF and just use td1.TaskDate = td2.TaskDate.

If you need a large range (above can cover ~3 years), I'd suggest building a proper number table or calendar table

Answer 4

If I understand correct, following statement should get you the tasks that didn't get executed every day in the entire range.

SQL Statement

SELECT  t.*
FROM    @Tasks t
        INNER JOIN (
          SELECT  TaskID
          FROM    @TasksDone td
          WHERE   td.TaskDate BETWEEN @RangeStart AND @RangeEnd        
          GROUP BY
                  td.TaskID
          HAVING  COUNT(TaskID) < CAST(@RangeEnd - @RangeStart AS INTEGER)+1
          UNION ALL
          SELECT  TaskID
          FROM    @TasksDone td
          WHERE   TaskID NOT IN (SELECT TaskID 
                                 FROM   @TasksDone 
                                 WHERE  TaskDate BETWEEN @RangeStart AND @RangeEnd)
        ) td ON td.TaskID = t.ID       

---

Test script

DECLARE @Tasks TABLE (
  ID INTEGER
  , DESCRIPTION NVARCHAR(32)
)

DECLARE @TasksDone TABLE (
  TaskID INTEGER
  , TaskDate DATETIME
)

DECLARE @RangeStart DATETIME
DECLARE @RangeEnd DATETIME

SET @RangeStart = GetDate() - 1
SET @RangeEnd = GetDate() + 1

INSERT INTO @Tasks
          SELECT 1, 'Done Every Day in range.'
UNION ALL SELECT 2, 'Done a few times in range.'
UNION ALL SELECT 3 , 'Not done anytime in range.'

INSERT INTO @TasksDone
          SELECT 1, @RangeStart
UNION ALL SELECT 1, GetDate()
UNION ALL SELECT 1, @RangeEnd
UNION ALL SELECT 2, GetDate()
UNION ALL SELECT 3, GetDate() + 2

SELECT  t.*
FROM    @Tasks t
        INNER JOIN (
          SELECT  TaskID
          FROM    @TasksDone td
          WHERE   td.TaskDate BETWEEN @RangeStart AND @RangeEnd        
          GROUP BY
                  td.TaskID
          HAVING  COUNT(TaskID) < CAST(@RangeEnd - @RangeStart AS INTEGER)+1
          UNION ALL
          SELECT  TaskID
          FROM    @TasksDone td
          WHERE   TaskID NOT IN (SELECT TaskID FROM @TasksDone WHERE TaskDate BETWEEN @RangeStart AND @RangeEnd)
        ) td ON td.TaskID = t.ID