Re: Ask for a query

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

break on orderdatum nodup skip 2

select distinct trunc(orderdatum,'MONTH'), customer_id from orders x
where orderdatum between trunc(to_date('01-jan-2005', 'dd-mon-yyyy'),'month') and last_day(add_months(to_date('01-jan-2005', 'dd-mon-yyyy'),2))
where not exists
(select 'x'
 from orders y
 where y.customer_id = x.customer_id
 and orderdatum <= to_date('01-jan-2005', 'dd-mon-yyyy') )
order by 1, 2

-- 
Sybrand Bakker
Senior Oracle DBA