Count instances within a date range

[pcharron]

I am trying to count the number of record instances that fall within a specified date range for each client receiving orders during the period. I can not seem to get the syntax correct and I believe I am going about it out of sequence. This is a sample layout of the data.Customer_Name1 / Item_desc / date_of_serviceCustomer_Name1 / Item_desc / date_of_serviceCustomer_Name1 / Item_desc / date_of_serviceCustomer_Name2 / Item_desc / date_of_serviceCustomer_Name2 / Item_desc / date_of_serviceCustomer_Name2 / Item_desc / date_of_serviceCustomer_Name3 / Item_desc / date_of_serviceCustomer_Name3 / Item_desc / date_of_serviceCustomer_Name4 / Item_desc / date_of_serviceWhat I would like to acomplish is the following:1. Specify a date range for each week in a given month, such as week1 = 10/01/2010-10/7/2010 to act as boundry for the variable week1, week2, etc. (the exact dates for these ranges would, of course, vary from month to month.)2. For every unique customer_name count the number of record instances falling into the above specified ranges and return a value. So assuming for customer_name1 above that all date_of_service values fell into the range for week1 the return would be "3". Then test for week2, then week 3, etc., each time returning (printing) the resultant value.[i have tried using a combination of SELECT COUNT and BETWEEN (to specify the date range, but I believe it needs to be asigned as a viariable from the user input, or direct query line change and I can't seem to get that to function.)] 3. When all "week" ranges are processed get the next unique customer_name (in the above case, customer_name2) and repeat the counting until there are no further unique customer_name records to process.The intended output of the query, ideally, would look something like this:Customer_name1 / week1 / n (some number)Customer_name1 / week2 / nCustomer_name2 / week1 / n**and so on**I admit my SQL is a bit thin and I would greatly appreciate any assistance the community might render me on this project.

[Fmdpa]

In my database, I store the date values as yyyy/mm/dd. That way, I can select the weeks individually by doing this:

SELECT COUNT(DISTINCT LEFT(date, 0, 6)) AS week FROM table

[pcharron]

What you suggest will allow for the selection of distinct records that fall within a date range, however I don't see that this will do it for each unique customer name.

[birbal]

add a group by at the end

SELECT COUNT(DISTINCT LEFT(date, 0, 6)) AS week FROM table  GROUP BY customer_name

[pcharron]

When I attempt to use the suggested syntax:SELECT COUNT(DISTINCT LEFT(date, 0, 6)) AS week FROM table GROUP BY customer_name I receive the following error:syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

[pcharron]

Here is a bit more specific information from testing.1. I'm trying to run this in Open Office Base.2. The test table is named Table13. The two columns I'm parsing from are named "Name" and "Date"Here is the sample query SELECT COUNT(DISTINCT LEFT (date, 0, 6 )) AS "week" FROM "Table1" GROUP BY "Name";This renders the following error when I execute the query:1: S1000 General error java.lang.ArrayIndexOutOfBoundsException: 2 in statement [sELECT COUNT(DISTINCT LEFT(date, 0, 6)]I'm wondering if the DISTINCT function is not being recognized by this platform.

[justsomeguy]

Unless you can find a reference indicating otherwise, I'm pretty sure the problem is that you're trying to send 3 values to LEFT, which I believe only accepts 2.

[pcharron]

I've got the query to run without errors however the resulting format makes it difficult to read. The syntax:SELECT COUNT( DISTINCT LEFT( "Date", 6 ) ) AS "week" FROM "Table1" GROUP BY "Name"This results in a table of a single column "date" with an entry of "1" iin each cell. I need to know who (Name) each entry is for. So instead of a result of this: "1"I need a result of this: "Name" / 1So I can then count the number of week entries per unique name.

[justsomeguy]

Then you need to select the name also.

[pcharron]

Thank you, the result now shows the count and the name. I need to get the counts by range. I don't know if I can add a BETWEEN statement (or where in the statement it would go) so that when...SELECT COUNT( DISTINCT LEFT( "Date", 6 ) ) parces out the day, when the date is in the yyyy/mm/dd format, can they then be grouped into ranges by week. So all records for a unique "Name" with a "Date" of 01 to 07 will show as a single result, 08-14 as another, etc.Right now I get the result where any number of records with variable dates for unique "Name" give only one returned entry in the query table. EXAMPLE:Smith / 1If Smith has 9 records in 3 week ranges it should result in one entry for each week range instead:Smith / 1Smith / 1Smith / 1Can I do this with multiple BETWEEN statements specifying the from to date of the range?

[justsomeguy]

That's getting fairly complicated. It will probably be easiest to use temporary tables to select the names and week numbers, and then do the count from that temporary table. It sounds like a giant switch statement with 52 cases, one for each week. It may be better to use a stored procedure instead. It would be less efficient, but you could also get all of the dates and do the other processing in another language like PHP.