Re: sql query

GM,

Won't it be again a perfect candidate for a time dimension table?

Store all relevant dates in a time_dimension table, add year, quarter columns, do proper indexes and then just join & group.

Something like...

SELECT count(*)
FROM orddet o, time_dimension td
WHERE td.the_year BETWEEN (1996, 1997)
AND td.time_key = o.order_date
GROUP BY td.quarter;

(sorry for any misspelling in the SQL, I did not try it out, hope you get the bottom line)

Would be nice if you would supply processing times for the alternative methods mentioned in the thread.

Norbert

"GM" <GM_at_nospam.com> schrieb im Newsbeitrag news:aenv2n$o81$1_at_galaxy.us.dell.com...
> I have a table with 800Millian rows. I want to write a sql query where
i
> want to find out how many rows fall between certain dates. It is bascially
> find out each quater data in year for last 6 years.
>
> Basically combining the below statments into one single sql query
>
> select count(*) from orddet where order_date >= '01-JAN-1996' and
order_date
> <= '31-MAR-1996';
> select count(*) from orddet where order_date >= '01-APR-1996' and
order_date
> <= '30-JUN-1996';
> select count(*) from orddet where order_date >= '01-JUL-1996' and
order_date
> <= '30-SEP-1996';
> select count(*) from orddet where order_date >= '01-OCT-1996' and
order_date
> <= '31-DEC-1996';
>
> select count(*) from orddet where order_date >= '01-JAN-1997' and
order_date
> <= '31-MAR-1997';
> select count(*) from orddet where order_date >= '01-APR-1997' and
order_date
> <= '30-JUN-1997';
> select count(*) from orddet where order_date >= '01-JUL-1997' and
order_date
> <= '30-SEP-1997';
> select count(*) from orddet where order_date >= '01-OCT-1997' and
order_date
> <= '31-DEC-1997';
>
> Thx
>
>
>
Received on Wed Jun 19 2002 - 03:39:59 CDT