Re: sql query

From: Imprecise <f_puhan_at_precise.com>
Date: Tue, 18 Jun 2002 17:31:18 -0400
Message-ID: <f_puhan-F1A180.17311818062002@vienna7.his.com>

In article <aenv2n$o81$1_at_galaxy.us.dell.com>, "GM" <GM_at_nospam.com> wrote:

> 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

You might do better to write this as a procedure. Or at least an anonymous PL/SQL block.

CREATE or REPLACE PROCEDURE Qtr_Count AS   cursor c_orddet is
    select order_date from orddet;

  Q1_96 NUMBER := 0;
  Q2_96 NUMBER := 0;
  Q3_96 NUMBER := 0;
  Q4_96 NUMBER := 0;

• Add as many quarterly counters as necessary

BEGIN
  for v_date in c_orddet LOOP

• if your version of Oracle supports it, you could use CASE if v_date.order_date between to_date('01-JAN-1996','DD-MON-YYYY') AND to_date('31-MAR-1996','DD-MON-YYYY') THEN Q1_96 := Q1_96 + 1; elsif v_date.order_date between to_date('01-APR-1996','DD-MON-YYYY') AND to_date('30-JUN-1996','DD-MON-YYYY') THEN Q2_96 := Q2_96 + 1; ... end if; end loop; END;

Please note: this is incomplete and untested. I am aware of a bit of a logic problem using the BETWEEN operator, but this shown only to stimulate the imagination...

-- 
The underscore character does not belong in my address. You know the drill...
***
Anyone sufficiently smart enough to configure and use USEnet for research should
be smart enough to Read The Freakin' Documentation!

Received on Tue Jun 18 2002 - 16:31:18 CDT