Hi Steffen - thanks for this, it seems to returning what I was looking for.

Am working on how to group by userid...

thanks again..
Patrick

--- In firebird-support@yahoogroups.com, "Steffen Heil" <lists@...> wrote:
>
> I suspect this will not work with a simple query. However:
>
> select * from count_tickets( 'start_date', 'end_date' )
>
> will work with:
>
> CREATE PROCEDURE COUNT_TICKETS (
> istart date,
> iend date)
> returns (
> odate date,
> ocount integer)
> as
> begin
> odate = :istart;
> while ( :odate <= :iend ) do begin
> select count(*) from notemaster
> where opendate < :odate
> and closedate > :odate
> into :ocount;
> suspend;
> odate = :odate + 1;
> end
> end
>
> Make sure to have indices on opendate and closedate. Even better store the
> result in an own table and only compute results that have not been computed
> before, as count(*) does take a loooooong time sometimes. Supposing both
> dates are usually set to current_date and are not overwritten, older
> computed values will not change...
>
> Regards,
> Steffen
>
>
> -----Ursprüngliche Nachricht-----
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] Im Auftrag von Patrick
> Gesendet: Dienstag, 30. März 2010 11:38
> An: firebird-support@yahoogroups.com
> Betreff: [firebird-support] Re: A seemingly simple query?
>
> 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]
> > >
> >
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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]
>