Joining a table to itself

Hi folks,

I have a problem that can probably be solved through the use of outer joins. However, the solution so far has escaped me. Suppose I have the following table:

Customer VARCHAR2(20)
Order_date DATE
Ship_date DATE

Here’s a sample of what this table looks like:

Customer Order_date Ship_date

Fred        9/10/1999
Fred        9/28/1999   10/27/1999
Bill        10/1/1999
Bill        10/5/1999
George      10/11/1999  10/15/1999
George      10/29/1999
George      10/28/1999
George      10/20/1999

I want to obtain the following information from this table, broken down by customer:

Total # outstanding orders

# outstanding orders <=5 days old
# outstanding orders > 6 and <=15 days old
# outstanding orders > 15 days old

So, for the table shown above, and given today’s date of 10/29/1999, the results I want look like this:

Customer Total <=5 6-15 >15

Fred          1                   1
Bill          2                   2
George        3      2     1

I’ve tried several variations of outer-joining the table to itself, but haven’t come up with the right solution.

This is my latest attempt, which is still incorrect:

select cust, outstanding, less_than_5,
between_6_15, greater_than_15
from
(SELECT * from

(select COUNT(*) outstanding, customer cust

	FROM custord
	WHERE ship_date is null
	GROUP BY customer) a,



(SELECT COUNT(*) less_than_5, customer


	FROM custord
	WHERE ship_date is null
	AND (sysdate - order_date) <= 5
	GROUP BY customer) b,



(SELECT COUNT(*) between_6_15, customer


	FROM custord
	WHERE ship_date is null
	AND (sysdate - order_date) > 6
	AND (sysdate - order_date) <= 15
	GROUP BY customer) c,



(SELECT COUNT(*) greater_than_15, customer


	FROM custord
	WHERE ship_date is null
	AND (sysdate - order_date) > 15
	GROUP BY customer) d

where a.cust = b.customer (+)
and b.customer = c.customer (+)
and c.customer = d.customer (+)
)

What I get instead is

Customer Total <=5 6-15 >15

Fred        1
Bill        2
George      3        2    1

Any help would be greatly appreciated.

Thanks,
Peter Mroz

PAREXEL International
peter.mroz_at_parexel.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 29 1999 - 15:18:34 CDT