And to clarify further - the OPENDATE and CLOSEDATE vary considerably between records, and in, for example, a 3 month period in any one day there is a changing number of records that are deemed to be 'open' - which is what I want to capture.

--- In firebird-support@yahoogroups.com, "Patrick" <pfitzgerald@...> wrote:
>
> I wish to get the count on a per day basis - not the total between dates.
>
>
>
> --- In firebird-support@yahoogroups.com, "Steffen Heil" <lists@> wrote:
> >
> > Hi
> >
> > So what's you question?
> > As far as I understand, you want to get the same results as for the query
> > shown, just for a range of days, instead of one day only.
> >
> > If so, just do:
> >
> > select
> > count(*)
> > from
> > notemaster
> > where
> > (OPENDATE<'last_day')
> > and
> > (CLOSEDATE>'first_day')
> >
> > This will get you any ticket that was somewhen open within the specified
> > range.
> > If you need tickets that were open during the complete range:
> >
> > select
> > count(*)
> > from
> > notemaster
> > where
> > (OPENDATE<'first_day')
> > and
> > (CLOSEDATE>'last_day')
> >
> > BTW: Beware of equals: You should probably consider changing one of the
> > comparisons (or both) to <= or >= instead of < or > respectively.
> >
> >
> > Regards,
> > Steffen
> >
> >
> > -----Ursprüngliche Nachricht-----
> > Von: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] Im Auftrag von pfitzgerald
> > Gesendet: Dienstag, 30. März 2010 09:28
> > An: firebird-support@yahoogroups.com
> > Betreff: [firebird-support] A seemingly simple query?
> >
> > 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
> >
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://www.firebirdsql.org and click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > Yahoo! Groups Links
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>