Dynamic sub-queries in a time range?

ManInBlack

Hi altogether,
i have got such big problem:

My report should have count queries about a special time range. By using a parameter the user can input the start date and end date. A field (products) has to be counted, but the aggregation should be monthly:

for example:
Start Parameter: 01.04.2009
End Parameter: 09.08.2009

The report should show following result:
__________________________________________________________________________________________
04.2009_ _ _ _| _ _ _ _05.2009_ _ _ |_ _ _ _ _06.2009_ _ _| _ _ _ _ _07.2009_ _ _ |_ _ _ _ _08.2009 _ _|
_ _ 20_ _ _ _ _| _ _ _ _ _ 25_ _ _ _ | _ _ _ _ _ 23_ _ _ _ _| _ _ _ _ _ 18_ _ _ _ _ _| _ _ _ _ _ _14 _ _ _ |
_____________|__________________|__________________|____________________|_________________

(The query is a sql-query which counts the products of a month:
select count of ProductA where time > 01.04.2009 and time < 31.04.2009)

The goal is to let the querries dynamical. If the time range (start parameter and end parameter) will be changed, more or less sub-queries have to be done.
I don´t want to select fix dates within of the where-clause.

The dynamic and variable factor is really important. Because an user would like to call queries only for 2 month, the other user for 2 years.

Is it possible to accomplish that, if yes, about which way? And how is the design of the report?

Please experts help me, if you have an idea. At the moment i am helpless and have no plan.

If my explanations aren´t exact enough please could you give me a post.

Maybe thank you very much in advance.

Byebye
ManInBlack

ManInBlack

I could solute it, but only with fix date parameters.

For each count result, i have a count query (and therefore data sets) with the date parameters.
The relation of a query to a data set is in my case 1:1.

---

For example:

- Data Set1:
select count productA where date >= 01.04.2009 and date < 01.05.2009

- Data Set2:
select count productA where date >= 01.05.2009 and date < 01.06.2009

- Data Set3:
// i use date parameters
select count productA where date >= params["3rdDate"] and date < params["4thDate"]

- further more Data Sets

---

All in all i have 5 queries with nearly the same functionality. The only difference are the dates. In my eyes it isn´t efficient.

I also have more products (productA, productB, productC,...). For every product i also have to create 5 data sets with the dates.

3 products --> are 15 data sets / queries

Hopefully i could give you an understanding of my huge problem.

Are there any possiblities to construct it more comfortable?

thx