date stuff

[sdkramer]

similar, but different question to my first one. I have a problem table, it has startdate and closed date.
i would like to know how many problems were unsolved in given month. ergo the start date is less than or equal to some month, and the closed date is null or greater than that same month.

so given the following records I would recieve the following.

problems
id start_date close_date
1 01/02/03 01/03/03
2 02/03/03 03/02/03
3 01/01/03 03/01/03
4 01/26/03 02/02/03
5 02/22/03 02/23/03

would return
01/03 2
02/03 2

so I guess my question is where in the world do I get the "some month" described above?

The only thing I can come up with is querying over and over again and somehow agregating that data. is there a more elegant solution?

 

[dan-cat]

I was bored this evening so I did this for you.

I put your sample data in tblDate once you run the code it creates a new table called tblKount listing the number of records per month that meet your criteria.

Hope this helps

Dan