Re: Ask for a query

On 7 Sep 2006 01:00:08 -0700, "Pickleman" <dutchpickleman_at_hotmail.com> wrote:

>Hello,
>
>I've got two tables, named Order and Customer. I want a query that let
>me see during a period per month wich customer bought something for the
>first time.
>
>Table Order contain a field Customer_ID and OrderDate
>
>Example:
>
>Period: Jan 1st 2005 - March 31st 2005
>
>Suppose in table Order:
>
>Customer_ID orderdatum
>1 12/01/2004
>1 09/15/2003
>1 01/13/2006
>2 01/01/2005
>2 02/02/2005
>3 01/05/2005
>3 05/12/2005
>4 08/05/2005
>5 03/21/2005
>
>Result
>Jan:
>2
>3
>March
>5
>
>Customer_ID 1 bought for the first time in September 2003, so he
>doesn't occur in the result. Customer_ID 4 bought later (August 2005)
>so he also doesn't occur in the result.
>
>How do I write that in SQL? I would be helped if the query is made for
>obe month only (I'll loop the query per month in that way)
>
>Thanks four reading my question. I hope you could help me.

Will this work?

SQL> create table orderinfo (customer_id number, orderdatum date);

Table created.

SQL> insert into orderinfo values (1, to_date('12/01/2004','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (1, to_date('09/15/2003','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (1, to_date('01/13/2006','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (2, to_date('01/01/2005','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (2, to_date('02/02/2005','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (3, to_date('01/05/2005','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (3, to_date('05/12/2005','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (4, to_date('08/05/2005','MM/DD/YYYY'));

1 row created.

SQL> insert into orderinfo values (5, to_date('03/21/2005','MM/DD/YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> select customer_id, min(orderdatum) from orderinfo group by customer_id;

CUSTOMER_ID MIN(ORDER

----------- ---------
          1 15-SEP-03
          2 01-JAN-05
          3 05-JAN-05
          4 05-AUG-05
          5 21-MAR-05

SQL> select a.monthname, a.custid from
  2 (select ord.customer_id custid, to_char(min(orderdatum),'MON') monthname   3 from orderinfo ord group by ord.customer_id) a   4 where a.monthname = 'JAN';

MON CUSTID
--- ----------

JAN          2
JAN          3

SQL> select a.monthname, a.custid from
  2 (select ord.customer_id custid, to_char(min(orderdatum),'MON') monthname   3 from orderinfo ord group by ord.customer_id) a   4 where a.monthname = 'MAR';

MON CUSTID
--- ----------
MAR 5 Received on Thu Sep 07 2006 - 03:50:02 CDT