Hi all,

I have table (NOTEMASTER) that has the following fields:
- OPENDATE (datetime) and OPENUID (varchar) and
- CLOSEDATE (datetime) and CLOSEUID (varchar).

These fields are automatically stamped with the current date and user id when certain other fields are updated. OPENDATE always preceeds CLOSEDATE. There a multiple records created daily.

I wish to create a query that returns a daily count of all records that are 'open', ie, records that have an OPENDATE < now and CLOSEDATE > now - across a range of specified dates.

For a single day, it would be similar to this:

select
count(*)
from
notemaster
where
(OPENDATE<'2010-1-1')
and
(CLOSEDATE>'2010-1-1')

Ideally I'd like to be able to get a range of dates and be able to group the results by OPENUID or CLOSEUID.

The grouping is less of an issue (as I can easily manipulate the above query using a GROUP BY clause) - but doing it across a date range has got me stumped.

How would I do this?

Patrick