Re: sql query

From: Mark J. Bobak <mark_at_bobak.net>
Date: 21 Jun 2002 09:52:44 -0700
Message-ID: <fe9b0e1b.0206210852.8a9a6a5@posting.google.com>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1024433884.20070.1.nnrp-12.9e984b29_at_news.demon.co.uk>...

> There is also the option for TRUNC() truncating
> the date to the quarter - which might be faster.
>
> However, the format MIGHT be 'QQ', and there
> is the problem that the dates that Oracle thinks
> are the first of each quarter may not be the ones
> required by the poster.

It should be easy to address that concern by calculating the offset you need for your fiscal calendar offset and just add that many days to the date column.
>
> Something like:
> select trunc(date_col, 'QQ') , count(*)
> from big_table
> group by trunc(date_col, 'QQ')
> ;
>
> should do it.

Try something like:

     select trunc(date_col + :fiscal_calendar_offset, 'QQ'), count(*)
       from big_table

   group by trunc(date_col + :fiscal_calendar_offset, 'QQ'), count(*);

instead?

>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
Received on Fri Jun 21 2002 - 11:52:44 CDT