How do you count dates?

[Guest]

Here's the sample content of my table:

Worker Date Task
=============
Worker1 Oct 1 Task1
Worker1 Oct 1 Task2
Worker1 Oct 2 Task1
Worker1 Oct 2 Task2
Worker2 Oct 1 Task1
Worker2 Oct 3 Task1
Worker2 Oct 3 Task2

I'm trying to count the number of days a worker did a task. If I query
between Oct 1 to Oct 3, Worker1 did 4 tasks in 2 days and Worker2 did 2 tasks
also for 2 days. If the query is between Oct 2 to 3, Worker1 did 2 tasks in
1 day and Worker2 also did 2 tasks in 1 day. Can this be done in a single
query? If so, please show me how to do it. Thanks in advance.

 

[Guest]

Try this

SELECT TableName.Worker , TableName.Task, Count(TableName.Task) AS CountOfDays
FROM TableName
GROUP BY TableName.Worker , TableName.Task

 

[Guest]

I need to count the dates not the tasks.

Try this

SELECT TableName.Worker , TableName.Task, Count(TableName.Task) AS CountOfDays
FROM TableName
GROUP BY TableName.Worker , TableName.Task
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Steve Schapel]

GD,

Unless I misunderstand you, the example of Worker2 for period Oct 1 to
Oct 3 would be 3 tasks in 2 days. No?

It is possible to do this in one query with the use of sub-queries.
However, the easiest approach is to do it in steps. Make one query to
return the number of days worked for each worker. Make another query to
return the number of tasks per worker. And then make a third query that
joins these other two queries, for your final result.

 

[Guest]

I know, but because there is an entry for each date you don't have to include
the date, you can if you want to.
The query count the entries for each worker and task, so if he worked for
two days, it will still count 2 becuse there are two entries
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Guest]

Ooops! you are right ... 3 tasks in 2 days. Actually, I was to do this with
2 queries:

1st:
SELECT table.Worker, table.Date, 1 AS WorkDays
FROM table
GROUP BY table.EMPID, table.DATE;

2nd:
SELECT WorkDays.DATE, Sum(WorkDays.WorkDays) AS SumOfWorkDays
FROM WorkDays
GROUP BY WorkDays.DATE;

Can this be done in a single query?