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]
>