Steffens answer to use a stored procedure is the correct answer - it is simply not doable in a simple query. Is it doable in a somewhat more complicated query? Well, with a recursive CTE and not all too long date range (there is a limit to recursion), then probably:

WITH RECURSIVE MyCTE (MyDate, MyCount)
AS (SELECT CAST(:FromDate as Date), Count(*)
FROM NoteMaster NM1
WHERE NM1.OpenDate < :FromDate
AND NM1.CloseDate > :FromDate
GROUP BY 1
UNION ALL
SELECT MC.MyDate+1, Count(*)
FROM MyCTE MC
JOIN NoteMaster NM2 on (1=1)
WHERE NM2.OpenDate < MC.MyDate+1
AND NM2.CloseDate > MC.MyDate+1
AND MC.MyDate < :ToDate
GROUP BY 1)
SELECT * FROM MyCTE

Why it cannot be done in a simple query? Well, I neither know of any simple query that can count a row multiple times, nor how to group on values not necessarily mentioned in any row. You have a problem that is simple to describe, but that is not the same as having a problem that is easy to solve. I've not tried the above query and do not know whether it will work.

HTH,
Set

In firebird-support@yahoogroups.com, "pfitzgerald" wrote:

>
> 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