0

I have this query that returns the tasks that were not performed:

SELECT COUNT(raddb.TipoTarefa.Id) AS Total, Descricao, IniciarTarefa

FROM raddb.TipoTarefa LEFT OUTER JOIN raddb.Tarefa ON raddb.Tarefa.tarefa = raddb.TipoTarefa.Id

WHERE IniciarTarefa IS NULL 

GROUP BY Descricao, IniciarTarefa

But I want it to check during the month on which days the tasks were not performed (since they are daily tasks), because in this way, it is enough that the task is performed on one of the days of the month and is no longer returned.

Update

I have the table where I have the types of tasks:

enter image description here

Now the table where I register the tasks:

enter image description here

For example, only for 10 days. Task 3 was performed on day 20, 21, 22, 23, 25 and 27, but was not performed on day 24, 26, 28, 29 and 30.

In the query, it was intended to return that task 3 was not performed on day 24, 26, 28, 29 and 30, a line for each day, where it returned the task name, and the date of the days when it was not performed

Bruno
  • 801
  • 5
  • 11
  • update your question add a proper data sample and th expected result – ScaisEdge Jun 27 '19 at 16:53
  • @scaisEdge I've already put an example of how you wanted it to return the data. – Bruno Jun 27 '19 at 17:03
  • yes we have a (very rough) sample of the expected result, but not the data from which that result should be calculated. If you don't supply the input, how can we understand the way to reach the output? – ADyson Jun 27 '19 at 17:06
  • I'm not in your data and your sample is not clear to me... try add a proper data samle as tabular data for all the table involved and the expected result .. – ScaisEdge Jun 27 '19 at 17:06
  • @scaisEdge I updated the question with data. Did you understand what I mean? – Bruno Jun 27 '19 at 17:25

1 Answers1

1

I have encountered this need before, and what I do is generate a list of dates between two dates and LEFT JOIN that against the data in question. Thus, it has rows for every date, with NULL in the actual data for those dates on which the event in question did not happen.

There are some existing Stack Overflow answers that show a few different approaches to this. I have always favored doing this with a list of dates generated in-query, but you could also generate a permanent table of dates and refer to that.

See:

Generating a series of dates

generate days from date range

HTH

landru27
  • 1,654
  • 12
  • 20