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]