Re: Ask for a query

On 7 Sep 2006 03:50:02 -0500, Richard Piasecki <usenet2_at_ogoent.com> wrote:

>
>
>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
>

Oops. I forgot about the year. Use queries like the following instead...

SQL> select to_char(a.firstdate,'MON'), a.custid from   2 (select ord.customer_id custid, min(ord.orderdatum) firstdate   3 from orderinfo ord group by ord.customer_id   4 ) a
  5 where to_char(a.firstdate,'MM/YYYY') = '01/2005';

TO_ CUSTID
--- ----------

JAN          2
JAN          3

SQL> select to_char(a.firstdate,'MON'), a.custid from   2 (select ord.customer_id custid, min(ord.orderdatum) firstdate   3 from orderinfo ord group by ord.customer_id   4 ) a
  5 where to_char(a.firstdate,'MM/YYYY') = '03/2005';

TO_ CUSTID
--- ----------
MAR 5 SQL> select to_char(a.firstdate,'MON'), a.custid from   2 (select ord.customer_id custid, min(ord.orderdatum) firstdate   3 from orderinfo ord group by ord.customer_id   4 ) a
  5 where to_char(a.firstdate,'MM/YYYY') = '09/2003';

TO_ CUSTID
--- ----------
SEP 1 SQL> select to_char(a.firstdate,'MON'), a.custid from   2 (select ord.customer_id custid, min(ord.orderdatum) firstdate   3 from orderinfo ord group by ord.customer_id   4 ) a
  5 where to_char(a.firstdate,'MM/YYYY') = '06/2004';

no rows selected Received on Thu Sep 07 2006 - 04:11:01 CDT