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]