First, I'll show you the 8.1.6 syntax (just to see how much easier this will be....) and then 2 alternative syntaxes for 8.1.5 (the last one works in all releases but is a little hard to read)....

The table I used is:

SQL> create table t ( x int, y date );
Table created.

SQL> insert into t values ( 1,'01-jan-00' );
SQL> insert into t values ( 1,'02-jan-00' );
SQL> insert into t values ( 2,'01-jan-00' );
SQL> insert into t values ( 2,'06-jan-00' );

SQL> insert into t values ( 3,'31-dec-1999' );
SQL> insert into t values ( 3,'01-jan-00' );
SQL> insert into t values ( 3,'02-jan-00' );
SQL> insert into t values ( 3,'03-jan-00' );
SQL> insert into t values ( 3,'04-jan-00' );
SQL> insert into t values ( 3,'05-jan-00' );
SQL> insert into t values ( 3,'06-jan-00' );
SQL> insert into t values ( 3,'07-jan-00' );
SQL> insert into t values ( 3,'08-jan-00' );
SQL> insert into t values ( 3,'09-jan-00' );

I wanted all the boundary values to be present in the test -- dec 31'st is out of range as is 8 and 9 Jan. Jan 2 and 3 are boundary conditions as well...
SQL> select x,
2 sum(case
3 when y between '01-jan-00' and '02-jan-00'
4 then 1
5 else 0
6 end ) jan1_jan2,
7 sum(case
8 when y between '03-jan-00' and '07-jan-00'
9 then 1
10 else 0
11 end ) jan3_jan7
12 from t
13 group by x
14 /

The new case statment in 8.1.6 makes doing this sort of logic pretty easy...

X JAN1_JAN2 JAN3_JAN7
---------- ---------- ----------
1 2 0
2 1 1
3 2 5


In 8.1.5, we can use the trick of "selecting a select" which is almost as easy as the above and would look like this:


SQL> select x,
2 ( select count(*)
3 from t b
4 where b.x = t.x
5 and b.y between '01-jan-00'
6 and '02-jan-00' ) jan1_jan2,
7 ( select count(*)
8 from t b
9 where b.x = t.x
10 and b.y between '03-jan-00'
11 and '07-jan-00' ) jan3_jan7
12 from (select distinct x
13 from t ) t
14 /

X JAN1_JAN2 JAN3_JAN7
---------- ---------- ----------
1 2 0
2 1 1
3 2 5

The above, while it works, will definitely not be as efficient as the CASE statement will be. The above will have to create a set of distinct X's and then for each one of them run 2 subqueries. The case statement will do all of this with 1 scan instead of 1 scan + N * 2 queries. So, how can we do the case statement today in 8.1.5 (and before...)


SQL> select x,
2 sum( decode( sign( y-to_date('03-jan-00') ),
3 -1, decode( sign(y-to_date('01-jan-00')),
4 -1, 0, 1 ), 0 )) jan1_jan2,
5 sum(decode( sign( y-to_date('08-jan-00') ),
6 -1, decode( sign(y-to_date('03-jan-00')),
7 -1, 0, 1 ), 0 )) jan3_jan7
8 from t
9 group by x
10 /

Decode can do it -- its a little obscure but it works. We have to convert:
if y between A and B into:

if y < B
if Y < A
return 0
else
return 1
end if;
else
return 0
end if

Thats what that decode does.

X JAN1_JAN2 JAN3_JAN7
---------- ---------- ----------
1 2 0
2 1 1
3 2 5