Re: Ask for a query



On 7 Sep 2006 01:00:08 -0700, "Pickleman" <dutchpickleman@xxxxxxxxxxx> 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


.

Relevant Pages

  • Re: Update Combo Box from a Text Box Help
    ... It appears your sql statement for the combobox is wrong. ... Temporarily bind this query to your combobox, ... So the CustID number is not getting passed to the combo box. ...
    (comp.databases.ms-access)
  • Re: Update Combo Box from a Text Box Help
    ... It appears your sql statement for the combobox is wrong. ... Temporarily bind this query to your combobox, ... Textbox Name: CustID ... Private Sub CustID_AfterUpdate ...
    (comp.databases.ms-access)
  • Re: Update Combo Box from a Text Box Help
    ... this is the subform CONTROL on the parent ... It appears your sql statement for the combobox is wrong. ... Temporarily bind this query to your combobox, ... So the CustID number is not getting passed to the combo box. ...
    (comp.databases.ms-access)
  • Re: Ask for a query
    ... Customer_ID 1 bought for the first time in September 2003, ... How do I write that in SQL? ... MON CUSTID ...
    (comp.databases.oracle.server)
  • Re: How to count same value as 1 in a query
    ... Michel, I was able to understand what you were trying to explain to me in SQL ... query that will be based on the previous query, ... Y amount of times during a year, but if the products was bought multiple ...
    (microsoft.public.access.queries)