If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Grouping Query
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 |
#2
|
|||
|
|||
-----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/AwUBQfbS2oechKqOuFEgEQL8dACfcdeHBNEpcysFGYwJGwNEk8 A+aDwAn24u MvHIerCe54gWXY1RN69H7i7E =Zyum -----END PGP SIGNATURE----- Kenny Holden wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query week to date, month to date, year to date hours | Ty | Running & Setting Up Queries | 1 | December 15th, 2004 03:46 AM |
incorrect sums in report using 2 tables | jkendrick75 | Setting Up & Running Reports | 22 | December 13th, 2004 02:19 PM |
query based on a date field | Richard | Running & Setting Up Queries | 2 | December 3rd, 2004 02:29 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |