Date Grouping Query

[Kenny Holden]

Hi List,

I have a table of tasks;

tasks
====
task_number
task_opened
task_closed


Now im trying to figure out how to group by day. Basically, given a month
and year eg september 2004. I want to for each day in that month show the
day, the count of opened jobs on that day, and the cound of closed jobs on
that day.

Im really strugling with this query.

Obviously I can do both the queries separately. But I really want a graph
showing for each day of the month, the number opened and the number closed.
On my current 2 queries the ouput generated doesnt list days where there are
0 jobs logged, so it sometimes shows 15th, 18, 20 etc...

Does anyone know of an easy way to cycle through all possible days of the
given month and do an iif count on the date comparing to the task_opened and
task_closed and increasing the number counted by this.

Any ideas?

Many Thanks, Kenny

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to load a temp table of dates for the month(s) you want to
query on. Then use that table in the query to get the info you want:

Temp table:

CREATE TABLE date_temp (task_date Date)

Use VBA to load the table w/ dates.

The query to get what you want (untested):

PARAMETERS start_date Date, end_date Date;

SELECT T.task_number, D.task_date,

Count(IIf(Not IsNull(T.task_opened),1)) As Opened,
Count(IIf(Not IsNull(T.task_closed),1)) As Closed

FROM tasks As T, date_temp As D

WHERE T.task_opened BETWEEN start_date And end_date
OR T.task_closed BETWEEN start_date And end_date

GROUP BY T.task_number, D.task_date

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfbS2oechKqOuFEgEQL8dACfcdeHBNEpcysFGYwJGwNEk8A+aDwAn24u
MvHIerCe54gWXY1RN69H7i7E
=Zyum
-----END PGP SIGNATURE-----

 

[Kenny Holden]

Many Thanks,

I guessed it would be this earlier in the morning, just havent got round to
implementing it yet.

Thanks again, Kenny