Thanks, I will try your query just as an experiment-

Steffens answer works for me, I had a suspicion that it might have to be done as a stored procedure - but like you say, it was simple to describe - but hard to even keep the concept of what the query would look like in my head... ;)


thanks again.. /p

--- In firebird-support@yahoogroups.com, "Svein Erling" <svein.erling.tysvaer@...> wrote:
>
> 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
>